Data Modeling/Modeling for Scale
Slowly Changing Dimensions (SCDs)
Dimension attributes change over time — a customer moves cities, a product's price changes. SCD types define how a dimensional model should handle that change, and why the choice affects historical reporting.
Dimension tables aren't static. A customer's address changes, a product moves to a different category, an employee gets promoted. Slowly Changing Dimension (SCD) types are a standard vocabulary for how a dimensional model handles these changes — specifically, whether history is preserved or overwritten.
Type 1: overwrite
The simplest approach: when an attribute changes, update the row in place. The dimension table always reflects the current state, and history is lost — if you re-run a report on last year's sales by region, a customer who moved regions this year will show up under their new region even for historical orders. Use Type 1 when history genuinely doesn't matter for that attribute (e.g. correcting a typo in a name).
Type 2: add a new row
The most common approach for attributes where history matters: instead of overwriting, insert a new row representing the new state, and mark the old row as expired. Each version of the customer gets its own surrogate key, along with effective_date and end_date (or is_current) columns. Fact rows link to whichever version of the dimension was current when the event happened — so historical reports stay accurate to the state at the time.
-- Type 2: two rows for the same customer, representing a region change
surrogate_key | customer_id | region | effective_date | end_date | is_current
101 | C-88 | West | 2024-01-01 | 2025-06-14 | false
205 | C-88 | East | 2025-06-15 | 9999-12-31 | trueType 3: add a new column
A middle ground: keep both the current and one previous value as separate columns (current_region, previous_region) on the same row. This preserves one step of history without growing the table, but can't track more than a single prior change — rarely used outside specific reporting needs like 'this quarter vs. last quarter.'
- Type 1 (overwrite): simplest, no history, use when past state doesn't matter
- Type 2 (new row): full history, most common in practice, requires surrogate keys and effective-dating
- Type 3 (new column): limited history (one prior value), rarely sufficient alone
- Hybrid approaches exist — e.g. Type 2 for some attributes, Type 1 for others on the same dimension
Tip — The question that decides the type
Ask: 'if this attribute changes, should historical facts be reported under the old value or the new value?' If old value: Type 2. If new value: Type 1. This single question resolves almost every SCD design debate.
SCDs are a good example of why dimensional modeling is a genuine design discipline rather than just 'normalize less': getting history handling right (or explicitly deciding you don't need it) has a direct, visible impact on whether reports are trustworthy.