What are SCD types 1, 2 and 3? Slowly changing dimensions explained

Your customer moved. Your warehouse says they did not. Or worse, it says they always lived in Lyon, and all of 2024's Paris revenue moved with them. Dimensions change, facts point at them, and the SCD type is the decision about which version each fact sees. One page on the three types and the join that doubles revenue. The print-ready A4 PDF is at the bottom.

The problem

• Dimensions change: city, plan, segment.
• Facts point at them, so which version does an order see?
• Slowly changing means slowly, not never.

Type 1

• Overwrite the row: no history, cheap.
• Good for typo fixes and names.
• Reports rewrite themselves: last year moves too.

Type 2

• New row per change: full history.
• valid_from / valid_to, plus an is_current flag.
• A surrogate key per version: facts join on this one.

Type 3

• A previous_ column: one step back only.
• Good for a rare reorg.
• Gets ugly at change number two.

Pick one

• Default to type 2: history is cheap now.
• Use type 1 inside it, per column, for corrections: the hybrid.
• dbt snapshots give you type 2 in about 10 lines.

Type 2, in two statements

-- customer 42 moved from Paris to Lyon today
UPDATE dim_customer
   SET valid_to = '2026-10-04', is_current = false
 WHERE customer_id = 42 AND is_current;

INSERT INTO dim_customer
  (customer_sk, customer_id, city,
   valid_from, valid_to, is_current)
VALUES (9871, 42, 'Lyon',
        '2026-10-04', '9999-12-31', true);
-- 2024 revenue still sits on the Paris row (sk 3310)

Close the current version, open a new one. The natural key (customer_id) now matches two rows. Every fact table joins on customer_sk, so each order keeps the city it had when it happened.

Gotchas

• Join on the natural key and each fact fans out once per version.
• No end date: use 9999-12-31, not NULL, or the current filter drops rows.
• Late changes: backfill the versions, do not just append.

The trap: the report that changed overnight

The quiz

Which join doubles your revenue?

A) JOIN dim_customer d
   ON f.customer_id = d.customer_id

B) JOIN dim_customer d
   ON f.customer_sk = d.customer_sk

A. The natural key matches every version of the customer, so each fact row fans out once per version. Facts join on the surrogate key, B.

Interview phrasing worth memorizing: I default to type 2 with a surrogate key and an is_current flag, and I only use type 1 for corrections that should rewrite history.