When descriptive data changes.
WHAT THE PROBLEM IS
A customer moves city. Do past sales belong to the old city or the new one?
WHY IT MATTERS
Both answers are correct for different questions, and choosing wrongly makes history unreliable.
WHAT THE APPROACHES ARE
Overwrite the old value, keeping no history Add a new row, keeping the old one with validity dates Keep the previous value in an additional column Combinations of these
WHAT OVERWRITING SUITS
Corrections of errors, where the old value was never true.
WHAT ADDING A ROW SUITS
Genuine changes where history matters.
WHAT IT REQUIRES
A surrogate key per version Validity dates A flag marking the current version
WHAT THE FACT TABLE THEN REFERENCES
The version that was current when the event occurred.
WHY THAT IS THE POINT
Historical reports remain correct, permanently.
WHAT TO DECIDE PER ATTRIBUTE
Which approach applies.
WHY PER ATTRIBUTE
A corrected spelling and a genuine relocation warrant different treatment.
WHAT TO DOCUMENT
That decision, for every dimension.
WHAT TO TEST
That a report for a past period does not change when a dimension is updated.
