Diagnosing a Point-in-Time Leak
A team trains a churn model on rows of the form
(user_id, snapshot_date, label = churned within 30 days), joining a
user_features table that is fully recomputed every Friday night with
columns such as sessions_last_30d, support_tickets_last_30d and
days_since_last_login. Offline PR-AUC is 0.91; in production the
first month's PR-AUC is 0.52.
- Explain the most likely cause of the gap, with a concrete example of how a training row is contaminated.
- Rewrite the join so that it is point-in-time correct (SQL or pseudocode is fine).
- One of the three features has a second leakage problem unrelated to the join. Which one, and what would you do about it?
1. Cause
The features are joined "as of the latest Friday", not as of
snapshot_date. A row with snapshot_date = Monday 3rd and a label
window covering the 3rd–2nd of next month is joined to features
recomputed on Friday 7th (or later, if the training job ran weeks
afterwards). days_since_last_login then reflects logins that
happened after the snapshot — inside the label window. If the user
churned, the feature shows a large gap; if they stayed, a small one.
The model learns to read the label out of the feature. In production
the feature can only reflect the past, so the signal disappears and
the metric collapses. This is temporal leakage via a non-point-in-time
join.
2. Point-in-time join
Keep every weekly snapshot of user_features with its feature_ts
instead of overwriting, then join each row to the latest snapshot at
or before its snapshot_date:
SELECT r.user_id, r.snapshot_date, r.label, f.*
FROM training_rows r
JOIN user_features_history f
ON f.user_id = r.user_id
AND f.feature_ts = (
SELECT MAX(feature_ts) FROM user_features_history h
WHERE h.user_id = r.user_id AND h.feature_ts <= r.snapshot_date
);
Or with an ASOF join where the engine supports it. Better still, log the feature vector at prediction time and train on the log, which makes the join unnecessary. Note the freshness cost: with weekly snapshots the features are up to 6 days stale — the same staleness the model will see online, which is exactly the point.
3. Label leakage through a feature
support_tickets_last_30d is suspicious even with a correct join:
users frequently open a ticket as part of cancelling ("how do I
export my data?"), so the feature is partly caused by the churn event
rather than predictive of it. Options: restrict to tickets opened
before a buffer period (e.g. exclude the last 7 days before the
snapshot), separate ticket categories and drop cancellation-related
ones, or check feature importance and the timing of tickets relative
to churn to confirm. In an interview, the general rule to state is:
for every feature, ask "could this be caused by the label?"
Share this question