What is future data leakage in feature joins?

Future data leakage happens when a training row uses feature values that were not available when the prediction would have been made. The model then learns from information production would not have, so offline metrics look stronger than real performance. Current-state dimension joins are a common source because they silently use later updates.

How do you write an as-of join in SQL?

Join labels to feature history on the entity key, keep only feature rows whose availability timestamp is at or before the label prediction timestamp, then rank those candidate rows by feature timestamp descending. Select the first ranked row per label. That gives the most recent valid feature snapshot for each training example.

Which timestamp matters for point-in-time correctness?

Use the time the prediction would have been made on the label side. On the feature side, use when the feature became available to the model, not merely when the source event occurred. Late data, backfills and slowly changing dimensions can all leak if availability time is ignored.

SQL for ML Engineers

02.04 · Concept

Point-in-Time Correct Joins

Implement an as-of join that prevents future data leakage when joining historical features to labels.

No video curated for this lesson yet

This lesson is written, ordered and part of the path - the video slot is the only thing still open. We are working through Everything Data lesson by lesson; 55 of 85 have their video so far.

The written notes below cover this idea in full - you lose nothing by reading instead of watching.

Point-in-time joins attach each label to the latest feature value that was available at prediction time, not the value visible in the warehouse now. The usual SQL shape is an entity join, a feature timestamp filter at or before the prediction timestamp, and a windowed pick of the newest valid row.

What this lesson answers

how to prevent future leakage in SQL joins
as of join for machine learning features
which timestamp should labels use for training data

Notes

Point-in-time correct joins are used when building training data from historical records, especially when joining labels to features that change over time. The key rule is that each training example should only use information that would have been available at the moment the prediction was made. For example, if you are predicting whether a customer will churn on June 1, you cannot join in a customer attribute, account balance, support ticket summary, or risk score that was created or updated after June 1.

Common questions

What is future data leakage in feature joins?: Future data leakage happens when a training row uses feature values that were not available when the prediction would have been made. The model then learns from information production would not have, so offline metrics look stronger than real performance. Current-state dimension joins are a common source because they silently use later updates.
How do you write an as-of join in SQL?: Join labels to feature history on the entity key, keep only feature rows whose availability timestamp is at or before the label prediction timestamp, then rank those candidate rows by feature timestamp descending. Select the first ranked row per label. That gives the most recent valid feature snapshot for each training example.
Which timestamp matters for point-in-time correctness?: Use the time the prediction would have been made on the label side. On the feature side, use when the feature became available to the model, not merely when the source event occurred. Late data, backfills and slowly changing dimensions can all leak if availability time is ignored.

Short definition: what is Point-in-Time Correct Joins?

02.03 · SQL for ML EngineersWindow Functions for Features 02.05 · SQL for ML EngineersQuery Plans and Optimization