SCD Type 1 (Overwrite the Old Data)
- In this type, the old data is simply overwritten with new data, and historical changes are not tracked.
- Use Case: When historical accuracy is not important.
Example:
A customer table initially has:
| Customer_ID | Name | City |
|---|---|---|
| 101 | Alice | New York |
Now, if Alice moves to Los Angeles, the record is updated as:
| Customer_ID | Name | City |
|---|---|---|
| 101 | Alice | Los Angeles |
There is no history of Alice living in New York.
SCD Type 2 (Maintain History with New Rows)
- This method keeps historical data by adding a new row with a versioning mechanism (e.g., start and end dates, active flag).
- Use Case: When tracking historical changes is essential.
Example:
Initial data:
| Customer_ID | Name | City | Start_Date | End_Date | Active_Flag |
|---|---|---|---|---|---|
| 101 | Alice | New York | 2020-01-01 | NULL | Y |
Now, Alice moves to Los Angeles. Instead of updating the existing row, a new row is inserted, and the old row is marked as inactive:
| Customer_ID | Name | City | Start_Date | End_Date | Active_Flag |
|---|---|---|---|---|---|
| 101 | Alice | New York | 2020-01-01 | 2023-03-01 | N |
| 101 | Alice | Los Angeles | 2023-03-02 | NULL | Y |
This approach preserves history.
SCD Type 3 (Maintain History with a Separate Column)
- Instead of keeping full historical records, only a limited number of changes are stored in separate columns.
- Use Case: When only the previous value needs to be retained. Consider a Product dimension where the product price may change periodically. In an SCD3 scenario, the dimension record includes fields for both the current and previous prices.