Slowly Changing Dimensions (SCD)

TYPE 0 EXAMPLES:
  customer.registration_date    ← when the customer first registered (never changes)
  customer.original_city        ← city where the customer first signed up (immutable)
  store.opening_date            ← when the store opened (historical fact, fixed)
  product.sku                   ← product identifier (never reassigned)
  employee.hire_date            ← when they joined the company

TYPE 0 RULE: On dimension load, INSERT new rows, NEVER update Type 0 columns.

-- dbt Silver model for customers:
INSERT INTO silver.customers (customer_id, registration_date, ...)
VALUES (...)
ON CONFLICT (customer_id) DO UPDATE SET
    -- Type 0 columns NOT in the update list:
    -- registration_date = EXCLUDED.registration_date  ← omitted intentionally
    -- Type 1 columns in the update list:
    city    = EXCLUDED.city,
    tier    = EXCLUDED.tier,
    updated_at = EXCLUDED.updated_at
;
-- registration_date is never overwritten even if source sends a different value.

-- Verification: confirm no Type 0 column was ever changed
SELECT customer_id, COUNT(DISTINCT registration_date) AS date_versions
FROM dimension_history_table
GROUP BY customer_id
HAVING COUNT(DISTINCT registration_date) > 1;

-- Returns: 0 rows — if any rows returned, a Type 0 column was changed incorrectly.

TYPE 1 EXAMPLES:
  customer.phone_number  ← updated when customer changes phone
  customer.email         ← updated when customer updates email
  store.manager_name     ← current manager (past manager irrelevant to most reports)
  product.description    ← updated when product copy is revised
  store.is_active        ← current operational status

TYPE 1 WHEN TO USE:
  ✓ The old value was genuinely wrong (data correction)
  ✓ History is not needed — reports always want current value
  ✓ The attribute has no analytical significance historically
  ✗ When historical accuracy matters for past events (then use Type 2 instead)

-- Upsert that overwrites changed attributes:
INSERT INTO dim_store
    (store_sk, store_id, store_name, manager_name, is_active, updated_at)
VALUES
    (1, 'ST001', 'FreshCart Midtown', 'Marcus Bennett', TRUE, NOW())
ON CONFLICT (store_id)
DO UPDATE SET
    manager_name = EXCLUDED.manager_name,   -- Type 1: always overwrite
    is_active    = EXCLUDED.is_active,       -- Type 1: always overwrite
    updated_at   = EXCLUDED.updated_at
;

-- EFFECT ON HISTORICAL FACT ROWS:
-- Before update: manager = 'Olivia Brown'.  After update: manager = 'Marcus Bennett'.
-- fct_orders joined to dim_store WHERE store_id = 'ST001': ALL historical orders
-- now show manager_name = 'Marcus Bennett' — even orders placed when Olivia
-- Brown was the manager. This IS the correct behaviour for Type 1 — if you
-- want historical orders to show who the manager was at the time, you need
-- Type 2 instead.

-- TYPE 1 IN dbt: Silver models use incremental merge with no special SCD
-- logic — all tracked columns are in the merge update set. No valid_from,
-- valid_to, or is_current needed for Type 1.
{{ config(materialized='incremental', unique_key='store_id',
          incremental_strategy='merge') }}

SCD Type 2 — Full History Preserved

Initial state — customer 4201938 registered from Seattle:
  customer_sk  customer_id  city      tier    valid_from   valid_to    is_current
  1            4201938      Seattle   silver  2024-01-15   NULL        TRUE

Customer places order 9284751 on 2024-06-10:
  fct_orders: order_sk=..., customer_sk=1, order_amount=380  ← joins to row 1

Customer moves to Austin, updates profile on 2026-02-01:

-- Step 1: expire the current row
UPDATE dim_customer
SET valid_to = '2026-01-31', is_current = FALSE
WHERE customer_id = 4201938 AND is_current = TRUE;

-- Step 2: insert the new version
INSERT INTO dim_customer
    (customer_sk, customer_id, city, tier, valid_from, valid_to, is_current)
VALUES
    (2, 4201938, 'Austin', 'silver', '2026-02-01', NULL, TRUE);

