In this lesson, you will learn how to preserve historical accuracy in your data warehouse by applying Type 1, 2, and 3 strategies to track attribute changes in your dimensions.
When an attribute (like a customer’s home address or an employee’s job title) changes, you must decide how to handle the "old" data. A Slowly Changing Dimension (SCD) strategy defines how your database reacts to these updates.
Type 1: Overwrite
With Type 1, you simply overwrite the old value with the new one. This is the simplest method, but it destroys history. If you use Type 1 for a customer’s city, you will only ever know where they live now, losing track of where they lived previously.
Type 2: Add a New Row (Historical Tracking)
Type 2 is the gold standard for historical accuracy. Instead of overwriting, you expire the old row (by setting an end_date) and insert a brand-new row for the change. You often use a start_date, end_date, and a current_flag to manage this.
-- Example of Type 2 structure
-- Row 1: Employee ID 101, Role: Junior, Start: 2022-01-01, End: 2023-01-01, Active: N
-- Row 2: Employee ID 101, Role: Senior, Start: 2023-01-01, End: NULL, Active: Y
Because you have two rows, your fact tables can join to the specific version of the dimension that was active at the time of the transaction. This preserves the "truth" of the past.
Type 3: Add a New Column (Current and Previous)
Type 3 tracks only a limited amount of history by adding a new column to the table, such as previous_role and current_role. This allows you to compare the current value to the one that immediately preceded it, but it cannot track long-term history if a user changes their attribute multiple times.
Choosing between these depends on whether your stakeholders need to see a "point-in-time" snapshot or just the latest information. Use Type 1 for data that doesn't need history, Type 2 for comprehensive reporting, and Type 3 when you only need to compare "now" versus "just before now."
Key Takeaway: Use SCD Type 1 to overwrite, Type 2 to create new rows for full history, and Type 3 to keep limited previous-value columns.