Slowly Changing Dimensions (SCD) Management

Mastering Slowly Changing Dimensions (SCD) Management

In this lesson, you will learn how to preserve historical context in your data warehouse when the attributes of a dimension entity change over time.

The Problem: Overwriting History

In a simple dimension table, if a customer moves from New York to California, you might be tempted to simply UPDATE the city column. However, this destroys the historical truth: any historical sales records associated with that customer would now appear as if they occurred in California. To solve this, we use SCD management to track these state changes.

Type 2: The Gold Standard for History

The most common approach for tracking change is SCD Type 2. Instead of overwriting rows, you retire the old record and insert a new one. To distinguish between them, we add effective dates and a "current" flag.

Consider this table structure:

CREATE TABLE dim_customer (
    customer_sk SERIAL PRIMARY KEY, -- Surrogate Key
    customer_id INT,                -- Natural Business Key
    city VARCHAR(50),
    start_date DATE,
    end_date DATE,
    is_current BOOLEAN
);

When a customer moves, you perform two operations:

1. Expire the old record: Update the end_date to today and set is_current to FALSE.
2. Insert the new record: Insert the new city, set start_date to today, end_date to NULL (or a future date), and is_current to TRUE.

Why This Matters for Aggregation

Because you now have multiple rows for the same customer_id (each with a unique customer_sk), your fact tables must link to the customer_sk that was active at the time of the transaction. This allows your analytical queries to perform accurate, point-in-time aggregations. By joining the fact table to the dimension table using both the customer_id and the transaction date (where transaction_date is between start_date and end_date), you ensure that sales are attributed to the correct geographical location for that specific moment in time.

Key Takeaway: Slowly Changing Dimensions maintain historical accuracy by versioning records with time ranges, allowing your reports to reflect the state of the business exactly as it existed when each event occurred.