ADE Slowly Changing Dimensions
Business facts change over time. A customer moves to a new city, an employee joins a new team, and a product moves to a new category. A data warehouse must decide what to do with the old value. Slowly Changing Dimensions, shortened to SCD, are the standard recipes for that decision.
Facts and Dimensions in Plain Words
A fact table records events, such as sales or trips. A dimension table describes the who, what, and where of those events, such as customers, products, and stores. Dimensions change slowly compared with facts, and the word "slowly" in SCD points to that gentle pace.
Think of a phone contact book. A friend moves to a new address. You can overwrite the old address, keep both addresses with dates, or add a spare field for the previous address. Each choice maps to one SCD type.
Scenario: The Customer Who Moved
BrightMart, a fictional grocery chain, stores customer Meena in Pune. In March, Meena moves to Goa. Last year she bought groceries worth 40,000 while living in Pune. Management asks for sales by city. The answer depends on the SCD type.
| customer_id | name | city |
|---|---|---|
| C100 | Meena | Pune |
Type 1: Overwrite
Type 1 replaces the old value with the new one. No history survives.
| customer_id | name | city |
|---|---|---|
| C100 | Meena | Goa |
Last year's purchases now appear under Goa. Type 1 suits corrections, such as a fixed spelling mistake, where history carries no value.
Type 2: Add a New Row
Type 2 keeps every version of the record. Each version gets a start date, an end date, and a flag that marks the current row.
| surrogate_key | customer_id | name | city | start_date | end_date | is_current |
|---|---|---|---|---|---|---|
| 1 | C100 | Meena | Pune | 2023-01-01 | 2026-03-14 | N |
| 2 | C100 | Meena | Goa | 2026-03-15 | 9999-12-31 | Y |
Last year's sales stay linked to Pune, and new sales link to Goa. A surrogate key, a simple number created by the warehouse, separates the two versions of the same customer. Type 2 is the most popular choice for analytics.
Type 3: Add a Column
Type 3 stores the current and the previous value side by side.
| customer_id | name | current_city | previous_city |
|---|---|---|---|
| C100 | Meena | Goa | Pune |
This type keeps only one step of history. Teams use it when a single before-and-after comparison is enough.
Choosing a Type
| Type | History Kept | Storage Cost | Best For |
|---|---|---|---|
| Type 1 | None | Lowest | Corrections and unimportant attributes |
| Type 2 | Full | Higher | Reports that must reflect the past accurately |
| Type 3 | One previous value | Low | Simple before-and-after comparisons |
Building Type 2 in Azure
The flow below runs inside Azure Data Factory, Databricks, or Synapse. Each run compares incoming records with the current rows in the dimension.
SOURCE CUSTOMER FEED
|
v
Compare with current dimension rows (match on customer_id)
|
+----+----------------------+
| |
No change Change found
(skip) |
1. Close old row: set end_date, is_current = N
2. Insert new row: start_date = today, is_current = Y
A hash of the tracked columns speeds up the comparison. Compute one hash from city, phone, and email on both sides. A different hash signals a change without checking each column by hand.
Delta Lake Merge Pattern
On Databricks, a MERGE statement on a Delta table performs the close-and-insert work. The statement updates matching current rows and inserts the new versions in a controlled step. Many teams stage the changed rows first and run the merge once per load.
Common Pitfalls
- Tracking too many columns creates an explosion of tiny row versions.
- Missing end dates leave two rows marked as current for one customer.
- Facts that join on the business key instead of the surrogate key lose history.
- Late-arriving changes need a rule that places them in the correct date range.
Key Points
- SCD defines how a warehouse treats changing descriptive data.
- Type 1 overwrites, Type 2 adds rows, and Type 3 adds a column.
- Type 2 with start date, end date, and current flag preserves accurate history.
- Surrogate keys and hash comparisons keep Type 2 loads clean and fast.