Slowly Changing Dimensions (SCD) are a set of dimensional modeling patterns for handling changes to dimension attributes over time in a data warehouse or data lakehouse.
In operational databases, if a customer changes their last name, or an employee is promoted from “Analyst” to “Manager”, the application issues an UPDATE and overwrites the old value. The operational database cares about the current state.
An analytical system often needs history. If you overwrite the employee’s title with “Manager”, a report on last year’s sales by job title will attribute last year’s sales to a Manager, which was not true at the time. Slowly Changing Dimensions define how to track these changes so historical reports stay accurate.
The Primary SCD Types
Data architects choose a pattern per attribute, based on what the business needs to report.
SCD Type 1: Overwrite
Type 1 ignores history. If a customer corrects a typo in their shipping address, the pipeline issues an UPDATE and replaces the old value with the new one.
Use Case: Type 1 fits attributes where the old value has no analytical meaning, such as a corrected misspelling. See Type 1 SCD.
SCD Type 2: Add New Row (Historical Preservation)
Type 2 is the most common pattern when full history is needed. When a dimension attribute changes, the old row is not overwritten. The pipeline closes out the old row and inserts a new row for the updated state.
To do this, the table needs extra columns:
- Surrogate Key: A unique key (e.g.,
1045) that identifies each version of the record, separate from the operational ID. See surrogate key. - Effective Start Date: When this version became active.
- Effective End Date: When this version stopped being active (often
9999-12-31for the current row). - Is_Current Flag: A boolean to filter quickly for the current version.
If employee John Doe (operational ID E-100) is promoted to Manager on May 1, 2026, the pipeline updates his existing row (setting the end date to May 1 and is_current to FALSE) and inserts a new row for E-100 with the title “Manager”, a start date of May 1, and is_current set to TRUE.
Use Case: An analyst can attribute the sales John made in April to an “Analyst” and the sales he made in June to a “Manager”. See Type 2 SCD.
SCD Type 3: Add New Column (Partial History)
Type 3 keeps only the most recent prior value. Instead of adding rows, the table has a column for the current value and a column for the previous value.
If a customer moves from “New York” to “California”, the pipeline moves “New York” into previous_state and writes “California” into current_state.
Use Case: Type 3 fits when the business only compares the current value with the one immediately before it. It keeps the table small but cannot answer questions about older changes. See Type 3 SCD.
SCD Implementation in the Lakehouse
On older Hadoop data lakes built from plain Hive tables and files, closing out a Type 2 row meant rewriting whole partitions, because the files did not support row-level UPDATE (Hive ACID tables helped later, mostly within Hive).
Open table formats changed this. Apache Iceberg, Delta Lake, and Apache Hudi provide ACID transactions and MERGE INTO on object storage. A Type 2 load on Iceberg is typically one MERGE that expires changed rows plus an insert of their new versions, run by Spark, Dremio, or Trino, or generated by dbt snapshots. Frequent merges on large dimensions benefit from merge-on-read and periodic compaction.
Is Iceberg Time Travel a Substitute for SCD Type 2?
It is tempting to skip Type 2 because Iceberg already keeps old snapshots. It is usually the wrong call:
| SCD Type 2 | Iceberg time travel | |
|---|---|---|
| Time axis | Business effective time (when the change was true) | System time (when the commit happened) |
| Retention | Permanent, part of the data | Until snapshots are expired |
| Query pattern | Join facts to the version valid at the fact’s date | Read the whole table as of one point in time |
| Late or corrected changes | Effective dates can be backdated | Commit time cannot be changed |
| Cost | Extra rows in the dimension | Old data files kept in storage |
Time travel is excellent for audits, debugging, and reproducing a report. Type 2 answers the business question “what was true when this sale happened”. Most lakehouses use both. For more examples, read Slowly Changing Dimensions: Types 1-3 with Examples.
Summary of Technical Value
Slowly Changing Dimensions keep historical reports accurate as dimension attributes change. Type 1 discards old values, Type 2 keeps a full timeline, and Type 3 keeps the immediately prior value. On a lakehouse, MERGE INTO on Iceberg tables makes all three practical on object storage, and snapshot time travel complements Type 2 rather than replacing it.
Learn More
To learn more about the Data Lakehouse, read The Lakehouse Built for Everyone by Alex Merced. You can find this and other books by Alex Merced at books.alexmerced.com.