Slowly Changing Dimensions (SCD): What They Are and How to Actually Choose the Right One
When you're working with data, one thing is guaranteed: data changes.
Customers move, prices get updated, employees switch roles. The real question isn’t if data changes, it’s how you handle those changes without breaking your analytics later.
That’s where Slowly Changing Dimensions (SCD) come in.
First, What Are SCDs?
SCDs are just different ways of handling updates to dimension data.
Whenever a value changes, you decide:
- Do I overwrite it?
- Do I keep the old value?
- Do I store both?
That decision is what defines the SCD type.
SCD Type 0 -> Leave It Alone
What happens: Once data is written, it never changes.
Use it when:
- The value should never change (like date of birth, original signup date)
Where people mess up: Sometimes data looks permanent but isn’t. If there’s even a chance it might need correction, this can cause issues later.
SCD Type 1 -> Just Update It
What happens: You overwrite the old value with the new one. No history.
Example:
- Fixing a spelling mistake in a name
- Updating an email address
Use it when:
- You only care about the latest value
- History doesn’t matter at all
Trade-off: You lose history completely. There’s no going back.
Simple way to think about it:
“I only care about what’s true right now.”
SCD Type 2 -> Keep Full History
What happens: Every change creates a new row. You track when each version was valid.
Typically you’ll have:
- start_date
- end_date
- is_current flag
Example: A customer changes city:
- Row 1 → Mumbai (till 2023)
- Row 2 → Bangalore (current)
Use it when:
- You need to track changes over time
- Reports depend on historical accuracy
Why this matters: Let’s say you’re analyzing sales by city & you need to know which city the customer belonged to at that time, not their current one.
Downside:
- More storage
- Slightly more complex queries
Mental model:
“I want to know what things looked like at any point in time.”
SCD Type 3 -> Just Keep the Previous Value
What happens: You store the current value and one previous value in extra columns.
Example:
| current_city | previous_city |
|---|---|
| Bangalore | Mumbai |
Use it when:
- You only care about the most recent change
- You don’t need full history
Limitation: If the value changes multiple times, you lose older history.
Mental model:
“I just want to compare current vs last state.”
SCD Type 4 -> Split Current and History
What happens:
- Main table → only current data
- Separate table → full history
Use it when:
- You want fast queries on current data
- But still need historical tracking somewhere
Why teams use this: It keeps your main table clean and efficient, especially at scale.
SCD Type 6 -> Hybrid Approach
What happens: It mixes Type 1, Type 2, and Type 3.
So you get:
- Full history (like Type 2)
- Current overwrite behavior (Type 1)
- Previous value columns (Type 3)
Use it when:
- You have complex reporting needs
- Different teams need different views of the same data
Powerful, but easy to overcomplicate. Don’t use this unless you actually need it. (no, using this does not make you cooler)
How to Choose (This Is What Actually Matters)
Instead of memorizing types, just ask:
1. Do I need history?
- No → Type 1
- Yes → go next
2. How much history?
- Full history → Type 2
- Only last change → Type 3
3. Will performance or scale be a problem?
- Yes → consider Type 4
- No → Type 2 is usually enough
Real Examples
- User profile info (name, email): Type 1
- Customer address over time: Type 2
- Tracking last status change: Type 3
- Large production systems: Type 4 or Type 6
Common Mistakes
1. Using Type 1 everywhere Feels simple at first, but the moment someone asks for historical data, you're stuck.
2. Using Type 2 for everything You end up with unnecessary complexity and bloated tables.
3. Not thinking about queries Your model should match how the data will be used, not just how it’s stored.
Final Thought
There’s no “best” SCD type.
It depends on what questions your data needs to answer.
If you’re unsure, default to this:
- Start simple (Type 1)
- Move to Type 2 only where history actually matters
That alone will keep your design clean and your future self grateful.