RESULTING TABLE STATE:
customer_sk  customer_id  city     tier    valid_from   valid_to    is_current
1            4201938      Seattle  silver  2024-01-15   2026-01-31  FALSE  ← expired
2            4201938      Austin   silver  2026-02-01   NULL        TRUE   ← current

Customer places order 9284755 on 2026-03-01:
fct_orders: order_sk=..., customer_sk=2, order_amount=460  ← joins to row 2

-- What city was customer 4201938 in when they placed order 9284751 (2024-06-10)?
SELECT c.city FROM fct_orders f
JOIN dim_customer c ON f.customer_sk = c.customer_sk
WHERE f.order_sk = <order_sk_for_9284751>;
-- Returns: 'Seattle' ← correct — the fact stored customer_sk=1 at load time

-- Revenue by customer city, historically accurate:
SELECT c.city, SUM(f.order_amount)
FROM fct_orders f JOIN dim_customer c ON f.customer_sk = c.customer_sk
GROUP BY c.city;
-- order_sk from 2024: joins to customer_sk=1 → Seattle
-- order_sk from 2026: joins to customer_sk=2 → Austin
-- Both cities get credit for orders placed when the customer was there ✓

-- WRONG APPROACH (joining on natural key with is_current):
JOIN dim_customer c ON f.customer_id = c.customer_id AND c.is_current = TRUE
-- This joins ALL orders (including 2024 ones) to the CURRENT version —
-- the 2024 Seattle order now shows 'Austin' — historically wrong ✗

Handling multiple changes in one load

# PRODUCTION TYPE 2 LOAD PROCEDURE (Python):
# Handles: new entities, Type 2 tracked changes, Type 1 changes

def load_dim_customer_scd2(
    source_rows: list[dict],
    dest_conn,
    type2_columns: list[str],   # ['city', 'tier']
    type1_columns: list[str],   # ['phone_masked', 'email_hashed']
) -> dict:
    """Type 2 columns: expire old row + insert new row on change.
    Type 1 columns: update in-place on current row (no new row)."""
    stats = {'new': 0, 'type2_change': 0, 'type1_change': 0, 'unchanged': 0}

    for row in source_rows:
        customer_id = row['customer_id']
        existing = dest_conn.execute("""
            SELECT * FROM dim_customer
            WHERE customer_id = %s AND is_current = TRUE
        """, (customer_id,)).fetchone()

        if existing is None:
            sk = generate_surrogate_key(customer_id, row['updated_at'])
            dest_conn.execute("""
                INSERT INTO dim_customer
                    (customer_sk, customer_id, city, tier, phone_masked,
                     valid_from, valid_to, is_current)
                VALUES (%s, %s, %s, %s, %s, %s, NULL, TRUE)
            """, (sk, customer_id, row['city'], row['tier'],
                  row['phone_masked'], row['updated_at'].date()))
            stats['new'] += 1
            continue

        # Check Type 2 columns for changes:
        type2_changed = any(row[col] != existing[col] for col in type2_columns)

        if type2_changed:
            # Expire old row:
            dest_conn.execute("""
                UPDATE dim_customer SET valid_to = %s, is_current = FALSE
                WHERE customer_sk = %s
            """, (row['updated_at'].date() - timedelta(days=1), existing['customer_sk']))

            # Insert new version:
            sk = generate_surrogate_key(customer_id, row['updated_at'])
            dest_conn.execute("""
                INSERT INTO dim_customer
                    (customer_sk, customer_id, city, tier, phone_masked,
                     valid_from, valid_to, is_current)
                VALUES (%s, %s, %s, %s, %s, %s, NULL, TRUE)
            """, (sk, customer_id, row['city'], row['tier'],
                  row['phone_masked'], row['updated_at'].date()))
            stats['type2_change'] += 1

        else:
            # No Type 2 change — check Type 1:
            type1_changed = any(row[col] != existing[col] for col in type1_columns)
            if type1_changed:
                dest_conn.execute("""
                    UPDATE dim_customer SET phone_masked = %s, dim_updated_at = NOW()
                    WHERE customer_sk = %s
                """, (row['phone_masked'], existing['customer_sk']))
                stats['type1_change'] += 1
            else:
                stats['unchanged'] += 1

    dest_conn.commit()
    return stats

-- Use when: source table has a reliable updated_at timestamp

