Slowly Changing Dimensions (SCD)
Techniques for handling changes to dimension attribute values over time in a data warehouse.
Last updated: 2026-04-12
Overview
Dimension attributes change: a customer moves cities, a product changes category, an employee transfers departments. In a dimensional model, how you handle these changes determines whether historical reports remain accurate.
Ralph Kimball defined the original SCD types. Later practitioners extended to types 4–7.
Type 0 — Retain Original
The attribute never changes in the DWH, even if it changes in the source.
- Use: Attributes that should always reflect the original value (e.g., original signup channel)
- Storage impact: None
Type 1 — Overwrite
Replace the old value with the new value. No history is kept.
- Use: Corrections (fixing a typo in a name), or when history is irrelevant
- Effect: Historical fact rows now reflect the current attribute value, not the value at time of event
- Storage impact: None — same row count
Type 2 — Add New Row (Full History)
Insert a new dimension row for each change. Use surrogate keys + effective date range to track versions.
- Columns added:
effective_date,expiry_date,is_currentflag - Use: When you need “what was the customer’s city when they placed this order?”
- Effect: Historical fact rows remain correctly associated with the dimension row that was current at the time
- Storage impact: Row count grows with each change
- Most commonly used SCD type
Example — DimCustomer:
| surrogate_key | customer_id | city | effective_date | expiry_date | is_current |
|---|---|---|---|---|---|
| 1001 | C42 | Chicago | 2020-01-01 | 2023-05-31 | N |
| 1002 | C42 | Seattle | 2023-06-01 | 9999-12-31 | Y |
Type 3 — Add New Column (Partial History)
Add a “previous value” column alongside the current value column.
- Use: When only one prior value matters (e.g., current_region + prior_region)
- Limitation: Only stores one level of history; older history is lost on the next change
- Storage impact: Adds columns, not rows
Type 4 — History Table
Keep the main dimension table as Type 1 (current values only), but maintain a separate history table with all versions.
- Use: When most queries only need current values but some need history
- Storage impact: Separate table; history table grows
Type 5 — Mini-dimension + Type 1 Outrigger
Split rapidly-changing attributes (age band, income band) into a separate mini-dimension, and embed the current mini-dimension key in the main dimension row (Type 1 outrigger).
- Use: High-cardinality or frequently changing profiling attributes that would cause extreme Type 2 row explosion
Type 6 — Hybrid (Type 1 + 2 + 3)
Add Type 2 rows for full history AND add a “current value” column on all rows (Type 1 overwrite) so any version of the row shows the current state for easy “what is current?” queries.
- Use: When you frequently need both “value at time of event” and “current value” in the same query
- Also called: Type 1+2+3 or “the hybrid type”
Type 7 — Dual Type 1 + Type 2
Maintain both a current-view and full-history view via the dimension, with the fact table linking to the surrogate key (Type 2) but also offering a current natural key join for Type 1 queries.
- Use: Maximum flexibility; querying tools can choose which view to use
Choosing a Type
| Need | Type |
|---|---|
| No history needed | 1 |
| Full history, historical reports accurate | 2 |
| Only previous value matters | 3 |
| Current-only performance + separate history | 4 |
| Rapid change, profiling attributes | 5 |
| Current + history in same query | 6 |
Connections
- dimensional-modeling — the methodology SCD types belong to
- star-schema — the schema where dimension tables live
- fact-table-types — fact tables reference dimension surrogate keys affected by SCD
- ralph-kimball — defined SCD types 0–3
Sources
- Dimensional Modeling — Joakim Dalby — added 2026-04-12