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. The current row's ExpirationDate is a far-future sentinel such as 9999-12-31 (not NULL) so
BETWEEN EffectiveDate AND ExpirationDatelookups work; IsCurrent makes "current version only" a simple filter - On change: Set existing row ExpirationDate and IsCurrent=False; Insert new row with new values. Order: stage the source, detect changed rows, expire the old version, then insert the new one (typically one MERGE, or UPDATE + INSERT in a single transaction)
- Benefit: Complete history preserved; facts link to correct historical version
- Facts must store the surrogate key (CustomerKey), looked up for the version current on the transaction date. A fact that stores the natural key (CustomerID) matches every version of the customer: joins fan out, or resolve to today's row and show current attributes on old sales
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?