-- snapshots/customers_snapshot.sql
{% snapshot customers_snapshot %}
{{ config(
    target_database = 'freshcart_prod',
    target_schema   = 'snapshots',
    unique_key      = 'customer_id',
    strategy        = 'timestamp',
    updated_at      = 'updated_at',      -- column dbt monitors for changes
    invalidate_hard_deletes = True,      -- expire rows when source row disappears
) }}
SELECT customer_id, customer_name, email_hashed, city, state, tier,
       acquisition_channel, registration_date, updated_at
FROM {{ source('silver', 'customers') }}
WHERE is_current = TRUE
{% endsnapshot %}

-- dbt adds these columns automatically:
--   dbt_scd_id       VARCHAR   — unique ID per version (hash of key + dbt_valid_from)
--   dbt_updated_at   TIMESTAMP — when dbt last processed this row
--   dbt_valid_from   TIMESTAMP — when this version became active
--   dbt_valid_to     TIMESTAMP — when this version expired (NULL = current)

{% snapshot customers_snapshot %}
{{ config(
    target_schema  = 'snapshots',
    unique_key     = 'customer_id',
    strategy       = 'check',
    check_cols     = ['city', 'tier'],   -- ONLY these columns trigger a new version
    -- Changing phone_masked does NOT create a new version (Type 1 for that column)
    invalidate_hard_deletes = True,
) }}
SELECT * FROM {{ source('silver', 'customers') }}
{% endsnapshot %}

HOW dbt SNAPSHOT RUNS:
- Reads the source query. For each row, checks if any check_cols (or
  updated_at) changed since last run.
- If changed: expires old row (dbt_valid_to = NOW()), inserts new row
  (dbt_valid_from = NOW(), dbt_valid_to = NULL).
- If unchanged: no action.
- If row disappeared from source AND invalidate_hard_deletes=True: expires
  the current row (marks it as deleted).

{{ config(materialized='table') }}

WITH snapshot AS (
    SELECT * FROM {{ ref('customers_snapshot') }}
)
SELECT
    {{ dbt_utils.generate_surrogate_key(['customer_id', 'dbt_valid_from']) }}
        AS customer_sk,
    customer_id, customer_name, email_hashed, city,
    CASE
        WHEN state IN ('Texas','Georgia','Florida','Alabama','Tennessee') THEN 'South'
        WHEN state IN ('California','Oregon','Washington') THEN 'West'
        WHEN state IN ('New York','New Jersey','Massachusetts','Pennsylvania','Connecticut') THEN 'Northeast'
        ELSE 'Midwest'
    END AS region,
    tier, acquisition_channel, registration_date,
    CAST(dbt_valid_from AS DATE) AS valid_from,
    CAST(dbt_valid_to AS DATE)   AS valid_to,
    CASE WHEN dbt_valid_to IS NULL THEN TRUE ELSE FALSE END AS is_current
FROM snapshot

-- RUNNING SNAPSHOTS:
dbt snapshot                          # run all snapshots
dbt snapshot -s customers_snapshot    # run one snapshot

-- IMPORTANT: dbt snapshot should run MORE FREQUENTLY than dbt run. If a
-- customer changes city twice in one day and snapshot only runs nightly,
-- the intermediate city is never captured — only the final day-end state.
-- For high-change dimensions: run snapshot every 15-30 minutes.

OPTION A: full-refresh (simplest, loses any history that existed)
  dbt snapshot --full-refresh
  Drops and recreates the snapshot table from scratch. All existing
  customers get one row with dbt_valid_from=NOW(), dbt_valid_to=NULL,
  is_current=TRUE. Going forward, all changes are captured. Past history
  is lost. Acceptable when no meaningful historical changes existed before.

OPTION B: seed historical versions from a separate data source
  If you have an audit log, CDC history in Bronze, or source system history,
  build a seed file with historical versions:
    customer_id  city     tier    updated_at
    4201938      Seattle  silver  2024-01-15  ← original registration
    4201938      Austin   silver  2026-02-01  ← after move
  Manually insert these into the snapshot table in the correct format
  BEFORE running dbt snapshot for the first time — dbt manages all future
  changes from there.

