Dimensional modeling patterns resolve evolving attributes, balancing straightforward query access against the need for rigorous historical audit trails.
When designing a data warehouse, one of the most critical questions a data architect faces is:
How do we handle data that changes over time?
Some data attributes, like date of birth or Social Security number, remain constant. Others, like email address or employer name, evolve. Managing these evolving attributes while maintaining an accurate history is what makes slowly changing dimensions essential to robust data warehouse design.
This article explains all six SCD types — their logic, use cases, and trade-offs — using clear examples and practical insights for modern orchestration tools like dbt, Mage, Prefect, or Dagster.
What Is a Slowly Changing Dimension?
A slowly changing dimension (SCD) refers to a dimension table in a data warehouse that stores and tracks changes to attribute values over time. As a core component of data warehousing, SCDs allow businesses to capture evolution. Unlike static dimensions, which never change, SCDs evolve — requiring specific dimensional modeling strategies to decide whether to overwrite, append, or version historical data.
SCD Type 0: Keep Original Values
Type 0 dimensions never change once created.
Attributes such as Full Name, Date of Birth, or SSN retain their original values permanently.
Example: Customer’s Date of Birth or Country of Birth
Use Type 0 when historical accuracy depends on preserving the original data without alteration.
SCD Type 1: Overwrite Existing Data
Type 1 replaces old values with new ones.
This method is simple, storage-efficient, and ideal for corrections or attributes that don’t require historical tracking.
Example:
A customer updates their email address after changing jobs.
Customer ID | Customer Name | Customer Email |
|---|---|---|
1 | Peter |
Type 1 works best in data warehouse design when you only need the most recent value and don’t care about history — for instance, correcting a typo or updating contact information.
SCD Type 2: Add a New Row
Type 2 is the most widely used SCD technique in data warehousing.
Add a new record each time an attribute changes, preserving all historical versions through comprehensive historical data tracking. You can identify the current record using columns such as:
Is_ActiveVersionStart_Date/End_Date
Example (Is Active Flag):
Row ID | Customer ID | Name | Is Active | |
|---|---|---|---|---|
1 | 1 | Peter | No | |
2 | 1 | Peter | Yes |
Example (Date Range):
Surrogate ID | Customer ID | Start Date | End Date | |
|---|---|---|---|---|
1 | 1 | 01-Feb-2023 | 01-Jan-2024 | |
2 | 1 | 01-Jan-2024 | NULL |
The current record’s End_Date is always NULL.
Use Case:
Tracking employment history, address changes, or customer status transitions within your dimensional modeling strategies.
Implementation Tip:
Tools like dbt or Azure Synapse Pipelines can automate Type 2 logic using MERGE or SCD components.
SCD Type 3: Add a New Attribute Column
Type 3 stores limited historical data by adding a new column for the previous value.
Example:
Customer ID | Name | Current Email | Previous Email |
|---|---|---|---|
1 | Peter |
Type 3 is ideal when you only need to compare current vs. last value — such as tracking a customer’s prior region or last manager.
SCD Type 4: Add a History Table
Type 4 separates active and historical data into two tables to streamline data warehouse design:
Active Table
Row ID | Customer ID | Name | Start Date | End Date | |
|---|---|---|---|---|---|
3 | 1 | Peter | 01-Jun-2023 | NULL |
History Table
Row ID | Customer ID | Start Date | End Date | |
|---|---|---|---|---|
1 | 1 | 01-Feb-2022 | 01-Jan-2023 | |
2 | 1 | 01-Jan-2023 | 01-Jun-2023 |
This approach works well when you frequently query only current records but still need a complete historical audit trail.
SCD Type 5: Combine Type 4 and Type 1
Type 5 combines the strengths of Type 4 (history table) and Type 1 (overwrite in base table).
You maintain:
A base dimension with current values.
A history table with all changes.
A foreign key link between the two.
Example:
Customer Table
Customer ID | Name | Employer ID | |
|---|---|---|---|
1 | Peter | 3 |
Company Table
Company ID | Company Name |
|---|---|
1 | Technology Hub |
2 | StructuredData |
3 | Oracle |
Employment History Table
Customer ID | Company ID | Start Date | End Date |
|---|---|---|---|
1 | 1 | 01-Feb-2022 | 01-Jan-2023 |
1 | 2 | 01-Jan-2023 | 01-Jun-2023 |
1 | 3 | 01-Jun-2023 | NULL |
This design is a sophisticated choice among dimensional modeling strategies, allowing current mini-dimension attributes to be accessed with the base dimension without linking through a fact table.
SCD Type 6: Combine Types 1, 2, and 3
Type 6 integrates overwriting (Type 1), row versioning (Type 2), and column tracking (Type 3).
It provides a unified view of current, previous, and historical data—all in one table —maximizing the effectiveness of SCD in data warehousing.
Row ID | Customer ID | Name | Current Email | Previous Email | Start Date | End Date | Is Active |
|---|---|---|---|---|---|---|---|
1 | 1 | Peter | NULL | 01-Feb-2022 | 01-Jan-2023 | No | |
2 | 1 | Peter | 01-Jan-2023 | 01-Jun-2023 | No | ||
3 | 1 | Peter | 01-Jun-2023 | NULL | Yes |
Benefits of Type 6:
Enables historical and current reporting from one table.
Avoids Type 1’s lack of history and Type 3’s limited scope.
Adds flexibility for business audits and machine learning models needing time-based features and historical data tracking.
When to Use Each SCD Type
Type | Method | Best For | Pros | Cons |
|---|---|---|---|---|
0 | Keep original | Immutable data | Simple, consistent | No change tracking |
1 | Overwrite | Corrections | Space efficient | No history |
2 | New row | Historical reporting | Complete audit trail | More storage |
3 | New column | Compare current vs. previous | Easy access | Limited history |
4 | History table | Large systems with frequent queries | Clean separation | Complex ETL |
5 | Type 4 + 1 | Hybrid systems | Efficient lookups | ETL overhead |
6 | Type 1 + 2 + 3 | Full hybrid | All-in-one view | Most complex to maintain |
Related reading: Type 2 SCDs are one way to preserve history, but they are not the only option. For a methodology built around fully auditable, historized data, see our guide to Data Vault 2.0 modeling.
Leadership Insight: Why SCDs Still Matter
In the age of cloud warehouses and real-time analytics, some teams underestimate the need for slowly changing dimensions — assuming everything can be recalculated.
Yet, regulatory reporting, financial reconciliations, and AI models all require robust accuracy in historical data tracking.
Modern tools like dbt snapshots, Databricks Delta, and Snowflake’s Streams & Tasks make SCD management easier and more automated than ever. In Snowflake, Dynamic Tables can be used for declarative SCD logic, often accelerated by visual interfaces like Coalesce. For Databricks environments, Delta Lake handles SCD logic through MERGE statements, which efficiently manage upserts for Type 1 and Type 2 records. Additionally, tools like Weld now offer automated history tables for specific connectors like Shopify to simplify tracking.
The key is aligning data design with business intent—deciding which dimensional modeling strategies best fit your attributes for long-term preservation.
Summary
Slowly changing dimensions form the backbone of historical accuracy in data warehouse design.
From immutable Type 0 attributes to the hybrid flexibility of Type 6, each approach balances simplicity, storage, and traceability.
When implemented thoughtfully, using the right SCD techniques transforms your warehouse from a static repository into a living system that evolves alongside your business.