SQL, data modeling, data quality, EDA, visualization · insight-0050
Join facts to the dimension version that was valid at event time, not to current attributes
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- aviation, retail, banking
Summary: situation, action, reason, boundary
Situation
A route was reclassified from domestic to intl-short last year, and a revenue-by-market report joins all history to the current route table.
Action
Keep dimension history as type 2 rows with valid_from, valid_to and a current flag, and join facts with event_date >= valid_from AND event_date < valid_to. Decide explicitly which reports want "as was" and which want "as is".
Reason
A type 2 dimension adds a new row per change, and facts reference the version in force when they occurred.
Boundary
Customer segments, product categories, route classifications, sales territories and pricing tiers.
Symptom or risk
Past revenue moves to the new market, so historical trends and model training labels reflect today's classification rather than what applied then.
Exceptions
Restated reporting, where the business asks to view all history under the current hierarchy, legitimately uses current attributes.
Trade-offs
History tables grow and range joins are slower than key joins.
Pitfalls
Overlapping or gapped validity intervals, which duplicate or drop facts.
Lessons and concepts
Claims and their sources
A type 2 slowly changing dimension adds a new row with a new surrogate key for each change, with effective and expiration dates and a current-row indicator; fact rows use the version in force at the time.