Copyright (c) 2026 MindMesh Academy. All rights reserved. This content is proprietary and may not be reproduced or distributed without permission.
2.1.3. Slowly Changing Dimensions (SCDs)
💡 First Principle: Dimensions change over time. SCD types determine how to handle historical tracking—from ignoring history (Type 1) to complete historical tracking (Type 2).
Scenario: A customer moves from New York to Los Angeles. Should historical sales show New York (where they were at purchase time) or Los Angeles (current address)?
SCD Types
| Type | Behavior | Use Case |
|---|---|---|
| Type 0 | Never update | Fixed reference data |
| Type 1 | Overwrite | Error corrections, no history needed |
| Type 2 | Add new row | Full history required |
| Type 3 | Add column | Limited history (current + previous) |
| Type 4 | Separate history table | History in dedicated mini-dimension |
Type 2 SCD Implementation
- Add columns: EffectiveDate, ExpirationDate, IsCurrent
- On change: Set existing row ExpirationDate and IsCurrent=False; Insert new row with new values
- Benefit: Complete history preserved; facts link to correct historical version
Visual: Type 2 SCD Example
| CustomerKey | CustomerID | Address | EffectiveDate | ExpirationDate | IsCurrent |
|---|---|---|---|---|---|
| 1001 | C123 | New York | 2020-01-01 | 2025-06-30 | False |
| 1002 | C123 | Los Angeles | 2025-07-01 | 9999-12-31 | True |
⚠️ Common Pitfall: Choosing Type 1 to "keep it simple" when history is required. Type 1 overwrites history permanently—if historical analysis is needed later, the data is lost.
Reflection Question: Your business needs to analyze customer behavior over time, including when customers changed regions. Which SCD type would you implement, and why?