gold-layer.sql

--  GOLD LAYER ( connecting, relationships )

-- FOR CUSTOMERS
-- cst_key from ci and cid from erp_cust_az12
-- renamed the columns for better understanding.
-- create  the view for gold customers table. 

CREATE VIEW gold.dim_customers AS
SELECT
ROW_NUMBER() OVER(ORDER BY cst_id) AS customer_key,
  ci.cst_id AS customer_id,
  ci.cst_key AS customer_number,
  ci.cst_firstname AS first_name,
  ci.cst_lastname AS last_name,
  CASE WHEN ci.cst_gndr != 'n/a' THEN ci.cst_gndr -- CRM is the master for gender info
       ELSE COALESCE(ca.gen, 'n/a')
   END AS gender,
  ci.cst_marital_status AS marital_status,
  ca.bdate AS birthdate,
  la.cntry AS country,
  ci.cst_create_date AS create_date
 FROM silver.crm_cust_info ci
 LEFT JOIN silver.erp_cust_az12 ca 
 ON ci.cst_key = ca.cid 
 LEFT JOIN silver.erp_loc_a101 la 
 ON ci.cst_key = la.cid 

-- FOR PRODUCTS

CREATE VIEW gold.dim_products AS
SELECT 
ROW_NUMBER() OVER(ORDER BY pn.prd_start_dt, pn.prd_key) AS product_key,
    pn.prd_id AS product_id,
    pn.prd_key AS product_number,
    pn.prd_nm AS product_name,
    pn.cat_id AS category_id,
    pc.cat AS category,
    pc.subcat AS subcategory,
    pc.maintenance,
    pn.prd_cost AS cost,
    pn.prd_line AS product_line,
    pn.prd_start_dt AS start_date
FROM silver.crm_prd_info pn
LEFT JOIN silver.erp_px_cat_g1v2 pc
    ON pn.cat_id = pc.id
WHERE prd_end_dt IS NULL; -- Filter out all historical data

-- now crm_sales_details 

CREATE VIEW gold.fact_sales AS

SELECT
sd.sls_ord_num AS order_number,
pr.product_key,
cu.customer_key,
sd.sls_order_dt AS order_date,
sd.sls_ship_dt AS shipping_date,
sd.sls_due_dt AS due_date,
sd.sls_sales AS sales_amount,
sd.sls_quantity AS quantity,
sd.sls_price AS price
FROM silver.crm_sales_details sd 
LEFT JOIN gold.dim_products pr 
ON sd.sls_prd_key = pr.product_number
LEFT JOIN gold.dim_customers cu 
ON sd.sls_cust_id = cu.customer_id


-- assigning primary keys and foreign keys

ALTER TABLE gold.dim_customers 
ADD CONSTRAINT PK_customer_key PRIMARY KEY (customer_key);

ALTER TABLE gold.dim_products 
ADD CONSTRAINT PK_product_key PRIMARY KEY (product_key);

ALTER TABLE gold.fact_sales
ADD CONSTRAINT FK_product_key FOREIGN KEY (product_key) REFERENCES gold.dim_products(product_key);

ALTER TABLE gold.fact_sales
ADD CONSTRAINT FK_customer_key FOREIGN KEY (customer_key) REFERENCES gold.dim_products(customer_key);