Managing Slowly Changing Dimensions

Select appropriate strategies for tracking historical changes in dimension attributes to maintain accurate reporting over time.

In this lesson, you will learn how to choose the right strategy for tracking attribute changes in your dimension tables so that your historical reports remain accurate.

When a customer moves to a new city or a product changes its category, simply overwriting the old value in your dimension table would destroy the history. If you updated a customer’s location, any past sales linked to that customer would suddenly appear as if they happened in the new city. To prevent this, we use Slowly Changing Dimension (SCD) strategies.

Three Common SCD Strategies

SCD Type 0 (Fixed): You ignore changes. The attribute never changes (like an original birth_date).
SCD Type 1 (Overwrite): You simply update the existing record. Use this when history doesn't matter (e.g., correcting a typo in a name). You lose all previous versions.
SCD Type 2 (Add New Row): This is the gold standard for historical accuracy. When an attribute changes, you expire the old record and insert a new one.

Implementing SCD Type 2

To use Type 2, you add columns to track the lifecycle of each record, typically start_date, end_date, and a current_flag.

-- Example of an SCD Type 2 row
-- Customer 101 moved from NY to CA on 2023-01-01
SELECT customer_id, city, start_date, end_date, is_current
FROM dim_customer
WHERE customer_id = 101;

-- Results:
-- 101 | NY | 2022-01-01 | 2022-12-31 | FALSE
-- 101 | CA | 2023-01-01 | 9999-12-31 | TRUE

When a fact table record (like a sale) is created, you link it to the specific surrogate_key that was active at that moment in time. This ensures that a sale made in 2022 correctly attributes the customer to "NY," while a 2023 sale attributes them to "CA," even though the customer is the same person.

Choosing between these depends on your business requirements: if you need to report on "what was true at the time of the sale," always default to Type 2. If you only care about the current status, Type 1 is simpler and more performant.

Key Takeaway: Use SCD Type 1 when historical accuracy is irrelevant, and SCD Type 2 when you must preserve a perfect audit trail of how dimension attributes changed over time.