The business asks why the regional sales report changed for a period that closed six months ago. What happened, and what is the fix?
Show the full answer Hide the answer
What the interviewer is testing
Whether you recognise the most common cause of retrospectively changing historical reports.
What happened
A customer or store moved between regions. The dimension is Type 1 — it overwrites — so the attribute now holds the current region, and every historical fact joined to it is attributed to the new region.
Last year's revenue moved between regions retrospectively. Nothing is broken; the model chose to discard history.
The fix
Type 2: a new dimension row with validity dates and a current-record flag. Facts join on the surrogate key that was current at the time, so historical reports remain stable and current reports reflect the new region.
The costs to plan for: the dimension grows; every fact must carry the surrogate key rather than the natural key; and queries must filter on the current flag or on effective dates — forgetting that filter is the single most common cause of inflated numbers in a warehouse, since each fact matches multiple dimension rows.
The decision framework
Not every attribute needs Type 2. Ask: is this attribute used to group or filter historical reporting?
| Attribute | Type |
|---|---|
| Misspelled name corrected | 1 — the old value was wrong |
| Sales region | 2 — reported on |
| Customer segment | 2 — reported on |
| Contact email | 1 — operational, not analytical |
| Product category | 2 — almost always |
What a strong answer adds
Recognising this as bitemporality in disguise: any system reporting on the past must decide whether it reports what it knows now or what it knew then. Deciding by accident is how two dashboards end up disagreeing, and the decision belongs in the model rather than in each report.
Common weak answers
Blaming the report. Converting every dimension to Type 2 without considering the query complexity it imposes.