Slowly Changing Dimensions

In the world of analytics and data warehousing, one of the trickiest challenges is keeping track of how things change over time.

• A store might move cities.
• A product might be rebranded.
• A customer might change their address.

In operational systems, these changes often overwrite the old value without a second thought. But in analytical systems, overwriting the past can break your historical reporting.

This is where Slowly Changing Dimensions (SCDs) come in. These are a set of modelling techniques designed to store and manage dimension data that changes infrequently while retaining historical accuracy.

What Are Dimensions and Why Do They Change Slowly?

In dimensional modelling (pioneered by Ralph Kimball), facts are the measurable events, like sales transactions, while dimensions are the descriptive attributes that give facts meaning.

Examples of dimensions:

• Customer (name, address, date of birth)
• Product (name, category, size)
• Location (city, country)
• Time (day, week, month, year)

Most of these do not change frequently. This makes them slow-moving data — but when they do change, the impact on reporting can be significant.

The Problem Without SCDs

Let’s say Alice Wong was a customer living in London from 2019 to 2024, and then moved to Manchester.

If your customer dimension only stores one row per customer, and you simply update her city from London to Manchester, then:

• Sales from 2019–2024 will incorrectly show her as being in Manchester.
• Historical reports will be misleading, breaking trust in your analytics.

SCDs solve this by allowing you to record both past and current attribute values.

SCD Types — The Complete List

The Kimball methodology and industry practice define several SCD strategies. Each type offers different trade-offs between simplicity, storage cost, and historical accuracy.

Type 0 – Fixed Attribute

• Keep the original value forever.
• Changes are ignored.
• Example: A product launch date that never changes.
• When to use: For attributes that are truly immutable.

If there is a request to alter the launch date of the iPhone from the 29th June 2007 it would be rejected.

Type 1 – Overwrite

• Overwrite old data with new data.
• No historical tracking.
• Example: Correcting a misspelling in a product name.
  • Pros: Simple, minimal storage.
  • Cons: Loses all history.

SQL Example:

In this example we have noticed typos in the product names:

Therefore we make the corrections, but we lose the original values and do no preserve any history.

UPDATE PRODUCT 
SET PRODUCT_NAME = 'iPhone'
WHERE PRODUCT_NAME = 'iZhone';

UPDATE PRODUCT 
SET PRODUCT_NAME = 'ChatGPT'
WHERE PRODUCT_NAME = 'ChatQPT';

UPDATE PRODUCT 
SET PRODUCT_NAME = 'iPhone'
WHERE PRODUCT_NAME = 'iZhone';

UPDATE PRODUCT 
SET PRODUCT_NAME = 'ChatGPT'
WHERE PRODUCT_NAME = 'ChatQPT';

Which leaves us with:

Type 2 – Add New Row (Most Common)

• Add a new record when an attribute changes.
• Track validity with start_date, end_date, and current_flag.
• Preserves complete history.

Pros: Full historical accuracy.
Cons: More storage, slightly more complex queries.

Again, in this example we correct the typos in the product names, but this time preserving history. We hame this change on 06-06-2025.

Type 3 – Add New Column for Previous Value

• Store limited history in extra columns.
• Typically keeps “current” and “previous” values only.
• Example: Current and prior region for a store.

Pros: Quick to query for comparisons.
Cons: Can only store a small number of changes.

Type 4 – Separate History Table

• Keep current dimension in one table.
• Store historical changes in a separate history table.
• Example: dim_customer and dim_customer_history.

Pros: Easy to query current state.
Cons: Requires joining two tables for full history.

Type 5 – Hybrid (4+1)

Type 5 builds on Type 4 by adding a “current profile” mini-dimension key to the base dimension and overwriting it as a Type 1 attribute. This lets users see current mini-dimension values alongside base attributes without joining through the fact table. In the presentation layer, the base and outrigger are shown as one table, with clearly named columns like “Current Income Level.” ETL overwrites the Type 1 reference whenever the profile changes, and if performance lags, the mini-dimension attributes can be embedded directly into the base dimension.

Type 6 – Hybrid (1+2+3)

• Combination of Type 1, Type 2, and Type 3.
• Current row updated for quick access.
• Historical rows preserved with Type 2 structure.
• Previous value column stored for fast comparisons.

SCDs in the Real World

Retail Example

• A product changes category from “Electronics” to “Smart Home.”
• Type 2 ensures historical sales remain tied to “Electronics” for the period before reclassification.

Airport Cargo Example

• Heathrow’s London Airport Cargo EDP Scheme might track freight carrier codes.
• If a carrier is rebranded, SCD Type 2 preserves the original brand for past shipments — crucial for regulatory reporting.

Financial Services

• Customer risk ratings may change periodically.
• Compliance audits require knowing the exact rating at the time of a transaction.

SCD Challenges

1. Data Volume
   • Type 2 can generate large dimension tables if changes are frequent.
2. ETL Complexity
   • Requires comparison logic between staging and current dimensions.
3. Query Complexity
   • Analysts must join fact tables to the correct dimension row based on the event date.
4. Schema Evolution
   • Adding new attributes requires updating SCD logic and history handling.

Modern SCD Implementation Patterns

Cloud Data Warehouses

• Snowflake — Use MERGE statements with date logic.
• BigQuery — Leverage ARRAY_AGG and QUALIFY for history.
• Redshift — Spectrum + merge staging patterns.

Data Transformation Frameworks

• dbt — Macros for SCD Type 2 handling.
• Informatica / Talend — Built-in SCD components.
• Spark / PySpark — DataFrame joins and window functions for history tracking.

Best Practices for SCDs

• Track change reason — Include a change_reason column for transparency.
• Version numbers — Easier than date ranges for some queries.
• Index on current_flag — Speeds up “current state” queries.
• Partition historical data — Improves performance in large Type 2 tables.
• Automate — Use metadata-driven ETL to handle SCD updates for multiple dimensions.

Final Thoughts

Slowly Changing Dimensions are about preserving truth over time.
Without them, your analytics lose credibility. With them, you gain:

• Trustworthy historical reporting
• Audit and compliance readiness
• Richer trend and change analysis

In the age of real-time analytics, streaming data, and machine learning, SCDs still matter. Even as event sourcing and immutable data lakes rise in popularity, dimension changes still need explicit modelling — and the SCD framework remains the industry’s common language for doing so.

References

1. Kimball, R. & Ross, M. — The Data Warehouse Toolkit (Wiley, 3rd Ed.)
2. Snowflake Docs — MERGE: https://docs.snowflake.com/en/sql-reference/sql/merge
3. dbt SCD Macros — https://docs.getdbt.com/docs/building-a-dbt-project/tests#scd
4. Redshift MERGE — https://docs.aws.amazon.com/redshift/latest/dg/merge.html