Slowly Changing Dimensions (SCD) Explained
Slowly changing dimensions are what every data engineer eventually runs into: the moment you realize your dimension table only stores today’s values and someone is asking about the past. Understanding SCD types comes down to one design decision you make every time a dimension attribute changes.
What a slowly changing dimension is
In a star schema, dimension tables hold the descriptive context around your measurements — a customer’s city, a product’s category, a salesperson’s region. Those attributes aren’t permanent. A customer moves. A product gets re-categorized. A rep transfers territories.
When that happens, your table has to decide what to do with the old value. The “slowly” just means changes are infrequent relative to the fact table. The three SCD types are the three sensible responses.
The core problem: overwrite or keep history?
Every SCD decision is one business question: when an attribute changes, what happens to the old value? There are three answers:
- Overwrite it — the old value is replaced; only the new state exists.
- Keep the full history — the old row is retained and a new row is added, each stamped with its valid date range.
- Keep just the previous value — the same row is updated but a “previous” column holds the one prior state.
Those map to Type 1, 2, and 3. The right choice depends on whether your reports need to be reproducible across time — teams usually discover they needed Type 2 the day a historical answer is simply gone.
SCD Type 1: overwrite the old value
Type 1 is the simplest: run an UPDATE on the existing row. The old value disappears — no new row, no history, current state only.
This is appropriate when the old value was wrong or genuinely irrelevant: correcting a typo, fixing a miscoded identifier. Where Type 1 causes problems is when teams apply it to attributes that do change meaningfully — a sales territory, a price tier — and then find they can’t explain historical revenue shifts.
SCD Type 2: add a new row with validity dates
Type 2 is the workhorse. Instead of updating the existing row, you close it with an expiry date and insert a new row. Both rows stay in the table — the history is fully preserved.
Three extra columns are required:
- effective_from (or valid_from) — when this version became active.
- effective_to (or valid_to) — when it was superseded;
NULLon the current row. - is_current — a boolean so queries can filter to today’s version without a date comparison.
Type 2 also requires a surrogate key — a system-generated integer separate from the natural business key. Once a second row exists for the same customer, the original customer_id is no longer unique. Your fact table’s foreign key must point at the surrogate so each historical transaction joins to the correct version of the customer row.
Here’s dim_customer after a customer moves from Austin to Denver:
| sk_customer | customer_id | city | effective_from | effective_to | is_current |
|---|---|---|---|---|---|
| 101 | C-42 | Austin | 2024-01-01 | 2025-06-30 | false |
| 102 | C-42 | Denver | 2025-07-01 | NULL | true |
Any fact row keyed to sk_customer = 101 will always join to “Austin” — the world as it was when that transaction happened. Past reports stay reproducible regardless of later moves.
SCD Type 3: add a column for the previous value
Type 3 takes a different approach: no new rows, just a new column. You add a previous_city column alongside the current one. When a value changes, the current value shifts into the “previous” column before the new value is written.
The trade-off is explicit: you can see the current value and the one immediately before it, but anything older is lost. Type 3 suits a narrow situation — a planned, one-time reorganization where a before–after comparison is needed for a defined window. For ongoing or unpredictable changes, Type 2 is nearly always the better fit.
Type 1 vs 2 vs 3 side by side
| Type | What happens | History kept | When to use |
|---|---|---|---|
| Type 1 | Overwrites the row in place | None — old value is gone | Typo fixes; irrelevant attributes |
| Type 2 | Closes old row; inserts new row with dates | Full history, indefinitely | Most reporting; past accuracy matters |
| Type 3 | Updates in place; prior value stored in a column | One transition (current & previous) | Single planned change; before–after view |
Most warehouse models use Type 1 for corrections and Type 2 for any attribute carrying reporting significance. Type 3 is rare.
How to implement it (and common pitfalls)
The Type 2 pattern is consistent across tools:
- Generate a surrogate key per row (auto-increment or a hash of natural key +
effective_from). - On each load, compare incoming rows against the current dimension. Where something changed, close the old row (
effective_to = today,is_current = false) and insert the new version (effective_from = today,effective_to = NULL,is_current = true). - Point the fact table’s foreign key at the surrogate, not the natural business key.
dbt snapshots automate this exactly. Set strategy: timestamp or strategy: check and dbt manages dbt_valid_from, dbt_valid_to, and dbt_scd_id on each run. Snapshots implement Type 2 only — Type 3 column-shifting you write yourself.
The most common pitfall: forgetting WHERE is_current = true (or WHERE effective_to IS NULL) on current-state queries. Without it, a join matches every historical version of each row and silently multiplies your metrics. If your row counts look oddly high after adding Type 2, that filter is almost always the fix. For deduplication patterns using window functions, see our SQL window functions guide.
Frequently asked questions
What is the difference between SCD Type 1 and Type 2?
Type 1 overwrites the existing row — the old value is gone and the table only reflects current state. Type 2 keeps the old row and inserts a new one with validity dates, preserving the full history. Type 1 is simpler; Type 2 adds rows, a surrogate key, and the requirement to filter is_current = true on every current-state query.
What are SCD Type 0, Type 4, and Type 6?
Type 0 means the attribute never changes — fixed at creation (like a birth date). Type 4 offloads historical rows to a separate history table, keeping only the current row in the main dimension. Type 6 is a hybrid of 1, 2, and 3 applied together — named because 1 + 2 + 3 = 6. All three are real but far rarer than the core three types.
How does dbt handle slowly changing dimensions?
dbt implements Type 2 via snapshots. Configure a snapshot pointing at a source model, choose a strategy (timestamp or check), and dbt maintains dbt_valid_from, dbt_valid_to, and dbt_scd_id automatically. There is no native Type 3 support; any column-shifting logic lives in a model built on top of the snapshot output.
Which SCD type should I use by default?
Default to Type 2 for any attribute that affects reporting — region, segment, category, tier. Default to Type 1 only when the old value was wrong or is genuinely irrelevant to any query. When in doubt, Type 2 is safer: it’s far easier to ignore history you stored than to reconstruct history you didn’t.
Slowly changing dimensions feel abstract until the day a stakeholder asks why last quarter’s split looks different now — and the answer is gone. For the broader context on dimension tables, see the star schema guide and the modern data stack overview.
Last updated: July 6, 2026
Comments
Post a Comment