Dimension attributes change — a customer moves, a product is recategorized. How you handle that history (SCD Type 1, 2, or 3) decides whether your historical reports stay correct.
A fact is a frozen event, but the dimensions around it drift: a customer relocates, a product moves category, a sales rep changes territory. The question a Slowly Changing Dimension (SCD) strategy answers is: when an attribute changes, do we overwrite it, or preserve what it was at the time each fact happened? Choosing wrong silently rewrites history.
A customer in California in 2023 (10 orders) moves to Texas in 2024.
Report: "2023 revenue by state."
If you OVERWRITE region to Texas -> those 10 old orders now count as
Texas revenue. 2023 history is silently WRONG.
The SCD type you pick decides whether the past stays true. This is one
of the most common — and most damaging — data-engineering mistakes.SCD Type 1 simply updates the attribute in place, keeping no history. It is correct when the old value was an error (a fixed typo) or when history genuinely does not matter. It is fast and simple, but destroys the ability to report “as it was,” so use it deliberately, not by default.
-- Type 1: overwrite in place. History is lost.
UPDATE dim_customer
SET region = 'Texas'
WHERE customer_key = 42;
-- Use when: correcting an error, or history truly doesn't matter
-- (e.g. fixing a misspelled name). Simple, but past reports shift.SCD Type 2 is the workhorse for history: instead of updating, you expire the current row and insert a new version, so each fact links to the dimension version that was current when it happened. It costs more rows and a surrogate key per version, but it keeps every historical report correct. This is the default choice when history matters.
-- dim_customer with Type 2 versioning columns
-- customer_key (surrogate PK, unique per VERSION)
-- customer_id (natural/business key, stable across versions)
-- valid_from, valid_to, is_current
-- 1) expire the current version
UPDATE dim_customer
SET valid_to = now(), is_current = false
WHERE customer_id = 1001 AND is_current;
-- 2) insert the new version with a NEW surrogate key
INSERT INTO dim_customer (customer_id, region, valid_from, valid_to, is_current)
VALUES (1001, 'Texas', now(), NULL, true);
-- Old facts point at the old customer_key (California). History stays correct.SCD Type 3 keeps a limited history by storing a “previous” column alongside the current one — useful only when you need to compare exactly one prior value (e.g. previous vs current sales territory). In practice you decide per attribute: overwrite the ones where history is noise, Type 2 the ones where it matters. Tools like dbt snapshots automate Type 2, which the dbt course covers.
TYPE 3 — keep current + one prior value in extra columns:
dim_rep(rep_key, name, current_region, previous_region)
Good for "compare this region vs the last one". Limited: only 1 step back.
Choosing per attribute:
attribute changes are errors / irrelevant -> Type 1 (overwrite)
you must report history correctly -> Type 2 (new row) <- default
you only need current vs immediately prior -> Type 3 (extra column)
Automating Type 2: dbt 'snapshots' capture versioned history for you
(see the dbt course). Don't hand-roll this if a tool does it.