SQL, data modeling, data quality, EDA, visualization · insight-0051
Keep facts whose dimension record has not arrived yet, using an explicit unknown member
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, ML engineer
- Industries
- aviation, retail, e-commerce
Summary: situation, action, reason, boundary
Situation
Bookings for a newly opened route arrive before the route appears in the route dimension, and the reporting query uses an inner join.
Action
Insert a placeholder dimension row keyed by the unresolved natural key with "unknown" attributes, or left join and label unmatched rows. Monitor the count of facts mapped to placeholders and update them when the real record arrives.
Reason
Fact arrival and reference-data maintenance run on different schedules; placeholders preserve totals while the context catches up.
Boundary
New products, new customers, new routes and any master-data feed slower than transactions.
Symptom or risk
The new route's bookings vanish from totals until the dimension catches up, understating revenue and hiding the launch.
Exceptions
Regulated reports that must not show unclassified items may need to hold facts until classification is complete, with a documented delay.
Trade-offs
Placeholders make some slices temporarily vague ("unknown market").
Pitfalls
Leaving placeholder counts unmonitored so "unknown" quietly becomes a large category.
Lessons and concepts
Claims and their sources
When facts arrive before their dimension context, the fact can be posted against a special dimension row holding the unresolved natural key, which is overwritten when the context arrives.