ORIGINAL COMPANY-CONTEXT PRACTICE
Assemble a decision snapshot without using future knowledge
Original practice scenario: A hypothetical operations team wants to reproduce which supplier facts an analyst could see before approving an order. Source records have event dates and later ingestion dates. Define the query.
Written for independent practice using company context; this is not a verified question from a company interview.
A REASONED APPROACH
Model answer.
Use both business-effective time and knowledge-availability time. A fact valid on the decision date may still have been unavailable to the analyst because it arrived afterward.
Work through it
- Store source identity, effective intervals, ingestion or publication timestamps and correction versions, defining the decision cutoff in a consistent time zone.
- Select records effective for the requested business time and known by the decision cutoff, then resolve correction precedence only within that visible evidence.
- Join at the intended supplier and order grain, returning unknown fields and provenance references rather than filling gaps with today's corrected state.
Tradeoffs to discuss
- Bitemporal history is more complex than a current snapshot but supports faithful replay and analysis of late-arriving corrections.
Common pitfalls
- Filtering only on event date still leaks future backfills.
- Check for overlapping effective records and use independent row-count controls so an as-of join does not multiply decisions.