SQL, data modeling, data quality, EDA, visualization · insight-0052
Build training rows with as-of joins so each feature uses only values known at the cutoff
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data scientist, ML engineer
- Industries
- aviation, banking, retail
Summary: situation, action, reason, boundary
Situation
A training table joins each departure to the latest customer and route features in the warehouse.
Action
Give every training row a cutoff timestamp. Join each feature table on entity and the most recent feature timestamp at or before the cutoff, within a maximum age, and also filter on when the value was written if corrections are backfilled.
Reason
Point-in-time correctness means using information as it existed then; latest-value joins answer a different question.
Boundary
Forecasting, churn, credit, fraud and any model trained on historical snapshots.
Symptom or risk
Features computed after the prediction time leak into training, offline accuracy is inflated and production performance drops.
Exceptions
Features that never change after creation, such as birth year or route distance.
Trade-offs
As-of joins are more expensive and need history tables you may not yet keep.
Pitfalls
Using a feature's event timestamp while ignoring that the value was corrected later.
Lessons and concepts
Claims and their sources
Point-in-time joins scan backward from each entity row's timestamp up to a time-to-live, and can also filter on the created timestamp so backfilled or corrected values do not leak into training data.