A retail platform's product attributes change over time, and historical reports must reflect the values as they were. What modelling approach handles this?

The approach

Type 2 slowly changing dimensions: each change creates a new row with validity dates and a surrogate key, and facts reference the surrogate key that was current at the time of the event.

A report over last year's sales then joins to the attributes as they were, which is the requirement.

Why the alternatives fail

• Overwriting (type 1) loses history entirely. Last year's report changes when a product is recategorised — and the number was correct at the time, so a changed historical report is a correctness problem, not merely a surprise.
• A current-value join produces the same failure, and it is the default when someone joins facts to a dimension on the natural key.
• Storing the attribute on the fact works and denormalises heavily, which is acceptable for a small number of attributes and unmanageable for many.

The decisions that must be explicit

• Which attributes are historised. Not all of them — a product's name may not need history while its category does, and historising everything multiplies the dimension's size.
• What triggers a new version. A correction to a data-entry error should not create a version, since it means the old value was never true. Distinguishing a correction from a change is a business rule and it is frequently unstated.
• How late-arriving facts are handled, since a fact arriving after a dimension change must reference the version that was current at the event time rather than at the load time.
• How the surrogate key is assigned and made stable, since an unstable one breaks every historical join.

The failure that recurs

Joining on the natural key, which silently produces current values and is indistinguishable from correct output. Nothing errors; the numbers are simply wrong, and the discrepancy is usually noticed when someone compares a report to a printed copy from last quarter.

A test that asserts a historical report is stable over time is the control that catches it, and it is rarely written.

The pragmatic scoping

Historise the small set of attributes that reports actually slice by, and accept current values for the rest. That is a decision the business must make, and asking for it explicitly is more useful than modelling everything defensively.