OPTION C: change history sourced from Bronze CDC
  Create a staging model that produces one row per version:
    SELECT customer_id, city, tier, change_timestamp AS updated_at
    FROM silver.customers_cdc_history
    ORDER BY customer_id, change_timestamp
  Point the dbt snapshot at this staging model — it processes each row,
  creating version rows as they appear, building a full historical SCD2
  table from CDC history.

MONITORING SNAPSHOT HEALTH:
  -- Version count per customer (investigate if any customer > 50):
  SELECT customer_id, COUNT(*) AS version_count
  FROM customers_snapshot GROUP BY customer_id
  ORDER BY version_count DESC LIMIT 20;

  -- Gaps in valid_from/valid_to continuity (an expired version with no successor):
  SELECT customer_id FROM customers_snapshot
  WHERE dbt_valid_to IS NOT NULL
    AND NOT EXISTS (
        SELECT 1 FROM customers_snapshot s2
        WHERE s2.customer_id = customers_snapshot.customer_id
          AND s2.dbt_valid_from = customers_snapshot.dbt_valid_to
    )

TYPE 3 TABLE STRUCTURE:
  dim_customer:
    customer_sk       BIGINT PRIMARY KEY
    customer_id       BIGINT
    city              VARCHAR(100)         ← CURRENT city
    previous_city     VARCHAR(100)         ← PREVIOUS city (one level back)
    city_changed_at   DATE                 ← when the city last changed
    tier              VARCHAR(20)          ← CURRENT tier
    previous_tier     VARCHAR(20)          ← PREVIOUS tier

INITIAL STATE:
  customer_sk  customer_id  city     previous_city  tier    previous_tier
  1            4201938      Seattle  NULL           silver  NULL

CUSTOMER MOVES TO AUSTIN (2026-02-01):
  UPDATE dim_customer
  SET previous_city   = city,          -- save current → previous
      city            = 'Austin',      -- new current
      city_changed_at = '2026-02-01'
  WHERE customer_id = 4201938;

RESULTING ROW:
customer_sk  customer_id  city    previous_city  tier    previous_tier
1            4201938      Austin  Seattle        silver  NULL

-- Revenue from customers who recently moved to each city:
SELECT city AS current_city,
    SUM(CASE WHEN f.order_date > c.city_changed_at THEN f.order_amount ELSE 0 END)
        AS revenue_after_move,
    previous_city AS came_from
FROM fct_orders f JOIN dim_customer c USING (customer_sk)
WHERE c.city_changed_at IS NOT NULL   -- only customers who have moved
GROUP BY 1, 3;

TYPE 3 LIMITATIONS:
  ✗ Only one level of history — if the customer moves again (Austin → New
    York), previous_city becomes Austin and Seattle is LOST.
  ✗ No point-in-time accuracy for fact table joins — all orders always join
    to the same single row, so a 2024 Seattle order and a 2026 Austin order
    both join to the same row.
  ✗ Works only for a two-state trajectory (old → new), not attributes that
    change frequently.

TYPE 3 WHEN TO USE:
  ✓ A simple "compare current vs previous" view is all that's needed
  ✓ The attribute changes at most once or twice in the entity's lifetime
  ✓ Simplicity matters more than full history
  ✓ Common case: sales territory reassignment — salesperson.territory shows
    current + previous territory, answering "how did revenue change after
    the territory reshuffle?"

Type 1: history not needed. Type 2: full point-in-time history needed.
Type 3: somewhere between — often superseded by Type 6 (hybrid).

dim_customer (current versions only — lean table):
  customer_sk  customer_id  city    tier    updated_at
  1            4201938      Austin  silver  2026-02-01

dim_customer_history (all historical versions):
  customer_history_sk  customer_id  city     tier    valid_from   valid_to
  100                  4201938      Seattle  silver  2024-01-15   2026-01-31
  101                  4201938      Austin   silver  2026-02-01   NULL

WHEN TYPE 4 IS USEFUL:
  ✓ Very large dimension tables where adding version rows slows down current queries
  ✓ 95% of queries only need current values, history table rarely joined
  ✓ Compliance / audit use cases requiring a separate history table by policy

LIMITATION: more complex to query — must choose between dim_customer
(current) and dim_customer_history (full history). Most teams prefer Type 2
since one table with version rows is simpler.

