Slowly Changing Dimension
Aussi : 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.