Data Warehousing for Analytics
Advanced 15 min read Data Warehousing for Analytics

Slowly Changing Dimensions

Decide what history means when dimensions change

Slowly changing dimensions (SCD) decide what happens to history when an attribute changes. Type 1 overwrites, Type 2 keeps a new row per version, Type 3 keeps prior and current. The choice determines whether past slices stay accurate.

dimensionSCDslowly changing dimensiontype 1type 2historysurrogate key
Prerequisites: Star Schema Design Dimensional Modeling
Before we start

What you will learn

  • Explain the difference between SCD types 1, 2, and 3
  • Choose the right type for a given attribute
  • Implement a type-2 dimension with validity dates
Introduction

What is this?

When an attribute like a customer's region or plan changes, the dimension needs a policy. Type 1 overwrites (no history). Type 2 inserts a new version row with valid_from/valid_to dates so each fact joins the attribute as it was. Type 3 keeps only current plus a prior value. Every attribute's policy should be declared.

Motivation

Why do you need it?

  • Without a policy, someone overwrites and silently rewrites history.
  • Reporting 'as it was when the order happened' requires type 2 for the right attributes.
  • Choosing the type IS the analytics decision: history vs simplicity.
Think about it

Mental model

Imagine

Rewriting yesterday's ledger entry

Think of

An SCD type-1 update

Rarely right for a business: the order that shipped to the old address (or new region) should keep its truth. Type 2 is the ledger with strikethrough and a new line.

Mechanics

How it works

  • Classify each attribute: overwrite (type 1), keep history (type 2), or prior+current (type 3).
  • For type 2, add surrogate keys plus valid_from and valid_to, and close the current row on change.
  • Fact rows bind to the dimension version current at the fact's time.
  • Querying an as-of date uses the valid window to pick the right version.

A changing attribute either overwrites or spawns a second version row.

Change
Type 1
Type 2
As-of
Real world

In production

  • Territories change for a sales rep: type 2 keeps old deals attributed to the old territory.
  • A product's category changes: type 1 is fine if historical category splits are irrelevant.
  • Customer plan tiers: type 2 supports 'revenue by plan as-of each month'.
Guidelines

Best practices

  • Decide the policy attribute-by-attribute and write it down.
  • Give type-2 dims surrogate keys and never reuse them.
  • Close current rows with valid_to and mark the current via a flag or null.
Watch out

Common mistakes

  • Applying type 1 to everything and quietly rewriting history.
  • Type-2 keys that churn on every refresh, blasting the fact-downtown joins.
  • Missing valid_to handling so joins return two active versions.
When things break

Debugging tips

  • If a fact joins two dim versions, check valid windows overlap — fix the closure logic.
  • If as-of reports differ from raw history, one attribute was wrongly type 1'd.
Interviews

Interview checkpoint

Practice

Hands-on lab

Implement a Type-2 Customer Dimension
45 min · advanced
In progress
Objectives
  • Add surrogate keys and valid_from/valid_to to a dimension
  • Detect changes and close old versions
  • Run an as-of join that resolves to the version in effect

Implement a Type-2 Customer Dimension

45 min · advanced

0/3 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

SCD type 2 records history by

2

A field where history must never change should use

Go deeper

Official documentation

Slowly changing dimension (Wikipedia)
docs
SCD patterns (Kimball)
docs