SQL, data modeling, data quality, EDA, visualization · insight-1049
Pick the slowly-changing-dimension type per attribute by asking whether history may change past answers
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A product team asks for the route dimension to be made "type 2", and every attribute, including a typo-corrected name and a reorganised sales region, starts creating new versions.
Action
For each attribute decide with its owner: correction of an error (overwrite, type 1), real change that should keep old reports unchanged (new version, type 2), or both current and original wanted (an extra column, type 3). Record the decision in the dimension's contract.
Reason
The type governs whether a past aggregate can change after the load, so it is a metric-definition decision, not a technical default.
Boundary
Customer, product, route, aircraft and organisation dimensions.
Symptom or risk
Versioning everything multiplies rows and joins, while overwriting everything silently rewrites last year's regional numbers.
Exceptions
Tiny dimensions that are fully reloaded with a snapshot each period.
Trade-offs
More versioning means more storage, more complex joins and slower backfills.
Pitfalls
Marking an attribute type 2 but joining facts to the current row only.
Lessons and concepts
Claims and their sources
A Kimball type 2 change inserts a new dimension row with its own surrogate key, start and end timestamps and a current flag, and facts store the key of the version in effect at the event.
A type 1 change overwrites the attribute, which loses history and requires recomputing aggregates that depend on it.