Slowly Changing Dimension

Definition

A Slowly Changing Dimension (SCD) is a data warehousing technique for tracking and managing changes to dimension table attributes over time — such as a customer's address or a product's category — so historical analysis reflects the values that were true at the time of each recorded fact.

Overview

In dimensional data warehouse modeling, dimension tables hold descriptive attributes (customer name, region, product category) that fact tables reference to give context to measured events (sales, clicks, transactions). The challenge SCDs address is that these descriptive attributes are not static: a customer moves, a product gets reclassified, an employee changes departments. If a warehouse simply overwrites the old value with the new one, historical reports lose accuracy — sales made when a customer lived in one region would incorrectly appear attributed to their new region. Ralph Kimball's dimensional modeling methodology formalized several standard strategies for handling this, numbered as SCD 'types.' The most commonly used types are: Type 1, which simply overwrites the old attribute value with the new one, losing history but keeping the model simple, appropriate when historical accuracy for that attribute doesn't matter; Type 2, which preserves history by inserting a new row for each change, typically with effective-date/end-date columns (or a current-flag) so each historical fact can be joined to the dimension row that was valid at that time — this is the most widely used approach when historical accuracy matters; and Type 3, which adds a new column to track a limited history (e.g., 'previous region' alongside 'current region'), useful when only the immediately prior value needs to be retained. Less common hybrid types (4, 6, and others) combine these approaches, such as maintaining a separate history table (Type 4) or blending Type 1, 2, and 3 techniques within a single dimension (sometimes called Type 6, or 'hybrid'). Choosing an SCD strategy per attribute (not necessarily per whole dimension) is a core data-warehouse design decision, balancing storage cost, query complexity, and the business need for historical accuracy. Type 2 in particular is ubiquitous in enterprise data warehouses and is directly supported by many ETL/ELT tools and modern data-build frameworks like dbt, which include built-in 'snapshot' functionality specifically to implement Type 2 SCD tracking over time.

Key Concepts

• Manages how dimension table attribute changes are tracked over time
• Type 1: overwrite old value, no history preserved
• Type 2: insert new row per change with effective-date/current-flag columns, preserving full history
• Type 3: add a column to track a limited (usually one-step) prior value
• Hybrid types (4, 6) combine strategies for more nuanced history needs
• Strategy can be chosen per attribute, not just per whole dimension table
• Directly supported by ETL/ELT tools and frameworks like dbt (via 'snapshots')
• Essential for accurate point-in-time historical reporting in a data warehouse

Use Cases

Frequently Asked Questions

Related in Database