How would you answer "what was this customer's tier when they placed that order"?
Whether the candidate recognises that this is a history-modelling question rather than a query problem, and whether they know what it costs and when it becomes impossible.
The situation behind the question
Interviewers ask this because it happened to them.
Finance wants revenue split by customer tier for last March. The customer dimension holds the current tier only. Several customers have been upgraded since.
A strong answer
Flags
Green flags
- Recognises that the operational model forgets, and that the question is unanswerable rather than merely hard.
- Defines grain precisely for the dimension: one row per entity per validity period, not one per entity.
- Knows the fact must reference the version rather than the entity, and can say why.
- Preserves raw change records as the fallback that makes rebuilding possible.
Red flags
- Proposes joining to the current dimension and does not notice the answer changes over time.
- Wants Type 2 on every attribute, with no sense of the storage and join cost.
- Cannot say what a re-run of last March's report does when the dimension is Type 1.
- Believes a nightly snapshot of the dimension is equivalent, without noticing what it misses between snapshots.
Follow-ups
Where the conversation goes if the first answer holds up.
- The change happened a year ago and nothing tracked it. What are your options?
- A daily snapshot of the dimension is cheaper to build. What does it fail to capture?
- How do you keep a re-run of an old period from producing a different answer than it did the first time?