How is a point-in-time correct join different from a normal SQL join?

A normal join usually asks which rows match by key, often using the latest state of a table. A point-in-time correct join also asks when the joined value became available. It filters out feature rows from after the prediction moment and picks the newest remaining row, so the training example reflects the information available at scoring time.

Should the join use event time or ingestion time?

It depends on what the model could actually access. Event time says when something happened; ingestion or processing time may say when the system learned it. For point-in-time correctness, the safest timestamp is availability time: the moment the feature value could have been used by the production prediction path.

Does an as-of join completely prevent data leakage?

No. It prevents one common leakage path: joining future feature values to past labels. Leakage can still enter through wrongly defined labels, features computed over future windows, backfilled tables, global aggregates, or preprocessing fitted on all data. Point-in-time joins are necessary for temporal ML data, but they are not a complete leakage audit.

What is the most common mistake when implementing this?

The common mistake is using the outcome time or the table update time without checking what it means. A churn label, for example, may be recorded after the customer behaviour being predicted. A feature row may describe an old event but be computed later. The join must use the prediction timestamp and the feature’s usable-from timestamp.

Point-in-Time Correct Joins

Point-in-time correct joins are joins that attach historical feature values to a training example only if those values were available at the prediction time. They prevent future data leakage by using an as-of rule: for each entity and prediction timestamp, select the latest valid feature record at or before that time.

The problem is that warehouses often show the world as it is known now, while training data must represent what was knowable then. A normal join to a customer, account, ticket, or risk table may pull in values written after the prediction moment. The model then learns from information production would not have had, producing offline metrics that look credible but collapse when the model is run live.

Mechanically, an as-of join matches label rows to feature rows by entity, keeps only feature records whose availability timestamp is not after the prediction timestamp, then chooses the most recent remaining record. In SQL this is commonly expressed with a join, a timestamp predicate, and a window function ordered by feature time descending. The important distinction is that the join is against historical feature state, not the table’s current row.

The main trade-off is that you need trustworthy time semantics, not just more SQL. The label time must mean when the prediction would have been made, while the feature time must mean when the value became usable. Late-arriving events, backfills, repaired dimensions, and batch pipelines can all make event time differ from availability time. If that distinction is lost, the query can look correct while still leaking.

Engineers meet point-in-time joins when building ML training sets, feature stores, churn models, fraud models, credit risk datasets, and any system where labels are observed after a prediction date. It is also common in audits of suspiciously strong models. A good practical test is: if this model had run live at that moment, could this exact value have been read by the scoring service?

Go deeper

Everything DataPoint-in-Time Correct JoinsImplement an as-of join that prevents future data leakage when joining historical features to labels.Open the lesson

Common questions

How is a point-in-time correct join different from a normal SQL join?: A normal join usually asks which rows match by key, often using the latest state of a table. A point-in-time correct join also asks when the joined value became available. It filters out feature rows from after the prediction moment and picks the newest remaining row, so the training example reflects the information available at scoring time.
Should the join use event time or ingestion time?: It depends on what the model could actually access. Event time says when something happened; ingestion or processing time may say when the system learned it. For point-in-time correctness, the safest timestamp is availability time: the moment the feature value could have been used by the production prediction path.
Does an as-of join completely prevent data leakage?: No. It prevents one common leakage path: joining future feature values to past labels. Leakage can still enter through wrongly defined labels, features computed over future windows, backfilled tables, global aggregates, or preprocessing fitted on all data. Point-in-time joins are necessary for temporal ML data, but they are not a complete leakage audit.
What is the most common mistake when implementing this?: The common mistake is using the outcome time or the table update time without checking what it means. A churn label, for example, may be recorded after the customer behaviour being predicted. A feature row may describe an old event but be computed later. The join must use the prediction timestamp and the feature’s usable-from timestamp.

Every term