Point-in-Time Correctness and the As-Of Join

In one line: building a training set means asking what every feature was at a million different past moments, and the obvious way to do it silently gives the model the answer.

What an ordinary join does wrong

You have labels — a million events, each with an entity and a timestamp — and you want features for each.

The naive query joins labels to features on the entity key:

SELECT l.*, f.value
FROM labels l
JOIN features f ON l.entity_id = f.entity_id

That returns every historical value of the feature for that entity, and if you take the latest, you have joined a value from after the label's timestamp.

The model learns to predict conversion from a feature that went up because of the conversion. Offline accuracy is superb. The feature is worthless in production, where only the March 1st value exists.

The as-of join

The correct operation joins on the entity and takes the most recent feature value whose timestamp is at or before the label's timestamp.

For each label (entity, t):
  take the feature row where
    entity matches
    AND event_timestamp <= t
  ordered by event_timestamp DESC
  limit 1

This is a temporal join, an as-of join, or a point-in-time correct join — three names for the same thing. It answers "what was true for this entity as of that moment", which is exactly what the serving system would have seen.

Every feature in the training set is joined this way, each with its own timestamp condition, which is why doing it by hand across twenty features is where teams lose weeks and introduce quiet bugs.

Three ways it still goes wrong

Getting the join right is necessary and not sufficient.

The feature freshness window

An as-of join takes the most recent value at or before the label time. If the pipeline had not run for three days, that value is three days old.

That is correct — it is what serving would have had — and it means the training row's feature is stale in a way the model will learn as normal. Fine when serving is equally stale. A problem when training joins against a batch feature and serving reads a streaming one, because the model was trained on staler inputs than it receives.

A maximum staleness bound on the join is the guard: refuse to join a value older than some threshold, and let the feature be missing instead. Missing is a condition the model can handle explicitly; silently-stale is not.

Event time versus knowledge time

The problem from the previous lesson, arriving here as a join condition.

An as-of join on event_timestamp asks what was true. A serving system could only use what was known. If an order placed on the 1st reached the warehouse on the 3rd, an event-time join for a decision on the 2nd includes it — and the serving system could not have.

The strict version joins on created_timestamp instead, or on both. It is more conservative, more correct, and it produces a training set slightly worse than the world actually was — which is the right direction to err.

The label's own timestamp

Subtler, and worth naming because it is easy to get backwards.

A label has two relevant times: when the decision was made, and when the outcome was known. A fraud label has a transaction on the 1st and a chargeback on the 60th.

Features must be joined as of the decision time, not the outcome time. Joining as of the chargeback date gives the model sixty days of subsequent behaviour — including, very likely, evidence of the fraud it is meant to predict.

The cost of doing it properly

An as-of join over a million labels and twenty features is not a cheap query. Each feature is a separate temporal join against a table holding every historical value.

The optimisations are ordinary and worth naming:

Partition the offline store by time. A join for March labels should not scan two years.

Bound the lookback. Do not scan history older than the maximum staleness you would accept anyway.

Materialise common training sets. If several models train on the same entities and window, compute the join once.

Precompute at ingestion for the common case. If nearly every model wants the same twenty features at the same granularity, a periodically-materialised wide table is much cheaper than joining on demand — at the cost of flexibility.

Or avoid it entirely

The alternative from the earlier lesson, now with its trade fully visible.

Log the feature vector you actually served. No as-of join, no event-versus-knowledge-time question, no staleness bound — the training row is by construction what the model saw.

Neither dominates. Logging is correct and inflexible; as-of joins are flexible and fragile. Mature systems do both — log served vectors for the models in production, and keep the offline store with as-of joins for experimenting with features nobody is serving yet.

Being able to say that — rather than picking one — is the answer that holds up.

Next: the registry that makes a definition singular.