Data Warehousing
Intermediate 15 min read Data Warehousing

Dimensions and Slowly Changing Dimensions

When a customer moves, history should not move with them

Dimensions describe facts. Slowly Changing Dimensions (SCDs) manage what happens when a description changes: overwrite (type 1), keep history (type 2), or keep only current (type 3).

dimensionSCD1SCD2surrogate keyhistorytype 2
Prerequisites: Facts and Measures
Before we start

What you will learn

  • Classify dimension changes as type 1, 2, or 3
  • Implement a type-2 dimension with surrogate keys and validity dates
  • Choose SCD strategy from the business's 'as of' questions
Introduction

What is this?

A dimension holds descriptive attributes. Slowly changing dimensions handle entity changes over time. Type 1 overwrites (fast, loses history), type 2 creates a new row per version (keeps full history), type 3 keeps one column of 'old' value (limited history).

Motivation

Why do you need it?

  • Analysts constantly ask 'as of then' questions (revenue by customer region, as of that date).
  • A wrong SCD choice silently rewrites history and breaks 'prior period' comparisons.
  • Type 2 is the workhorse; knowing its costs (share of dimension size, join complexity) matters.
Think about it

Mental model

Imagine

Your former address on a fixed paper form

Think of

Type 3 (keeping one previous value only)

The form keeps only current + last; older history is gone. Type 2 would keep a folder of every address; type 1 would just erase the old one entirely. Each answers a different 'what did it look like then?' question.

Mechanics

How it works

  • A surrogate key identifies the 'customer' entity with versions; each row carries validity dates and a hash of attributes.
  • Type 1 updates in place: cheap, but retroactive restatements disappear.
  • Type 2 inserts a new version and closes the old (valid_from/valid_to), preserving history while facts point to versions.
  • Type 3 tracks one or two prior values in dedicated columns for quick 'previous vs current' comparisons.
  • Hashing all attributes compares versions cheaply: identical hash means no change, skip the row.

A type-2 version chain over time.

v1 created
Attribute changes
v1 closed
v2 opened
Facts join to version
Hands-on

Examples

Detecting change with a hash, then versioning

Detecting change with a hash, then versioning
-- one-row-per-customer version scan pattern (SQL-ish rules):
-- 1. hash = md5(concat(region, tier, ...))
-- 2. compare with latest version's hash
-- 3. if different: close latest (valid_to = today-1), insert new (valid_from = today)
 
-- Reading as-of:
SELECT sum(f.gross_amount)
FROM fact_sales f
JOIN dim_customer d ON f.customer_key = d.customer_key
WHERE '2026-02-15' BETWEEN d.valid_from AND d.valid_to
  AND d.region = 'West';

Validity dates let any fact row resolve exactly which version described it then.

Real world

In production

  • A sales dashboard hides 'as of quarter' figures by joining facts to the dimension version valid that day.
  • A compliance team wants change audit for addresses: type 2 preserves the trail.
  • A fast-moving campaign dimension uses type 1 for low-stakes attributes and reserves type 2 for tracked ones.
Guidelines

Best practices

  • Pick type 1/2/3 per attribute or per requirement, not wholesale.
  • Hash every attribute to detect changes cheaply instead of comparing column by column.
  • Include dbt-style snapshot columns (dbt_valid_from/to) and test them in the model.
  • Bound type-2 dimension growth: one version per change event, not per row touched.
Watch out

Common mistakes

  • Applying type 2 to every column, doubling dimension size for attributes nobody queries historically.
  • Using valid_to = 9999-12-31 everywhere without handling open-ended joins.
  • Overwriting a type-1 revision and making 'this period vs last year' comparisons impossible.
When things break

Debugging tips

  • When as-of reports show missing rows, check the version with valid_to before the query date.
  • A type-2 dimension exploding in size usually indicates a churning attribute (e.g. last_updated) folded into the hash.
  • Verify your hash excluding volatile columns before trusting the 'no change' short-circuit.
Interviews

Interview checkpoint

Practice

Hands-on lab

Implement a Type-2 Dimension with Hash Detection
40 min · intermediate
In progress
Objectives
  • Track source customer updates with an attribute hash
  • Close the current version and open a new one only on real changes
  • Answer an as-of question against versioned rows

Implement a Type-2 Dimension with Hash Detection

40 min · intermediate

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

Quick quiz

0/2
1

Which SCD strategy preserves full history by creating a new row per change?

2

A growing type-2 dimension is a perfectly normal, expected behavior for history-preserving attributes.

Go deeper

Official documentation

Kimball slowly changing dimensions
docs
dbt snapshots
docs