Data platform · Storage & Modeling

SCD Type 1 vs Type 2

MediumSCD1SCD2dimensionshistory

Interview question

What is the difference between SCD Type 1 and Type 2?

How to answer

The short answer

Slowly Changing Dimensions (SCD) describe how dimension attributes change over time.

SCD1: overwrite the old value. No history.

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

SCD2: keep history with new rows (and usually 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

When to use which

  • SCD1: corrections, unimportant history (fix a typo in name)
  • SCD2: analytics must answer "what city were they in when they ordered?"

are a common SCD2 implementation helper.

Interview tip: Give the overwrite vs history contrast, then one business question that only SCD2 can answer.