Capstone: A Type-2 SCD Loader

A production loader composes every Level-4 skill

Everything in Level 4 so far was a component. A real daily loader composes them in order. Here is the anatomy of a production dim_customer Type-2 loader processing one daily dump:

1. Dedup the source (Module 4.4). A raw dump has duplicate natural keys; reduce it to one row per customer, keeping the freshest with ROW_NUMBER() OVER (PARTITION BY customer_code ORDER BY updated_at DESC) and keeping rn = 1. Everything downstream reads this clean set, never the raw dump.
2. Apply SCD Type 2 (Module 4.3). For each deduped customer whose tracked attribute changed versus the current dimension row: expire the old row (effective_to = snapshot_date, is_current = 0) and insert a new version (fresh surrogate key, effective_from = snapshot_date, effective_to = '9999-12-31', is_current = 1). Brand-new customers get one current row. Unchanged (and absent) customers are left untouched.
3. Assert quality (Module 4.5). Run zero-row DQ tests: exactly one is_current = 1 per customer, no orphan facts against the dimension, contiguous validity windows.
4. Verify performance (the EXPLAIN module). The fact→dim as-of join should be an index seek, so index the join key.
5. Idempotency (Module 4.4). The whole script, run twice on the same dump, must leave the dimension byte-identical: the second pass detects no changes and inserts nothing.

The one property everything depends on: idempotent change-detection

The single most important correctness property is idempotency of the SCD2 step. Change-detection must compare the dump against the current dimension row, and you must snapshot the changed set into a temp table before you expire or insert; otherwise the insert creates rows that a naive second pass re-reads as "changes," and you get infinite version growth on re-runs.

-- 1. dedup -> clean_dump (one row per code, latest updated_at)
DROP TABLE IF EXISTS clean_dump;
CREATE TEMP TABLE clean_dump AS
WITH ranked AS (
  SELECT customer_code, city, updated_at,
         ROW_NUMBER() OVER (PARTITION BY customer_code ORDER BY updated_at DESC) AS rn
  FROM raw_dump
)
SELECT customer_code, city FROM ranked WHERE rn = 1;

-- 2. FREEZE the changed set BEFORE mutating anything
DROP TABLE IF EXISTS changed;
CREATE TEMP TABLE changed AS
SELECT c.customer_code, c.city
FROM clean_dump c
JOIN dim_customer d ON d.customer_code = c.customer_code AND d.is_current = 1
WHERE c.city IS NOT d.city;   -- null-safe (Module 4.3): also catches NULL<->value city changes

-- 3. expire the old versions, then 4. insert the new ones, both driven by 'changed'
UPDATE dim_customer SET effective_to = '2026-03-01', is_current = 0
WHERE is_current = 1 AND customer_code IN (SELECT customer_code FROM changed);
INSERT INTO dim_customer (customer_code, city, effective_from, effective_to, is_current)
SELECT customer_code, city, '2026-03-01', '9999-12-31', 1 FROM changed;

On the second run changed is empty (the current city already equals the dump) so the expire and insert both no-op. Brand-new customers are the mirror image: freeze the codes that have no row in the dimension at all, and insert one current version for each (never run the expire step for them).

Two guards keep the whole script re-runnable, not just the SCD2 step:

• Lead every temp table with DROP TABLE IF EXISTS …; so a second pass doesn't error on "table already exists."
• Populate a recomputable target (fact_sales.customer_key, a dq_results gate) with an idempotent UPDATE or a DELETE + re-INSERT, never a blind INSERT that doubles rows on the second pass.

Resolve each fact to the surrogate key that was valid on its sale date with a half-open as-of join (f.sale_date >= d.effective_from AND f.sale_date < d.effective_to) as an UPDATE, so re-running recomputes the identical key. Then index the join column and confirm the plan with EXPLAIN QUERY PLAN; you're looking for a SEARCH … USING INDEX (a seek), not a SCAN.

In the warehouse: SQLite has no QUALIFY, so you filter ROW_NUMBER() in a wrapping subquery/CTE (WHERE rn = 1). Snowflake and BigQuery let you write QUALIFY ROW_NUMBER() OVER (…) = 1 in one line: same dedup, less nesting.

In the warehouse: this entire loader is what dbt's snapshot (SCD2) plus MERGE-based incremental models plus schema tests generate for you, but building it by hand once is exactly how you learn what those tools are doing, and it's the interview question behind "walk me through a Type-2 dimension load."

Recap: a production loader = dedup → SCD2 (snapshot-then-expire-then-insert) → DQ assertions → plan verification, all engineered so re-running the whole script changes nothing. Idempotent change-detection against the current row is the property everything else depends on.

Execution mode: you write a multi-statement script. It runs against a fresh seeded SQLite DB, then hidden assertion queries check the dimension, the as-of fact keys, the DQ gate, and the join index. The grader re-runs your whole script to prove it changes nothing the second time.