Data modeling · Foundations

When to use SCD Type 1 vs Type 2

MediumSCD1SCD2historypoint-in-time

Interview question

When would you choose SCD Type 1 versus Type 2?

How to answer

The short answer

Choose based on whether historical truth matters for analytics.

Use Type 1 when

  • The change is a correction (typo in name, fixed phone format)
  • History of the attribute is unimportant for reporting
  • Stakeholders always want the latest label ("show current brand name everywhere")

Use Type 2 when

  • You must answer point-in-time questions: "What segment was this customer in when they ordered?"
  • Attribute changes would otherwise rewrite last year's dashboards
  • Compliance or audit needs prior values

Mixed design is normal

On dim_customer, typos on email might be Type 1, while city and loyalty_tier are Type 2. Not every column needs history.

Cost of Type 2

More rows, required, fact loads must look up the correct version for event time.

Interview tip

Give one business question that only Type 2 can answer, then say Type 1 is for corrections and non-historical attributes.