dim_customer (Type 6 — history + current value in every row):
  customer_sk      BIGINT PK      ← unique per version (surrogate)
  customer_id      BIGINT         ← natural key
  city             VARCHAR        ← city AS OF THIS VERSION (historical accuracy)
  current_city     VARCHAR        ← current city for all versions (Type 1 overwrite)
  previous_city    VARCHAR        ← previous city (Type 3)
  valid_from       DATE           ← when this version became active
  valid_to         DATE           ← when this version expired (NULL = current)
  is_current       BOOLEAN

TABLE STATE (customer moved Seattle → Austin):
  customer_sk  customer_id  city     current_city  valid_from   valid_to    is_current
  1            4201938      Seattle  Austin        2024-01-15   2026-01-31  FALSE
  2            4201938      Austin   Austin        2026-02-01   NULL        TRUE

NOTE: current_city = 'Austin' in BOTH rows, even the historical row.
      city = 'Seattle' in the historical row (point-in-time accurate).

-- Historical revenue by city (point-in-time accurate):
SELECT c.city AS historical_city, SUM(f.order_amount)
FROM fct_orders f JOIN dim_customer c ON f.customer_sk = c.customer_sk
GROUP BY c.city;
-- Uses c.city (version-specific) ← historically correct ✓

-- Current revenue by city (where customers ARE TODAY):
SELECT c.current_city, SUM(f.order_amount)
FROM fct_orders f JOIN dim_customer c ON f.customer_sk = c.customer_sk
GROUP BY c.current_city;
-- Uses c.current_city ← all orders attributed to Austin (where customer is now) ✓
-- No is_current=TRUE filter needed — current_city is in every row.
-- Both queries from ONE join — this is Type 6's key advantage over Type 2 alone.

-- Step 1: update current_city in ALL existing rows for this customer:
UPDATE dim_customer
SET current_city = 'Austin'    -- Type 1 overwrite on all versions
WHERE customer_id = 4201938;

-- Step 2: expire the current row + insert new version (Type 2):
UPDATE dim_customer
SET valid_to = '2026-01-31', is_current = FALSE
WHERE customer_id = 4201938 AND is_current = TRUE;

INSERT INTO dim_customer
    (customer_sk, customer_id, city, current_city, valid_from, valid_to, is_current)
VALUES (2, 4201938, 'Austin', 'Austin', '2026-02-01', NULL, TRUE);

-- dbt snapshot does NOT natively support Type 6 — it requires a custom
-- dbt macro or a Python pipeline.

dim_customer (pure Type 2 — no current_city column needed):
  customer_sk  customer_id  city     tier    valid_from   valid_to    is_current
  1            4201938      Seattle  silver  2024-01-15   2026-01-31  FALSE
  2            4201938      Austin   silver  2026-02-01   NULL        TRUE

fct_orders (with DUAL surrogate keys):
  order_sk  history_customer_sk  current_customer_sk  order_amount  order_date
  100       1                    2                     380.00       2024-06-10
  101       2                    2                     460.00       2026-03-01

  history_customer_sk: the SK active at order time (stored at fact load time)
  current_customer_sk: the SK of the current version (updated when customer changes)

-- Historical revenue by city (point-in-time accurate):
SELECT c.city, SUM(f.order_amount) FROM fct_orders f
JOIN dim_customer c ON f.history_customer_sk = c.customer_sk
GROUP BY c.city;
-- Order 100 → SK=1 → 'Seattle'. Order 101 → SK=2 → 'Austin' ✓

-- Current revenue by city (where customers are TODAY):
SELECT c.city, SUM(f.order_amount) FROM fct_orders f
JOIN dim_customer c ON f.current_customer_sk = c.customer_sk
WHERE c.is_current = TRUE
GROUP BY c.city;
-- Both orders join to SK=2 → 'Austin' — "revenue from customers now in Austin" ✓

TYPE 7 COMPLEXITY: requires updating current_customer_sk in the fact table
whenever a customer's current version changes — expensive for large fact
tables. Most teams avoid this unless the use case specifically requires it.
Type 6 is more common in practice (a current_city column in the dimension
row is cheaper to maintain than updating millions of fact table rows).

