DATA ENGINEERING · QUESTION 92
What are slowly changing dimensions, and when do you use Type 1 or Type 2?
On this page
Slowly changing dimension strategies define how a warehouse handles changes to descriptive attributes. Type 1 overwrites the old value and keeps no history; Type 2 creates a new version with effective dates or a current flag, preserving historical context. Choose based on whether reports should reflect today’s value or the value that was true when an event occurred.
LET'S MAKE IT SIMPLE
First, the idea in plain English
A customer may move to a new region or a product may change category. If the warehouse simply overwrites the field, old reports may be restated using the new value. Sometimes that is desired; sometimes analysts need to know what was true at the time. Slowly changing dimensions make that choice explicit.
A USEFUL COMPARISON
Picture it this way
A contact list can replace an old address with the new one, or keep dated address records showing where someone lived during each period. The first is simple current-state storage; the second preserves history for questions about the past.
FOLLOW THE JOURNEY
Choose between overwrite and effective-dated history
Contrast Type 1 current-value replacement with Type 2 versioned dimension rows.
This simplified visual highlights the main relationships. In practice, the exact design depends on the requirements; Facts must map to the intended dimension version.
A Type 1 update overwrites an attribute in one row; a Type 2 update closes the old row and inserts a new effective-dated row.
How to read this visual
This simplified visual highlights the main relationships. In practice, the exact design depends on the requirements; Facts must map to the intended dimension version.
UNDER THE HOOD
How it works, step by step
Detect attribute change
Compare incoming dimension values with the current record.
Why it matters: Only tracked attributes need the chosen history behavior.
Apply Type 1 or Type 2
Overwrite the value or create a dated version.
Why it matters: The strategy defines how historical analyses interpret the change.
Join facts correctly
Use current keys or effective-time range joins as required.
Why it matters: Facts must map to the intended dimension version.
OUTSIDE THE TEXTBOOK
A practical example
If a customer changes region, a current operations dashboard may use Type 1 and show all orders under the latest region. A historical sales report may use Type 2 so orders remain attributed to the region recorded at purchase time. The reporting requirement determines the strategy.
GO A LITTLE DEEPER
Technical details that matter
- Type 2 commonly uses a surrogate key, effective start/end timestamps, and a current-row indicator.
- Late-arriving facts may need a dimension version resolved by event time.
- Some attributes use different policies; Type 0, Type 3, and hybrid patterns exist.
MAKE A GOOD CHOICE
When is this useful?
- Preserving historical customer, product, or organizational attributes.
- Maintaining current-state dimensions when old values are irrelevant.
- Supporting audit and point-in-time reporting.
Use event sourcing or an append-only change log when complete attribute history and replay are requirements beyond a warehouse dimension.
WATCH OUT FOR
Common misunderstandings
- Using Type 1 when historical reporting must remain unchanged.
- Creating overlapping effective-date ranges in Type 2.
- Joining facts to the current row when the historical version is required.
REAL-WORLD TRADE-OFFS
Limitations and failure cases
- Type 2 increases row counts and join complexity.
- Late updates can require correcting historical ranges.
- The model must communicate which attributes are overwritten versus tracked.
WHAT TO REMEMBER
Key takeaways
- 1Type 1 overwrites; Type 2 preserves dated versions.
- 2Choose based on historical reporting needs.
- 3Handle effective times and late-arriving facts deliberately.
PRACTICE THE FOLLOW-UP
What might an interviewer ask next?
What is a surrogate key used for in Type 2?
It identifies each historical version of a dimension entity so facts can reference the version valid at their event time.
Can one dimension use both types?
Yes. Different attributes can have different history requirements, but the policy should be explicit and tested.
SAY IT OUT LOUD
How to explain this in an interview
“Slowly changing dimensions define what happens when descriptive attributes change. Type 1 overwrites and represents current state; Type 2 adds an effective-dated version so history can be reconstructed. I choose based on reporting requirements, then ensure facts join to the correct version and date ranges do not overlap.”
Connect this question to the roadmap topic it supports.