Orientation
What You'll Master Here
Dimensions describe the world, and the world changes: a customer moves state, a product is re-categorized, a salesperson switches regions. The question slowly changing dimensions (SCDs) answer is deceptively deep, when an attribute changes, what should happen to history? Overwrite it, or preserve what was true before?
This is the single most asked data-modeling interview topic, and the most commonly botched in practice. You will learn the full menu, Types 0 through 6, with the trade-offs of each, then go deep on Type 2 (the workhorse) and how to query it correctly with point-in-time joins.
Every type is shown with the exact rows it produces and the exact query that reads it, because SCD bugs are silent: a report attributes a March sale to the customer’s current state instead of their state in March, and nobody notices until the numbers are challenged.
Why it matters
Whether history is preserved decides if you can ever answer "what was true then?". Choosing the wrong SCD type means either losing history forever or bloating dimensions needlessly, and it is very expensive to change after data accumulates.
Core mental model
When a dimension attribute changes, you choose: overwrite (lose history), add a row (keep full history), or add a column (keep limited history).
- slowly changing dimension
- A dimension whose attributes change over time, requiring a history strategy.
- Type 1
- Overwrite the attribute; no history is kept.
- Type 2
- Add a new row with effective dates; full history is preserved.
- effective dating
- valid_from / valid_to columns that mark when a row’s values were true.
Common mistake
Overwriting dimension attributes everywhere by default.
History is destroyed: past facts silently re-attribute to current attribute values, corrupting trends.
Better habit
- Decide a history strategy per attribute, not per table.
- Default important descriptive attributes to Type 2.
- Always store the surrogate key on facts so history is queryable.
SCD is one question with several answers: when a value changes, do you keep the old one? Type 1 says no, Type 2 says yes (new row), Type 3 says "just the last one". Everything else is a variation.
Follow one example throughout: customer 7 moves from NY to CA on 2026-06-01. Watch what each SCD type does to the rows and to a March sale.
Practice prompts
- Name an attribute where losing history would be a serious problem.
- State the core difference between Type 1 and Type 2 in one sentence.
Remember this
SCDs decide what happens to history when a dimension attribute changes; the choice (overwrite, add row, add column) determines whether you can ever report the past accurately.