Slowly Changing Dimension
Also: SCD, Slowly Changing Dimensions, Dimension history tracking
A Slowly Changing Dimension (SCD) is a data warehousing technique for managing dimension attributes that change over time, preserving history or overwriting values based on chosen rules.
What It Is
A Slowly Changing Dimension (SCD) is a method used in data warehousing to handle changes to descriptive attributes in dimension tables over time. Dimensions hold the context for facts (numeric measurements): customers, products, employees, stores, and similar entities. Because these attributes (a customer's address, a product's category, an employee's department) change infrequently and unpredictably, the warehouse needs an agreed strategy for how to record those changes.
Why it matters
Without a clear SCD strategy, historical reporting becomes inaccurate. If a customer moves from one region to another and you simply overwrite the old region, all past sales appear as if they always belonged to the new region. SCD techniques let teams decide whether they care about current state only, full history, or a blend. This directly affects compliance, trend analysis, attribution, and audit reliability.
Common Types
- Type 0: Attributes never change (for example, a birth date). Updates are ignored.
- Type 1: Overwrite the old value. No history is kept. Simple, but past context is lost.
- Type 2: Add a new row for each change, with effective dates and a current flag. Full history is preserved. This is the most common approach for meaningful historical analysis.
- Type 3: Keep limited history in extra columns (for example, current_region and previous_region). Useful when only one prior value matters.
- Type 4 and 6: Hybrid approaches using history tables or combining Types 1, 2, and 3.
How it is used in practice
ETL or ELT pipelines compare incoming source records to existing dimension rows. When a tracked attribute differs, the pipeline applies the configured SCD logic. Modern transformation tools and frameworks often automate Type 2 handling using surrogate keys, hash comparisons, and validity timestamps.
Concrete Example
A customer named Maria lives in North region. Sales are recorded against her. She moves to South region.
- With Type 1, the warehouse overwrites North with South. Old sales now look like South sales.
- With Type 2, a new row is created. Sales before the move stay linked to North, sales after the move link to South. Reports correctly reflect where she lived at the time of each purchase.
Choosing the right type is a business decision, not just a technical one.
Frequently asked questions
What is a Slowly Changing Dimension in data warehousing?
A Slowly Changing Dimension (SCD) is the agreed method for recording changes to descriptive attributes in dimension tables: a customer's address, a product's category, an employee's department. Dimensions provide the context around numeric facts, and those attributes change infrequently and unpredictably. The SCD type you pick decides whether the warehouse keeps the old value, replaces it, or stores both.
Why does the choice of SCD type affect reporting accuracy?
Because overwriting an attribute rewrites the past. If a customer moves from the North region to the South and you simply replace the value, every historical sale suddenly appears as a South sale. That distorts trend analysis, attribution and audit trails, which is why the decision has compliance implications and not only technical ones.
What is the difference between SCD Type 1 and Type 2?
Type 1 overwrites the old value and keeps no history: simple to run, but past context disappears. Type 2 inserts a new row for each change with effective dates and a current flag, so facts stay linked to the attribute value that applied when they occurred. Type 2 is the usual choice when historical analysis matters.
When is Type 0 or Type 3 enough instead of full Type 2 history?
Type 0 fits attributes that should never change, such as a birth date: incoming updates are simply ignored. Type 3 fits cases where only one prior value matters, stored in extra columns like current_region and previous_region. Types 4 and 6 exist as hybrids, using separate history tables or mixing Types 1, 2 and 3 in the same dimension.
How do ETL or ELT pipelines actually apply SCD logic?
The pipeline compares each incoming source record to the existing dimension row and detects whether a tracked attribute differs, then applies the configured SCD rule. Modern transformation tools automate Type 2 handling with surrogate keys, hash comparisons to spot changes, and validity timestamps to close the old row and open the new one.