Does the attribute change?
  No → Type 0 (fixed)
  Yes → Does historical accuracy matter for analysis?
    No → Type 1 (overwrite)
    Yes → How many historical states do you need?
      "Just current and previous" → Type 3
      "Full history required" → Continue...
        Do you need both historical and current queries from the same join?
          No  → Type 2 (standard — use is_current filter when needed)
          Yes → Type 6 (add current_city column to dimension)
        Is the dimension table very large (10M+ rows)?
          Yes → Consider Type 4 (separate history table)

PRACTICAL GUIDANCE (2026):
  80% of use cases: TYPE 2 (with dbt snapshot)
  15% of use cases: TYPE 1 (for corrections and non-analytical attributes)
   5% of use cases: TYPE 6, 3, or 4 (special requirements)
  TYPE 7: almost never needed — Type 6 handles the same use case more simply

REAL EXAMPLES FROM FOOD DELIVERY PLATFORMS:
  customer.city:              Type 2 (revenue attribution changes with location)
  customer.tier:               Type 2 (LTV and cohort analysis by acquisition tier)
  customer.phone_masked:        Type 1 (contact info — never needed historically)
  customer.registration_date:   Type 0 (immutable — when they first joined)
  store.manager_name:           Type 1 (most reports — manager history not tracked)
  product.price:                NOT in dimension — put price in fact table as a fact

The growth team presents a report showing Austin revenue growing 50% in Q1 2026. The Seattle team disputes the numbers — several major customers they know personally appear to be attributed to Austin. You are asked to investigate.

Step 1-2 — checking the dimension, then Bronze CDC history

SELECT customer_id, city, valid_from, valid_to, is_current
FROM dim_customer
WHERE customer_id IN (4201938, 4201939, 4201940, 4201941)
ORDER BY customer_id;

-- Returns only ONE row per customer:
4201938  Austin  2024-01-15  NULL  TRUE
4201939  Austin  2024-03-02  NULL  TRUE

-- Only one row per customer — no version history. is_current=TRUE for all
-- (only one version exists). valid_from = registration date (no Type 2
-- tracking). dim_customer was built with Type 1 (overwrite) — not Type 2.

SELECT customer_id, city, updated_at, _change_type
FROM bronze.customers_cdc
WHERE customer_id = 4201938
ORDER BY updated_at;

4201938  Seattle  2024-01-15  insert  ← registered in Seattle
4201938  Austin   2026-02-01  update  ← moved to Austin

Confirmed: customer 4201938 was in Seattle until 2026-02-01. dim_customer
overwrote 'Seattle' with 'Austin' (Type 1). ALL historical orders from
2024 and 2025 now show city = 'Austin' — this is why Austin revenue looks
inflated and Seattle looks deflated.

Step 3 — estimating the impact, and the migration plan

SELECT c.city AS wrong_city, DATE_PART('year', f.order_date) AS year,
    COUNT(*) AS affected_orders, SUM(f.order_amount) AS misattributed_revenue
FROM fct_orders f
JOIN dim_customer c ON f.customer_sk = c.customer_sk
JOIN bronze.customers_cdc cdc ON f.customer_id = cdc.customer_id
    AND f.order_date < '2026-02-01'   -- orders placed before the move
WHERE c.city = 'Austin'          -- currently attributed to Austin
  AND cdc.city = 'Seattle'        -- but were actually in Seattle
  AND cdc._change_type = 'insert' -- initial registration city
GROUP BY 1, 2;

Shows: 18,234 orders, $1.47 million misattributed from Seattle to Austin

MIGRATION PLAN:
1. Build SCD Type 2 snapshot from Bronze CDC history
2. Rebuild dim_customer with version rows from CDC
3. Reload fct_orders — re-lookup customer_sk using the date-range join
4. Rebuild Gold revenue models
Full migration: 4 days of engineering, 1 day of validation

PREVENTION: dbt snapshot now runs hourly on the silver.customers source
using strategy='check', check_cols=['city', 'tier', 'state'] — future
city changes create a new version row automatically.

This is one of the most common SCD incidents in production — a dimension was built with Type 1 when the business question required Type 2. The data engineer who built it did not ask “do historical facts need to reflect the value that was true at the time?” Revenue attribution by city requires exactly that. The fix required rebuilding the dimension from the preserved Bronze CDC history — which is why preserving raw Bronze data is so valuable.