Slowly Changing Dimension (SCD)

Slowly Changing Dimension (SCD) is a set of techniques for managing how dimension attributes change over time in a data warehouse. A customer moves to a new city; a product is re-categorized. The question SCD answers is whether and how the warehouse should remember the old value. The chosen type determines whether historical facts reflect the attribute as it was or as it is now.

How It Works

The common types are:

• Type 0 (retain original): the attribute never changes; original value is fixed.
• Type 1 (overwrite): the new value replaces the old; no history is kept. Simple, but past reports change retroactively.
• Type 2 (add new row): a new dimension row is inserted for the changed entity with a new surrogate key, plus effective-from/effective-to dates and a current-flag. History is fully preserved, and facts link to the version valid at the time.
• Type 3 (add new column): a "previous value" column stores limited prior history alongside the current value.
• Type 4 / 6 (hybrid/mini-dimension): combine techniques, often using a history table or blending types 1, 2, and 3.

Type 2 is the most widely used when accurate point-in-time reporting matters, and it relies on surrogate keys so a single business entity can have multiple dimension rows.

When to Use It

Use SCD whenever dimension attributes can change and the warehouse must report consistently across time. Type 2 is the default when you need point-in-time accuracy (revenue by the customer's region at the time of sale); Type 1 suffices when only the current value matters and history is irrelevant.

Trade-offs

Type 2 grows dimension tables and complicates ETL: detecting changes, closing old rows, generating surrogate keys, and managing effective dates. Type 1 is simple but loses history and silently rewrites the past. Choosing the wrong type is expensive to reverse once facts are loaded. Performance and storage costs rise with high-churn dimensions, sometimes motivating mini-dimensions for fast-changing attributes.

Related Patterns

SCD is a core technique within the star-schema dimensional model, often exposed through a materialized-view, and conceptually related to event-sourcing in its goal of preserving change history.

Example

A customer dimension uses Type 2. When a customer relocates, the warehouse closes the old row (sets its effective-to date and clears the current flag) and inserts a new row with a fresh surrogate key. Sales facts continue to point at the surrogate key valid when each sale occurred, so a regional revenue report stays accurate for every historical period.