Slowly changing dimensions describe how you handle attribute changes in dimension tables. Type 1 overwrites and loses history. Type 2 inserts a new row per change with a surrogate key, valid_from and valid_to timestamps, and a current flag, so facts join to the version of the attribute that was true at the time. Type 3 keeps a previous value column for a single prior state.
Why interviewers ask this
This is the core dimensional modeling question, and it separates people who have built a warehouse from people who have only queried one. Interviewers want the surrogate key, because natural keys break once you have multiple rows per entity, and they want to hear how facts join to the correct historical version rather than to whatever is current.
How to structure your answer
- Define types 1, 2, and 3 in one line each.
- Detail the type 2 columns: surrogate key, valid_from, valid_to, is_current.
- Explain how facts join to the correct version at event time.
- Cover the load logic: close the old row, insert the new one.
- Mention the cost: table growth and query complexity.
Example answer
Type 1 just overwrites the value, so you lose history. Type 2 keeps history by adding a row every time an attribute changes. In practice that means a surrogate key as the primary key, the natural business key alongside it, valid_from and valid_to timestamps, and an is_current boolean for convenience. The fact table stores the surrogate key that was current when the event happened, so a sales fact from March still joins to the sales rep's March territory rather than their current one. That distinction is the entire point, and it is the thing finance will notice if you get it wrong. The load is a merge: find rows where the tracked columns changed, set valid_to on the existing row, insert a new row with valid_from set and valid_to as null or a far future date. I usually hash the tracked columns into a single change key so the comparison is one equality check rather than twenty.
Walking into this interview soon? GhostPilot listens to your live call, spots the question the moment it is asked, and puts a structured answer on your screen in real time. Try it on your next mock, or grab a $29 Session Pass, no subscription, for the real thing.
See how it worksFollow-up questions to expect
- How do you handle a late arriving dimension record?
- What happens to your fact table if you use the natural key instead?
- How would you implement type 2 in dbt?
Related data engineer questions
Your interviewer will ask their own version of this. Paste your actual job description into the free Question Predictor and get the 20 questions that role is most likely to ask, with what each one is really probing.
Predict my questions