Slowly Changing Dimensions: Type 2

SCD Type 2: keep history instead of overwriting

Type 1 overwrites and forgets. But finance often needs to attribute a sale to the customer's attributes as they were on the sale date: if Ada lived in London in January and Berlin in March, a January order must stay attributed to London. That requires keeping history, and that's SCD Type 2: instead of overwriting, you expire the old row and insert a new version with a fresh surrogate key. The dimension grows one row per change, and each row carries a validity window.

Table

Ada after the London -> Berlin change. The old row is expired (effective_to set to the change date, is_current 0); the new version opens with a fresh window and is_current 1. Exactly one current row per key, and the windows abut so an as-of join matches exactly one version.

email	city	effective_from	effective_to	is_current
a@x.com	London	2026-01-01	2026-03-01	0
a@x.com	Berlin	2026-03-01	9999-12-31	1

Three columns make Type 2 work:

• effective_from: the date this version became true.
• effective_to: the date it stopped being true (a far-future sentinel like '9999-12-31' while still current).
• is_current: a 1/0 flag; exactly one current row per natural key.

Each version also gets its own new surrogate key, so a fact table can point at the specific version valid as of the event date. That's the whole point: fact.customer_key references the version that was current when the sale happened, not the latest one.

The apply algorithm

When a fresh source dump arrives, for each natural key whose tracked attributes changed:

1. Expire the current row: set effective_to = <change_date> and is_current = 0 on the row where is_current = 1.
2. Insert a new version: the new attribute values, effective_from = <change_date>, effective_to = '9999-12-31', is_current = 1, a fresh surrogate key.

Unchanged keys are left alone; brand-new keys get a single current row.

Worked example

-- Ada moves from London to Berlin, effective 2026-03-01.

-- Step 1: expire the old current row
UPDATE dim_customer
SET effective_to = '2026-03-01', is_current = 0
WHERE email = 'a@x.com' AND is_current = 1;

-- Step 2: insert the new version with a fresh surrogate key
INSERT INTO dim_customer (email, name, city, effective_from, effective_to, is_current)
VALUES ('a@x.com', 'Ada', 'Berlin', '2026-03-01', '9999-12-31', 1);

After this, dim_customer has two rows for Ada: [London, 2026-01-01 → 2026-03-01, is_current=0] and [Berlin, 2026-03-01 → 9999-12-31, is_current=1]. A fact row dated 2026-02-10 joins to the London version because 2026-02-10 falls in [effective_from, effective_to); a fact dated 2026-03-15 joins to Berlin.

Anatomy of the as-of join (how facts use Type 2)

SELECT f.sale_id, d.city
FROM fact_sales f
JOIN dim_customer d
  ON d.email = f.email
 AND f.sale_date >= d.effective_from
 AND f.sale_date <  d.effective_to;     -- half-open window: [from, to)

The >= effective_from AND < effective_to is the as-of predicate: it selects the one version valid on the sale date. Use a half-open interval (< effective_to, not <=) so the boundary date belongs to exactly one version and rows never double-count.

Detecting a changed attribute safely (NULL-aware)

A general apply compares today's dump to the current dimension row and versions only the keys whose tracked attribute changed. The trap: an attribute can go from NULL to a value (the city was unknown at first load, now it's filled in) or from a value to NULL. Plain <> is not null-safe: 'Madrid' <> NULL evaluates to NULL, not TRUE, so the changed-set filter drops the row, treats it as unchanged, and no new version is created. That's a silently lost history record.

Use a null-safe change predicate instead. In SQLite that's the two-operand IS / IS NOT, which compare NULLs as ordinary values: 'Madrid' IS NOT NULL is TRUE, NULL IS NOT NULL is FALSE.

-- WRONG: NULL <-> value transitions are missed (the predicate is NULL, not TRUE)
WHERE stg.city <> dim.city
-- RIGHT: null-safe, catches NULL->value and value->NULL as well as value->value
WHERE stg.city IS NOT dim.city

In the warehouse: the ANSI spelling is a IS DISTINCT FROM b (Postgres, Snowflake, BigQuery, and SQLite 3.39+ all support it), the same null-safe "did it actually change?" test. dbt snapshots use it to pick the rows to version, precisely so a NULL becoming populated still opens a new row. Never write raw <> in SCD change detection.

Common pitfalls

• More than one is_current = 1 per key is the #1 Type 2 bug: it means an update ran the insert without expiring the old row, and every downstream WHERE is_current = 1 now doubles. Graders assert exactly one current row per key.
• Overlapping windows (old row's effective_to ≠ new row's effective_from) make the as-of join match two versions or none. Set the expiring row's effective_to equal to the new row's effective_from.
• Closed intervals double-count. If both versions include the boundary date (<=), a sale on that exact day joins twice. Always half-open.
• Expiring on the wrong key. WHERE email = ? AND is_current = 1: forgetting is_current = 1 expires all historical versions.

In the warehouse: Snowflake/BigQuery implement the whole Type 2 apply as a single MERGE with WHEN MATCHED THEN UPDATE (expire) plus an INSERT for the new version, often generated by dbt's snapshot macro. The two-step expire-then-insert logic is identical; only the statement packaging differs. TRUE/FALSE are real booleans there; in SQLite is_current is 1/0.

Recap: SCD Type 2 keeps history by expiring the old row (effective_to = change_date, is_current = 0) and inserting a new version (fresh surrogate key, effective_from = change_date, effective_to = '9999-12-31', is_current = 1); facts join to the version valid on the event date via a half-open [effective_from, effective_to) as-of predicate, and there must be exactly one is_current = 1 per natural key.

Execution mode: you write a multi-statement script. It runs against a fresh seeded SQLite DB, then hidden assertion queries check the version count, the current flag, the validity windows, and idempotency.