Building a data warehouse (SQL)
تفاصيل العمل
#Mastering Data Warehouse Architecture: From Raw Data to Actionable Insights
? Bronze Layer: Raw Data Capture
Objective: Preserve source data in its original form
Key Implementation Strategies:
- Direct ingestion from source systems
- Minimal transformations
- Preserve data lineage
- Create staging tables for each source
Sample Bronze Layer Table:
CREATE TABLE bronze.crm_cust (
cst_id NVARCHAR(50),
cst_firstname NVARCHAR(50),
cst_lastname NVARCHAR(50),
cst_marital_status NVARCHAR(50),
cst_gndr NVARCHAR(50)
)
? Silver Layer: Data Transformation & Cleansing
Objective: Clean, standardize, and prepare data for analysis
Transformation Techniques:
- Remove duplicate records
- Standardize data types
- Apply business rules
- Normalize and format data
Sample Silver Layer Transformation:
INSERT INTO silver.crm_cust
SELECT
cst_id,
TRIM(cst_firstname),
CASE
WHEN cst_marital_status = 'M' THEN 'MARRIED'
WHEN cst_marital_status = 'S' THEN 'SINGLE'
ELSE 'UNKNOWN'
END AS marital_status
FROM bronze.crm_cust
? Gold Layer: Business-Ready Insights
Objective: Create refined, optimized data models for reporting
Dimensional Views Creation:
1. Customer Dimension View:
CREATE VIEW gold.cst_dim AS (
SELECT
c.cst_id AS customer_id,
CONCAT(c.cst_firstname, ' ', c.cst_lastname) AS customer_fullname,
c.cst_marital_status,
COALESCE(c.cst_gndr, e.GEN, 'UNKNOWN') AS customer_gender,
l.CNTRY AS customer_country
FROM silver.crm_cust c
LEFT JOIN silver.erp_cust e ON c.cst_key = e.CID
LEFT JOIN silver.erp_loc l ON c.cst_key = l.CID
)
2. Product Dimension View:
CREATE VIEW gold.prd_dim AS (
SELECT
p.prd_key AS product_id,
p.prd_nm AS product_name,
c.CAT AS product_category,
c.SUBCAT AS product_subcategory
FROM silver.crm_prd p
LEFT JOIN silver.erp_cat c ON p.cat_id = c.ID
)
3. Sales Fact View:
CREATE VIEW gold.sales_fact AS (
SELECT
sls_ord_num AS order_number,
sls_prd_key AS product_id,
sls_cust_id AS customer_id,
sls_sales AS total_sales,
sls_quantity AS quantity
FROM silver.crm_salesdetails
)