Slowly Changing Dimensions (SCDs)

THE MENTAL MODEL: A dimension in a data warehouse, like a list of customers or products, isn't always static. A customer moves, a product gets recategorized. A Slowly Changing Dimension (SCD) is a formal strategy to manage these infrequent but important updates so they don't corrupt your historical data. It's about deciding if you care about the "before" and "after" states of your data, contrasting with rapidly changing transactional data like order prices which are handled differently.

HOW IT WORKS: When an attribute in a dimension table changes, a decision must be made. For example, if a customer's address changes, do we update the existing record, losing the old address forever? Or do we create a new record to preserve the history? The SCD framework provides different approaches for these scenarios, balancing storage costs, query complexity, and the need for historical accuracy. The core mechanism is deciding whether to overwrite the data, add a new version, or track history in separate columns.

WHEN TO USE IT: Use SCDs for dimension attributes that are fundamental to analysis but can change over time. This includes things like customer details (name, address), product attributes (category, brand), or geographical data (store locations, sales territories). It's essential when you need to run reports that compare different time periods accurately and reflect the state of the world as it was at that time.

WHEN NOT TO USE IT: SCDs are not for rapidly changing data. Transactional facts like quantity sold, price, or specific timestamps belong in a fact table, not a dimension managed as an SCD. These are considered measures or rapidly changing attributes. Applying SCD techniques here would be overly complex and inefficient. They are also unnecessary for attributes that are truly static and will never change.

ONE CANONICAL EXAMPLE: A customer, Jane Doe, lives in New York and makes a purchase in 2022. Your sales reports for 2022 attribute her purchase to the New York region. In 2023, she moves to Los Angeles. Without an SCD strategy, if you simply update her record to "Los Angeles", it looks like her 2022 purchase also happened while she lived in LA. This corrupts your historical sales-by-region report. An SCD strategy would preserve the fact that the 2022 purchase is tied to the "New York" version of Jane Doe.