Data Warehousing Concepts Cheat Sheet

SELECT d.year, p.category, SUM(f.sales_amount) AS total_salesFROM fact_sales fJOIN dim_date d ON f.date_key = d.date_keyJOIN dim_product p ON f.product_key = p.product_keyWHERE d.year IN (2023, 2024)GROUP BY d.year, p.categoryORDER BY d.year, total_sales DESC;

-- Expire the current rowUPDATE dim_customerSET end_date = CURRENT_DATE, is_current = FALSEWHERE customer_id = 42 AND is_current = TRUE;-- Insert the new versionINSERT INTO dim_customer (customer_id, name, city, start_date, end_date, is_current)VALUES (42, 'Jane Doe', 'Austin', CURRENT_DATE, NULL, TRUE);

MERGE INTO dim_customer AS targetUSING staging_customer AS srcON target.customer_id = src.customer_id AND target.is_current = TRUEWHEN MATCHED AND (target.city <> src.city OR target.email <> src.email) THEN  UPDATE SET target.end_date = CURRENT_DATE, target.is_current = FALSEWHEN NOT MATCHED THEN  INSERT (customer_id, name, city, email, start_date, end_date, is_current)  VALUES (src.customer_id, src.name, src.city, src.email, CURRENT_DATE, NULL, TRUE);-- Follow with a second INSERT for rows that changed, since MERGE can't-- both close the old row and insert a new one for the same key in one passINSERT INTO dim_customer (customer_id, name, city, email, start_date, end_date, is_current)SELECT src.customer_id, src.name, src.city, src.email, CURRENT_DATE, NULL, TRUEFROM staging_customer srcJOIN dim_customer d ON d.customer_id = src.customer_id AND d.is_current = FALSE AND d.end_date = CURRENT_DATE;

-- Periodic snapshot fact: one row per account per dayCREATE TABLE fact_account_snapshot (  snapshot_date_key INT,  account_key        INT,  balance_amount      NUMERIC(18,2),  PRIMARY KEY (snapshot_date_key, account_key));-- Accumulating snapshot: one row per order, updated as it moves through stagesCREATE TABLE fact_order_pipeline (  order_key            INT PRIMARY KEY,  ordered_date_key      INT,  shipped_date_key       INT,  delivered_date_key      INT,  days_order_to_ship        INT);-- Factless fact table: records an event/relationship with no measureCREATE TABLE fact_student_attendance (  date_key    INT,  student_key INT,  class_key   INT);

-- Running total of sales per region ordered by dateSELECT  d.date, f.region,  SUM(f.sales_amount) OVER (PARTITION BY f.region ORDER BY d.date) AS running_total,  RANK() OVER (PARTITION BY d.year ORDER BY SUM(f.sales_amount) DESC) AS region_rankFROM fact_sales fJOIN dim_date d ON f.date_key = d.date_keyGROUP BY d.date, f.region, d.year;-- Month-over-month change using LAGSELECT  month, revenue,  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_changeFROM monthly_revenue;