Point-in-Time Feature Reads and the Skew They Prevent

A feature table that holds the current value of a feature is a table of the present. Training needs the value that was current at the moment each label was observed, which is a different question and usually a different answer. A user’s lifetime order count today is not the count that was visible when their last purchase was labelled as fraudulent, and a model trained on the present value has been given information from the future of every one of its training examples.

The mechanism that prevents this is a point-in-time read: a join that selects, for each labelled event, the feature row that was valid at that event’s timestamp rather than the row that is valid now. It is a temporal join, and it is the reason feature stores keep history rather than only current state.

Two clocks, and which one the label belongs to

Every labelled example carries a timestamp, and the timestamp is not the time the row was written. It is the time at which the outcome became determined, which is the moment the features are allowed to depend on.

For a transaction fraud label, the outcome is determined when the chargeback is filed, weeks after the transaction. The features must be as of the transaction time, not the chargeback time, because at scoring time the system will only have the transaction time. For a recommendation label, the outcome is determined when the user clicks, which is shortly after the impression, and the features must be as of the impression.

Getting this wrong in either direction produces a specific pathology. Using the later timestamp leaks information the serving path cannot see, and the model’s offline metrics are unreachable online. Using a timestamp after the features were actually written but before the outcome is determined can capture a feature update that was caused by the event itself, which is a subtle form of leakage that survives an as-of join if the feature table records write time rather than event time.

The rule is that the as-of timestamp must be strictly before the outcome determination and no later than the latest information available at serving time. Those two constraints often leave a narrow window, and identifying it is the first task in building a training set.

The join shape

A point-in-time join takes a spine of labelled events, each with an entity key and a timestamp, and for each event selects the latest feature row for that entity whose validity start is at or before the event timestamp. If the feature table records intervals with an explicit end, the condition is that the event timestamp falls inside the interval.

Two table shapes support this. The interval shape stores valid_from and valid_to per row, with the invariant that intervals for one entity do not overlap. The version shape stores only valid_from per row and relies on a window function or a correlated subquery to pick the latest row at or before the timestamp. The interval shape makes the join cheaper and the invariant explicit; the version shape is easier to write from an append-only log.

-- Spine: one row per labelled event.
-- features: entity_id, valid_from, valid_to, value
SELECT s.event_id, s.label, f.value
FROM spine AS s
LEFT JOIN features AS f
  ON f.entity_id = s.entity_id
 AND f.valid_from <= s.event_time
 AND (f.valid_to > s.event_time OR f.valid_to IS NULL);

The LEFT JOIN is deliberate. An entity with no feature row at the event time is a real case, and dropping it biases the training set toward entities that existed early. Keeping it produces a null that the model has to handle at serving time as well, which is exactly the behaviour being tested.

The cost of this join is the interesting part. A naive implementation is a nested loop over the spine with an index lookup into the feature table per row, which for a spine of $n$ rows and a feature table of $m$ rows costs $n \log m$ index probes. That is acceptable for batch training and unacceptable when the spine is large and the feature table is huge, which is why warehouse engines offer a specialised as-of join operator that sorts both sides by entity and time and merges them in one pass.

Why equality on the key is not enough

The join above looks like a range join, and a query planner that treats it as one will produce a plan proportional to the number of matching pairs, which for a high-frequency feature can be enormous. The saving structure is that the join is per entity and ordered in time: sort both inputs by entity then timestamp, and the match for each spine row is the last feature row before it.

A merge-based plan costs one sort of each side, $O(n \log n + m \log m)$, plus a linear scan. A nested-loop plan with an index costs $n$ probes, each $O(\log m)$, which is asymptotically similar but has a much worse constant when the probe touches a cold page. The practical difference is large enough that warehouses expose an explicit operator rather than relying on the planner to recognise the pattern.

There is a correctness trap in the merge formulation. If the feature table has two rows with the same entity and the same valid_from, the “latest” row is ambiguous, and different engines will pick different ones. Deduplicating on the (entity, valid_from) key at write time is the only way to make the join deterministic, and it has to happen upstream, because a training query cannot repair a table that has two versions of the same instant.

Backfills and the label horizon

A feature that is computed by a batch job running daily has a lag: the value for day $d$ becomes available on day $d+1$. A model trained on the value as of the event time would use the day-$d$ value for an event on day $d$, but at serving time on day $d$ that value does not exist yet.

The fix is to shift the as-of timestamp back by the feature’s availability lag. This is correct and it is easy to forget per feature, which is why the lag belongs in the feature’s definition rather than in each training query. A feature defined as “orders in the last 30 days, available with one day of lag” has an effective as-of time of event time minus one day, and every consumer applies the same shift.

A backfill is the operation of recomputing history after a feature definition changes. It is dangerous in exactly the way that matters here: a backfill that writes corrected values into the same table with new valid_from timestamps changes what a point-in-time join returns for historical events. A model retrained after a backfill sees different features than the model it replaces, which is intended, but a model retrained after a backfill that was not applied to the serving path sees a distribution the server cannot reproduce. Backfills and deployments have to be coordinated, and the coordination is a versioning problem of the kind described in Model Artifact Identity: the feature definition version is part of what the model was trained against.

The serving path is a point-in-time read of the present

At request time the same join runs with the current timestamp and a single entity key. The server reads the latest row whose valid_from is at or before now, which is usually the current row, and this is where the two paths diverge.

The divergence is rarely in the join logic. It is in the surrounding computation. A training pipeline computes “orders in the last 30 days” as a SQL aggregate over a warehouse table that includes late-arriving records; the serving path computes the same aggregate from a streaming counter that includes only records processed so far. The two definitions agree on paper and differ on the boundary: an order that arrives with a two-day delay is in the training aggregate and not in the streaming one.

Three properties need to match, and each fails independently.

• Window boundaries. A 30-day window anchored at midnight versus anchored at the request timestamp selects different sets. The difference is small per request and systematic across the population.
• Late data. Batch pipelines reprocess late records; streaming counters usually do not. The training feature is a retrospective truth, the serving feature is a provisional estimate.
• Null and default handling. A missing value filled with zero in training and with a sentinel in serving produces a feature the model has never seen in its training distribution.

The standard defence is to log the serving features alongside each prediction and to periodically compare the logged distribution against the training distribution, per feature. That comparison is a drift check, and it is described in Windowed Drift Detection. It catches the aggregate effect of divergence but not the cause, which is why the logging has to record the feature values themselves rather than only the final prediction.

Where the mechanism stops paying

Point-in-time joins make training data honest about time. They do not make it honest about causality, and the difference is worth stating because the join is often treated as a complete answer.

A feature computed from an event that the label also depends on is leaked even when the as-of join is correct, as long as the feature value is written before the label is determined. The transaction example is the clean case: a fraud feature that counts declines in the last hour is affected by the fraudulent transaction itself, and the as-of join faithfully returns a value that already contains the outcome.

Detecting this requires reasoning about which writes are downstream of the event, which is a property of the data pipeline and not of the query. The join guarantees that the feature is old enough; it does not guarantee that the feature is independent. Both properties are needed, and only the first is mechanisable.

The second limitation is cost. Maintaining interval history for every feature multiplies storage by the number of versions per entity, and a feature that changes on every request has history proportional to traffic. Most feature stores therefore keep full history for a subset of features and current values for the rest, and the subset that gets history is chosen by whether a label can arrive later than the feature changes. That criterion is a property of the label, not of the feature, which is why the decision has to be made per label-feature pair rather than per table.