Data modeling · Foundations

SCD overview: Type 1, 2, and 3

MediumSCDSCD1SCD2SCD3dimensions

Interview question

What are Slowly Changing Dimensions? Explain Type 1, Type 2, and Type 3.

How to answer

The short answer

Slowly Changing Dimensions (SCD) describe how dimension attributes change over time (customer city, product category, employee department).

Type 1: overwrite

Replace the old value. No history.

Output
customer_id=7 city: Austin -> Dallas
table now shows Dallas only

Type 2: new row (history)

Insert a new version row; keep old rows with validity windows (valid_from / valid_to or is_current).

Output
customer_id=7
row1: Austin  valid 2024-01-01 to 2026-03-01  is_current=false
row2: Dallas  valid 2026-03-01 to null        is_current=true

Type 3: previous value column

Keep limited history in columns like current_city and previous_city (or city and city_prior). Only one prior value (or a fixed few), not full history.

Output
customer_id=7  city=Dallas  previous_city=Austin

Quick compare

TypeHistoryTypical use
1NoneCorrections, typos
2Full (row versions)"What city when they ordered?"
3Limited (columns)Need prior + current only

Interview tip

Lead with Type 1 vs Type 2; mention Type 3 as "prior value columns for limited history."