What You'll Master Here
overwrite (lose history), add a row (keep full history), or add a column (keep limited history).
Dimensions describe the world, and the world changes: a customer moves state, a store is re-districted, a subscriber switches tariff. Slowly changing dimensions (SCDs) are the named strategies for one question — when an attribute changes, what happens to history?
Apply each type to the same move below and watch two things move with it: the rows in dim_customer, and the answer to a Q1 question that has only one true answer. The selector opens on Type 1, the one you get by accident.
When a dimension attribute changes, you choose: overwrite (lose history), add a row (keep full history), or add a column (keep limited history).
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.
- 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.
| customer_key | customer_id | state |
|---|---|---|
| 501 | C-7 | CA |
| sale_id | customer_id | sale_date | amount |
|---|---|---|---|
| s1 | C-7 | 2026-02-14 | 40.00 |
| s2 | C-7 | 2026-03-10 | 25.00 |
| s3 | C-7 | 2026-07-02 | 60.00 |
“What were Q1 2026 sales by state?”
The two Q1 sales total 65.00 and were both earned while C-7 lived in New York. Any answer other than NY 65.00 is wrong.What the report returns| state | q1_revenue |
|---|---|
| CA | 65.00 |
- Q1 as it was · wrong, and silently so
- Where C-7 lives now · answered correctly
Overwriting dimension attributes everywhere by default. History is destroyed: past facts silently re-attribute to current attribute values, corrupting trends.
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 the most asked dimensional-modeling interview topic because it is the cheapest way to tell whether a candidate has ever had to defend a historical number. The follow-up is always the same: which type, and for which attribute?
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.