3.1.3. Slowly Changing Dimensions (SCDs)
💡 First Principle: Dimensions change over time, and how you handle those changes determines whether your historical analysis tells the truth. If a customer moves from New York to Los Angeles, should last year's sales show New York (where they were at purchase time) or Los Angeles (current address)? There's no single right answer—it depends on your business question.
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 (birth date) |
| 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 |
⚠️ Exam Trap: Choosing Type 1 to "keep it simple" when history is required permanently destroys historical context. If stakeholders ever ask "what were our sales by customer region over time?" and you used Type 1, that data is gone. Clarify history requirements before choosing.
Reflection Question: Your business needs to analyze customer behavior over time, including when customers changed regions. Which SCD type would you implement, and why?
