Understanding SCD Type 1 and Type 2 in Data Warehousing
Understanding SCD Type 1 and Type 2 in Data Warehousing
When working with dimensional data models, particularly in data warehouses or BI systems, handling changes to dimension data over time is a common and critical challenge. This is where Slowly Changing Dimensions (SCD) come in.
What are Slowly Changing Dimensions (SCD)?
In a star schema or snowflake schema, dimension tables store descriptive attributes (like customer name, address, or product category) that can change over time. But how do we manage these changes while preserving the historical context of facts?
SCD is a set of techniques used to handle these changes. Today, weβll focus on the two most widely used types: SCD Type 1 and SCD Type 2.
π SCD Type 1: Overwrite the Past
Definition:
SCD Type 1 simply updates the existing dimension record with new data. Thereβs no history preserved.
When to use:
* When maintaining historical data isnβt important.
* For correcting errors (e.g., a typo in a customer's name).
* When the change should apply retroactively in all reports.
Example:
Initial State:
| CustomerID | Name | City |
|---|---|---|
| 101 | John Doe | New York |
If John moves to Chicago:
| CustomerID | Name | City |
|---|---|---|
| 101 | John Doe | Chicago |
Pros:
* Simple to implement.
* No data bloat.
Cons:
* Loss of historical information.
π SCD Type 2: Preserve History
Definition:
SCD Type 2 adds a new row for every change in the dimension attribute, thus preserving the full history.
When to use:
* When historical reporting is essential.
* To track how dimension values changed over time.
Implementation typically includes:
* Surrogate key (e.g., CustomerSK)
* Start and end dates
* Current flag (IsCurrent)
Example:
Initial State:
| CustomerSK | CustomerID | Name | City | StartDate | EndDate | IsCurrent |
|---|---|---|---|---|---|---|
| 1 | 101 | John Doe | New York | 2022-01-01 | 2023-06-15 | N |
If John moves to Chicago:
| CustomerSK | CustomerID | Name | City | StartDate | EndDate | IsCurrent |
|---|---|---|---|---|---|---|
| 1 | 101 | John Doe | New York | 2022-01-01 | 2023-06-15 | N |
| 2 | 101 | John Doe | Chicago | 2023-06-16 | NULL | Y |
Pros:
* Preserves full history.
* Enables point-in-time analysis.
Cons:
* More complex queries.
* Larger data size.
π Choosing Between Type 1 and Type 2
| Criteria | Type 1 | Type 2 |
|---|---|---|
| Historical tracking | β No | β Yes |
| Simplicity | β Simple | β Complex |
| Space requirement | β Low | β Higher |
| Use case | Corrections only | Historical analysis |
Posted on June 21, 2025