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.
Background concepts from first principles
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.
What good looks like
Grain or policy first, boxes second. Atomic publish. Safe reruns. Clear out-of-scope.
Scope control
Protect the critical path. Park optional marts after the SLA landing succeeds.
Clarifying questions strategy
Ask what changes grain, SLA, retention, or cost. State assumptions when answers are vague.
What good looks like
Grain or policy first, boxes second. Atomic publish. Safe reruns. Clear out-of-scope.
Scope control
Protect the critical path. Park optional marts after the SLA landing succeeds.
Clarifying questions strategy
Ask what changes grain, SLA, retention, or cost. State assumptions when answers are vague.
What good looks like
Grain or policy first, boxes second. Atomic publish. Safe reruns. Clear out-of-scope.
Scope control
Protect the critical path. Park optional marts after the SLA landing succeeds.
Clarifying questions strategy
Ask what changes grain, SLA, retention, or cost. State assumptions when answers are vague.
What good looks like
Grain or policy first, boxes second. Atomic publish. Safe reruns. Clear out-of-scope.
Scope control
Protect the critical path. Park optional marts after the SLA landing succeeds.
Clarifying questions strategy
Ask what changes grain, SLA, retention, or cost. State assumptions when answers are vague.
What good looks like
Grain or policy first, boxes second. Atomic publish. Safe reruns. Clear out-of-scope.
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.