Evidlake
Menu

Slowly Changing Dimension

Abbreviation: SCD. Also known as: SCD type 2.

A descriptive attribute, such as a product's category, that changes rarely. Type 2 handling keeps every version with validity dates.

Updated Last reviewed

Synthetic dataExamples on this page use synthetic data created for illustration.

Definition

A slowly changing dimension is a descriptive attribute, such as a product’s category or a customer’s region, that changes only occasionally. Type 2 handling keeps every version with validity dates, so each fact is joined to the description that was true when it happened.

In plain terms

Facts (a sale, a click) happen once; descriptions (which category a product belongs to) change. If you overwrite the description, every old fact suddenly appears under the new one and history is rewritten. This is the same trap as using today’s data to judge the past.

The standard remedy, called type 2, adds a new row for each version of the description with the dates it was valid. Joining a fact to the row whose dates contain the fact’s date gives the right answer for any period.

Example

A product moves from category Kitchen to Home on 1 June. It sold 50 units a month from January to May and 60 in June.

HandlingKitchenHome
Type 1: overwrite the category0 units310 units
Type 2: keep both versions with dates250 units60 units

With type 1, a report on last quarter’s Kitchen sales would show nothing, although the product was a Kitchen item all that time.

Common misunderstandings

  • “Type 1 is wrong.” Overwriting is fine when history does not matter, for example to fix a typo.
  • “It is only for customer data.” Product categories, price lists, organisational hierarchies and tax rules all change slowly.
  • “The validity dates are just an audit trail.” They are what makes correct historical joins possible.
  • Bitemporal Data

    Data that records two times for every fact: when it was true in the world, and when the system learned it.

  • Point-in-Time

    Reading data as it was known at a past moment — not as it looks today after later corrections and additions.

  • Look-ahead Bias

    A study error where information that was not yet available at the time of a decision is used to evaluate that decision.

References

  1. Ralph Kimball, Margy Ross. The Data Warehouse Toolkit . Wiley, 2013. ISBN 978-1118530801.
  2. Type 2: Add New Row (dimensional modeling technique) . Kimball Group.