SQL for Data Warehousing: SCD Types 1, 2, 3, Star vs Snowflake Schema, ROLLUP and CUBE

Reporting databases are designed differently from application databases. This guide covers the data-warehouse SQL that data and ETL testers meet: slowly changing dimensions (how history is kept), OLTP vs OLAP, star and snowflake schemas, and subtotal queries with ROLLUP, CUBE and GROUPING SETS.

Slowly Changing Dimensions (SCD)

SCD Type 1 — Overwrite (No History)

Before update:

After SCD Type 1 update (customer moved to Hyderabad):

-- SCD Type 1: Simple UPDATE, no history
UPDATE dim_customers
SET city = 'Hyderabad',
    updated_at = NOW()
WHERE customer_id = 101;

-- Pros: Simple, storage-efficient
-- Cons: Historical data permanently lost
-- Use: Correcting errors, non-analytical attributes (phone, email typos)

SCD Type 2 — Add New Row (Full History) ← Most Important

SCD Type 2 Table Design:

-- SCD Type 2: Expire old record, insert new record
START TRANSACTION;

-- Step 1: Expire the current record
UPDATE dim_customers
SET effective_to = DATE_SUB(CURDATE(), INTERVAL 1 DAY),
    is_current = FALSE
WHERE customer_id = 101 AND is_current = TRUE;

-- Step 2: Insert new current record
INSERT INTO dim_customers
    (customer_id, name, city, effective_from, effective_to, is_current)
VALUES
    (101, 'Naveed Khan', 'Hyderabad', CURDATE(), '9999-12-31', TRUE);

COMMIT;

-- Query: What city was customer 101 in on 2023-06-01?
SELECT city FROM dim_customers
WHERE customer_id = 101
  AND '2023-06-01' BETWEEN effective_from AND effective_to;
-- Returns: Bangalore ✓

-- Query: Current city of all customers
SELECT customer_id, name, city FROM dim_customers WHERE is_current = TRUE;

-- Surrogate key approach (better for joins)
-- dim_customers: surrogate_key (PK), customer_id (natural key), ...
-- fact_orders references surrogate_key, not customer_id
-- This way the fact row always points to the correct dimension snapshot

SCD Type 3 — Add New Column (Limited History)

SCD Type 3 Table:

-- SCD Type 3: Shift current to previous, update current
UPDATE dim_customers
SET prev_city       = current_city,
    current_city    = 'Hyderabad',
    city_changed_at = CURDATE()
WHERE customer_id = 101;

-- Pros: Simple queries for before/after comparison
-- Cons: Only one historical value stored, more schema changes needed for each new attribute

Star Schema, Snowflake and Data Warehouse SQL

OLTP vs OLAP

Star Schema

Star Schema: E-Commerce Sales Data Warehouse

-- Create Star Schema tables
CREATE TABLE dim_date (
    date_key     INT PRIMARY KEY,   -- e.g., 20250115 (YYYYMMDD)
    full_date    DATE NOT NULL,
    day_of_week  VARCHAR(10),
    week_number  INT,
    month_num    INT,
    month_name   VARCHAR(10),
    quarter      INT,
    year         INT,
    is_weekend   BOOLEAN,
    is_holiday   BOOLEAN,
    fiscal_year  INT,
    fiscal_qtr   INT
);

CREATE TABLE fact_sales (
    sale_id      BIGINT PRIMARY KEY AUTO_INCREMENT,
    date_key     INT REFERENCES dim_date(date_key),
    customer_key INT REFERENCES dim_customer(customer_key),
    product_key  INT REFERENCES dim_product(product_key),
    store_key    INT REFERENCES dim_store(store_key),
    quantity     INT NOT NULL,
    unit_price   DECIMAL(10,2),
    discount_pct DECIMAL(5,2) DEFAULT 0,
    revenue      DECIMAL(12,2),
    cost         DECIMAL(12,2)
);

-- Typical Star Schema Query: Revenue by Category and Quarter
SELECT
    d.year, d.quarter, p.category,
    SUM(f.revenue)   AS total_revenue,
    SUM(f.quantity)  AS units_sold,
    COUNT(DISTINCT f.sale_id) AS transactions
FROM fact_sales f
JOIN dim_date     d ON f.date_key     = d.date_key
JOIN dim_product   p ON f.product_key  = p.product_key
WHERE d.year = 2025
GROUP BY d.year, d.quarter, p.category
ORDER BY d.quarter, total_revenue DESC;

Snowflake Schema

Rollup, Cube, Grouping Sets

-- ROLLUP: subtotals by dept, then overall total
SELECT dept, city,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(dept, city);
-- Produces:
--   Engineering | Bangalore | 10 | 850000
--   Engineering | Mumbai    |  5 | 420000
--   Engineering | NULL      | 15 | 1270000  ← dept subtotal
--   HR          | Delhi     |  8 | 480000
--   HR          | NULL      |  8 | 480000   ← dept subtotal
--   NULL        | NULL      | 23 | 1750000  ← grand total

-- GROUPING() function: detect which NULLs are from ROLLUP (not real NULLs)
SELECT
    CASE GROUPING(dept) WHEN 1 THEN 'ALL DEPTS' ELSE dept END AS dept,
    CASE GROUPING(city) WHEN 1 THEN 'ALL CITIES' ELSE city END AS city,
    SUM(salary) AS total
FROM employees
GROUP BY ROLLUP(dept, city);

-- CUBE: ALL combinations of dept + city
SELECT dept, city, SUM(salary) AS total
FROM employees
GROUP BY CUBE(dept, city);
-- Produces: (dept,city), (dept,ALL), (ALL,city), (ALL,ALL)

-- GROUPING SETS: choose exactly which groupings you want
SELECT dept, city, month, SUM(salary)
FROM employees
GROUP BY GROUPING SETS (
    (dept, city),   -- group by dept and city
    (dept),         -- group by dept only
    ()              -- grand total
);
-- More flexible than ROLLUP or CUBE

FAQs

What is SCD Type 2?

A slowly changing dimension that keeps full history: when an attribute changes, the current row is closed with an end date and a new current row is inserted.

What is the difference between a star and a snowflake schema?

In a star schema each dimension is one denormalised table joined directly to the fact table; a snowflake schema normalises dimensions into several related tables, saving space at the cost of more joins.

What does ROLLUP do in SQL?

It adds subtotal rows for each level of the GROUP BY columns plus a grand total; CUBE adds subtotals for every combination of the columns.