In this lesson, you will learn how to choose the right strategy for tracking historical changes in your dimension tables so your reports stay accurate over time.
When you manage a dimension table, such as Dim_Customer, you face a common problem: what happens when a customer moves to a new city? If you simply overwrite the old city with the new one, you lose the historical record of where they lived previously. This is the challenge of Slowly Changing Dimensions (SCDs).
To solve this, we use "Type" techniques to decide how to handle attribute changes:
SCD Type 1: Overwriting
Use this when you don't care about history. You simply replace the old value with the new one. This is easy to maintain but erases the past.
- Best for: Correcting data errors (e.g., fixing a misspelled name).
SCD Type 2: Adding New Rows
This is the most common technique for analytical accuracy. When an attribute changes, you "expire" the current record by adding an end_date and insert a brand-new row for the customer with the new information and a new surrogate_key.
Imagine a customer, Alice, moves:
| Surrogate_Key | Name | City | Start_Date | End_Date | Current_Flag |
|---|---|---|---|---|---|
| 101 | Alice | New York | 2022-01-01 | 2023-05-01 | N |
| 102 | Alice | London | 2023-05-01 | NULL | Y |
By keeping both rows, your fact tables linked to Surrogate_Key 101 will always correctly attribute Alice’s sales to her time in New York, while new sales link to 102 for London.
SCD Type 3: Adding Columns
You add a "previous" column (e.g., previous_city) to the existing row. This keeps only one version of history. It is rarely used because it becomes messy if the data changes a third or fourth time.
Choosing between these depends on your reporting needs. Do you need to know what was true at the time of the transaction? If yes, Type 2 is your gold standard for maintaining a reliable audit trail.
Key Takeaway: Use SCD Type 2 by adding new rows with effective dates to preserve historical context, ensuring your analytical reports reflect the reality of the business at any point in time.