Loading lesson...
"How do you track changes over time?" is the question that separates modelers from query writers
"How do you track changes over time?" is the question that separates modelers from query writers
Topics covered: When Dimensions Change, Type 1 vs Type 2 vs Type 3 Tradeoffs, Implementing Type 2 with Effective Dates, Hybrid SCD and Type 6, SCD at Pipeline Scale
What They're Really Testing The interviewer already knows you can recite SCD types 1 through 3. They are testing three things: (1) whether you ask about the business requirement before choosing a type, (2) whether you understand the storage and query cost tradeoffs, and (3) whether you have implemented this at scale and know the operational pain points. The 60-Second Framework Step 4 is the strong-hire signal. Not 'I'd use Type 2' but 'Type 2 for THIS attribute because of THIS business reason, a
The interview is not about knowing these types. It is about choosing between them under constraints. Each type is a tradeoff between history preservation, storage cost, query complexity, and pipeline complexity. Type 1: What to Say When History Doesn't Matter Your Type 1 answer: 'Type 1 overwrites the old value. I use it for attributes where nobody ever queries the historical value: name corrections, phone number updates, typo fixes. Applying Type 2 to a phone number creates rows nobody will eve
Most candidates can explain Type 2 conceptually. The interview escalation is: 'Now implement it.' This is where candidates who have only read about Type 2 stall, and candidates who have built it in production accelerate. The Schema You Should Be Able to Write in 60 Seconds The MERGE Pattern: What the Interviewer Expects You to Produce The row_hash comparison is the key optimization. Without it, you would need to compare every tracked column in the WHERE clause. With it, a single hash comparison
Type 6 appears in senior interviews at companies dealing with customer segmentation or geographic reorganizations. The interviewer tests whether you know it exists, when it is justified, and what it costs. Saying 'I have never heard of Type 6' is not disqualifying, but knowing it and being able to articulate the write-amplification tradeoff is a clear differentiator. The Trigger: When the Interviewer Describes This Scenario Your Type 6 answer: 'I need Type 6 when the dashboard shows both histori
Conceptual SCD is easy. SCD at 100M+ dimension rows with daily updates is where interviews get hard. This section covers the operational pain points that only surface at scale, and the vocabulary that tells the interviewer you have lived through them. The Four Scale Questions Interviewers Use to Probe Depth Incremental vs Full Rebuild The senior answer: 'We run incremental SCD daily for speed, and a full rebuild weekly as a reconciliation step. If the full rebuild produces different results, we