Slowly changing dimensions

The overwrite lie is sneaky because nothing errors. The query runs, the dashboard renders, the numbers look plausible. It surfaces weeks later as a trust incident: finance's saved Q1 deck disagrees with the live Q1 dashboard, and now every number in the warehouse is suspect.

• Facts are safe by nature: an order happened on a date, at a price. Nobody edits history there.
• Dimensions drift by nature: customers move cities, upgrade plans, change names. Products get repriced and recategorized. The change is real and legitimate - the question is how to record it.
• "Slowly changing" is Kimball's name for exactly this: attributes that change occasionally, not constantly, and whose history may matter.

Not every change deserves history. If a customer's name was misspelled at signup, the old value was never true - fixing it everywhere, including the past, is the honest move. The test question: "was the old value ever correct?" If no, overwrite freely (that is Type 1, next part). If yes, and anyone might slice facts by it, you owe the warehouse a history.

Back in b4 you built dim_customer with a generated customer_key instead of joining facts straight to customer_id. Tonight is the payoff. Type 2 means one customer becomes many rows - and many rows cannot share a primary key.

• Natural key (customer_id = 2): identifies the person. Stays the same across every version. Nicknamed the durable key.
• Surrogate key (customer_key = 2, then 16): identifies one version of the person. Every new version gets a fresh key.
• A fact row loaded in March points at the version key that was current in March - so old facts stay attached to old attribute values automatically, with a plain key join.

If your facts join on the natural key instead, history is still recoverable - but every query must do the date-range dance you will meet in Part 3. Both patterns exist in the wild; know which one your shop uses.

• Was the old value ever true? No: Type 1. Yes: keep going.
• Will anyone slice facts by this attribute's past values? Yes: Type 2. No: Type 1 is defensible.
• Is the attribute a birth fact (signup date, first channel)? Type 0 - freeze it.
• Do analysts only ever ask "current vs immediately previous"? Type 3 fits, but ask twice - the second change destroys the first history.

The scd setup adds dim_customer_scd to your star: same attributes as dim_customer, plus the three history columns. Right now every customer has exactly one row, valid from their signup date, open-ended, current.

SELECT customer_id, name, plan, valid_from, valid_to, is_current
FROM dim_customer_scd
WHERE is_current
ORDER BY customer_id
LIMIT 6;

SELECT customer_id, count(*) AS versions
FROM dim_customer_scd
GROUP BY customer_id
ORDER BY versions DESC, customer_id
LIMIT 5;

Current rows keep valid_to NULL - honest, because the window has no known end. But date BETWEEN x AND NULL evaluates to NULL, which silently drops rows. Hence the idiom: coalesce(valid_to, DATE '9999-12-31') converts "no end yet" into "the end of time" only at query time. Some teams store 9999-12-31 physically instead of NULL; either works, pick one and write it down in the model docs.

Tonight's snapshot carries two stories: Liam (exists, plan changed) and Ivy Nolan (customer 16, never seen before). Watch the ON clause - it matches only current rows whose plan actually differs, so unchanged customers pass through untouched. Batch keys land at durable id + 100 so you can spot tonight's arrivals at a glance.

MERGE INTO dim_customer_scd t
USING (
  SELECT * FROM (VALUES
    (2,  'Liam Ford', 'Austin', 'USA',   'Pro'),
    (16, 'Ivy Nolan', 'Osaka',  'Japan', 'Basic')
  ) v(customer_id, name, city, country, plan)
) src
ON t.customer_id = src.customer_id
   AND t.is_current AND t.plan <> src.plan
WHEN MATCHED THEN
  UPDATE SET valid_to = DATE '2026-06-30', is_current = FALSE
WHEN NOT MATCHED THEN
  INSERT VALUES (src.customer_id + 100, src.customer_id, src.name,
                 src.city, src.country, src.plan,
                 DATE '2026-07-01', NULL, TRUE);

INSERT INTO dim_customer_scd
SELECT d.customer_key + 100, d.customer_id, d.name, d.city, d.country,
       'Pro', DATE '2026-07-01', NULL, TRUE
FROM dim_customer_scd d
WHERE d.customer_id = 2 AND d.valid_to = DATE '2026-06-30';

SELECT customer_id, plan, valid_from, valid_to, is_current
FROM dim_customer_scd
WHERE customer_id IN (2, 16)
ORDER BY customer_id, valid_from;

Read the result: Liam has two rows (Basic sealed, Pro open), Ivy has one fresh row. Three situations - changed, new, unchanged - one repeatable script. This exact reconcile shape returns as the loading workhorse in b6.

Zoe Tan (customer 14) relocates on July 1. Write the three-move Type 2 change yourself before you peek - close out, insert with the new city and country, read the timeline. The box below holds a working answer.

UPDATE dim_customer_scd
SET valid_to = DATE '2026-06-30', is_current = FALSE
WHERE customer_id = 14 AND is_current;

INSERT INTO dim_customer_scd
SELECT (SELECT max(customer_key) + 1 FROM dim_customer_scd),
       customer_id, name, 'Tokyo', 'Japan',
       plan, DATE '2026-07-01', NULL, TRUE
FROM stg_customers
WHERE customer_id = 14;

SELECT customer_id, city, country, valid_from, valid_to, is_current
FROM dim_customer_scd
WHERE customer_id = 14
ORDER BY valid_from;

The BETWEEN join answers per-fact questions too. After the upgrade, ask the warehouse what plan Liam was on the day each of his orders happened - both answer Basic, because both predate July. Any order he places from July onward will answer Pro, with no query change.

UPDATE dim_customer_scd
SET valid_to = DATE '2026-06-30', is_current = FALSE
WHERE customer_id = 2 AND is_current;

INSERT INTO dim_customer_scd
SELECT (SELECT max(customer_key) + 1 FROM dim_customer_scd),
       customer_id, name, city, country,
       'Pro', DATE '2026-07-01', NULL, TRUE
FROM stg_customers
WHERE customer_id = 2;

SELECT f.order_id, f.date_key, d.plan AS plan_when_ordered
FROM fct_order_line f
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_customer_scd d
  ON c.customer_id = d.customer_id
 AND f.date_key BETWEEN d.valid_from
                    AND coalesce(d.valid_to, DATE '9999-12-31')
WHERE c.customer_id = 2
ORDER BY f.date_key;