System design interview

Slowly Changing Dimensions (SCD Type 1 vs Type 2)

EasyLocked55 min read

Explain SCD Type 1 vs Type 2 and how to implement Type 2 in a batch pipeline.

system-designscddimensional-modelingbatch

Start here

The problem in plain words

A customer named Asha lives in Bengaluru and places orders for two years. Then she moves to Mumbai. Finance asks, "What was our revenue by customer city last year?" If the system overwrote her city with Mumbai, all those Bengaluru orders now appear to come from Mumbai, and last year's report changes after the fact.

Things like a customer's city, a product's category or an employee's department change slowly over time. These are called slowly changing dimensions. For each one you have to decide whether to forget the old value or to keep it.

Type 1 means overwrite and forget. Type 2 means keep a new row each time, with the dates it was valid. The skill is knowing which to choose, and being able to describe how a nightly job maintains Type 2 without making duplicates.

Words you will meet

Dimension
A table describing things analysts group by, such as customers or products.
Slowly changing dimension (SCD)
A dimension whose attributes change occasionally, and which needs a rule for handling the change.
Type 1
Overwrite the old value. Simple, but the history is lost.
Type 2
Close the old row and add a new row with the new value, keeping validity dates, so history is preserved.
Natural key
The identifier from the source, such as customer id C1. It stays the same across changes.
Surrogate key
A new number for each row in the dimension. In Type 2 each version of a customer has its own.
valid_from / valid_to
The dates between which a row was the true version.
is_current
A flag that marks the latest version of each natural key.
Idempotent load
Running the same day's load again does not create extra rows.
What overwriting does to last year's report
movesreportCustomer rowcity = BengaluruOverwritecity = MumbaiRevenue by cityold orders now sayMumbai
Press Play to watch it move

Each arrow is one thing happening. Play it step by step, or read the list below.

  1. 1.Asha moves from Bengaluru to Mumbai. The load simply overwrites her city.
  2. 2.Finance runs last year's revenue by city. Every old Bengaluru order now appears under Mumbai. The past was rewritten.

The numbers, as things you can picture

  • Daily batch updatesThe dimension is refreshed once a day from the source database or change events.

What the interviewer wants

Explain Type 1 and Type 2 with the customer example, say when to use each, describe the batch steps that maintain Type 2, and show how fact rows point to the right version.

The interview setup

Explain SCD Type 1 (overwrite) versus SCD Type 2 (history with valid_from/valid_to). When do you use each? How do you implement Type 2 in a batch pipeline?

This is a teaching interview. Start with a customer moving cities and a historical revenue-by-city question. Then name the types.

Dimensions change

Customers move. Products get new categories. Sales reps change regions. Analytics still asks questions about the past.

Type 1 - overwrite

Replace the attribute in place. Correct typos. Accept that history of that attribute is lost.

Type 2 - history row

Insert a new row with a new surrogate key and validity interval. Close the old row. Facts point at the key that was true at event time.

Type 3 (only if asked)

Keep current and previous in columns. Limited history. Rarely the default.

Why surrogate keys matter

If facts store natural keys only, you cannot attach a fact to the dimension version that was true historically without time predicates everywhere. Surrogate keys bake the version into the fact.

The expanded problem

  • Define Type 1 and Type 2 clearly
  • Give when-to-use criteria
  • Describe batch merge steps for Type 2
  • Explain how facts reference Type 2 keys
  • Discuss late-arriving dimension changes

What good looks like

  • City move story first
  • Clear Type 1 vs Type 2
  • Stepwise batch merge
  • Fact lookup by natural key + timestamp
  • Caution against Type 2 everywhere

Clarifying questions

  • Which attributes need history?
  • What is the effective dating source of truth?
  • Daily batch only, or CDC streaming dims?
  • How to handle late attribute changes after facts loaded?

Out of scope

  • Every SCD variant encyclopedia
  • UI for master data management
  • Real-time CDC deep dive unless invited

Clarifying questions strategy

Ask what changes grain, SLA, retention, or cost. State assumptions when answers are vague.

Scope control

Protect the critical path. Park optional marts after the SLA landing succeeds.

Interview framing details

You are expected to teach while you design. Start from a concrete failure, define the terms, then put the algorithm or layout on the board. Keep the scope tight: one grain, one SLA, one publication method.

Ask only the clarifying questions that would change your diagram. Write assumptions when the interviewer asks you to decide. Call out out-of-scope work so you do not burn the clock on tooling trivia.

A strong close restates the critical path, the failure mode you fear most, and what you would ship in week one.