Point-in-Time Feature Assembly and the As-Of Join

A label carries an event time. A loan defaults on a date, a user converts on a date, a machine fails on a date. The features used to predict that label must be the values of their sources as recorded before that date, because anything later is knowledge the model will not have when it is asked to score a new application. The naive join computes features from the current state of the source tables and produces a training set that is both wrong and, in initial evaluations, impressively accurate.

The filtered join and why it is still wrong

Consider a customer table with a segment column that is overwritten in place when the customer’s segment changes, and a label table with an event date. Joining on the customer key and filtering on the event date returns the segment as of whenever the label was created, not as of the event. Since the label’s creation usually follows the event, the join returns a value that postdates the event, sometimes by days.

The obvious fix is to join a history table with valid-from and valid-to timestamps and take the row whose interval contains the event date:

SELECT e.entity_id, e.event_ts, h.segment
FROM events e
JOIN segment_history h
  ON h.entity_id = e.entity_id
 AND e.event_ts >= h.valid_from
 AND e.event_ts <  COALESCE(h.valid_to, 'infinity'::timestamptz);

This is the as-of join, and its correctness depends entirely on the history table being an append-only log of what was believed at each moment, not a log of what is true about each moment. Those differ. A correction entered on Friday for a segment change that took place on Monday is, in a bitemporal table, a record with valid-from Monday and transaction-time Friday. A model trained on the table’s contents today will use Friday’s correction for an event on Wednesday, which is a leak of a day, and the same model deployed on Wednesday could not have. The gap between valid time and transaction time is the reason feature stores maintain both, and the reason an evaluation that reconstructs history from a table lacking transaction time is only as good as the corrections never made.

The windowed aggregate and its boundary

Most features are aggregates over a trailing window: the count of transactions in the last 30 days, the mean of a sensor’s readings in the last 6 hours, the number of distinct devices seen in the last 7 days. For an event at time t, the window is defined by a half-open interval, usually from t - W exclusive to t inclusive. The choice of which end is closed matters at the boundary and is usually invisible until an event lands exactly on it.

The event at the boundary time is the event being labelled in some problems and a distinct observation in others. For a model scoring a purchase, the purchase itself must not be in the count of previous purchases; the window must end strictly before the event. For a model scoring a sensor reading, the reading at the event time is often the input, and the window includes it. Encoding the wrong convention produces a feature that is off by one event on every row where the event is dense, which is a systematic shift rather than noise. The convention belongs in the feature definition, not in the query’s implementation.

The same reasoning applies to the lag between event time and processing time. A transaction’s timestamp is set by the terminal; its arrival in the warehouse is later. A feature computed over arrivals rather than event times sees a window that is shifted by the typical latency and truncated by the maximum latency. For a system where processing delay is minutes this is a small error; for one where records arrive days late, the reconstructed window silently omits the tail of events that happened before the event time but arrived after the label was created. Excluding the tail is a leak in the reverse direction: the feature at scoring time, computed with the same arrival discipline, will include events that the training feature omitted, so the distribution differs between training and serving in a way that no validation would reveal.

Retroactive label changes and the rebuild

Labels are not immutable either. A fraud determination is made at authorisation, revised on review weeks later, and possibly reversed. A training set built from today’s label table assigns the final determination to an event whose features were computed before the review existed. For a model trained to predict the final determination, this is correct, provided the features are computed at a point before the resolution: the point-in-time join still excludes the review, and the model learns what was knowable. For a model that must act at authorisation time, the target itself is a future event, and the honest training set contains only the events whose resolutions had occurred at the time the model would have been trained.

import heapq

def as_of_join(events, history, key_ts):
    """events: [(entity, ts)], history: [(entity, valid_from, value)] sorted per entity."""
    events = sorted(events, key=key_ts)
    by_entity = {}
    for entity, valid_from, value in history:
        by_entity.setdefault(entity, []).append((valid_from, value))
    for rows in by_entity.values():
        rows.sort()
    pointers, out = {}, []
    for entity, ts in events:
        rows = by_entity.get(entity, [])
        i = pointers.get(entity, 0)
        while i < len(rows) and rows[i][0] <= ts:   # strictly <=: interval closed on the left
            i += 1
        pointers[entity] = i
        out.append((entity, ts, rows[i - 1][1] if i else None))
    return out

The merge is linear in the combined size of the two inputs once both are sorted by entity and time, which is why as-of joins are cheap on a storage engine that can return rows in that order. The subtlety in the code is the pointer per entity: an as-of join cannot be a single merge over a global sort, because the two inputs interleave differently for each entity.

Backfills destroy the reconstruction

Training sets are rebuilt when a feature definition changes, and the rebuild recomputes the feature over historical data. If the pipeline reads from source tables that only hold current values, the rebuild overwrites the history with today’s state, and every prior training set diverges from the new one beyond the intended change. The damage is not the change itself; it is that the change cannot be isolated, so the effect on performance cannot be attributed.

The structural requirement is that the source of every feature is append-only and timestamped with both valid time and the time the value was recorded. Where the source is a mutable operational database, change data capture is the usual mechanism: a log of every insert, update, and delete with the database’s transaction commit time, from which an as-of view can be reconstructed. Without it, the practical alternative is to snapshot the source at each training-set build and never rebuild across a snapshot boundary, which bounds the accuracy of any backfill that reaches before the oldest snapshot.

A related failure appears when a feature’s definition depends on a global statistic, such as a category’s mean over all rows, recomputed nightly. Rebuilding the history of such a feature requires the statistic as it stood each night, not as it stands now. Storing only the current value makes the historical feature unreconstructable, and computing it from the full table leaks the future into the past. The fold discipline for label-dependent statistics is the same idea applied to time rather than to folds, and it is treated in out-of-fold target encoding.

Training-serving skew is the same problem twice

A feature computed by a batch job over the warehouse and a feature computed by an online service from a key-value store are two implementations of one definition. They diverge for mundane reasons: a timezone convention, a rounding rule, a different source table. The symptom is a model that validates well and scores badly, with a feature distribution that differs from the training distribution in ways that are hard to localise because each feature’s divergence is small.

Three controls bound the problem. The definition is expressed once and executed by both paths, which is the premise of a feature store: the same transformation logic, one evaluated over historical partitions and one over recent events. A sample of live requests is persisted with the features that were actually served, and that sample becomes the evaluation set, so validation happens on the distribution the model sees rather than on a reconstruction of it. And a monitoring check compares, per feature, the distribution of the served sample against the training distribution, which catches a divergence when it appears rather than after a decision has been made.

None of these remove the need for the point-in-time join during training. They remove the excuse for not having one: if the served sample exists and is large enough to train on, the features in it are correct by construction, and the historical set becomes a way of getting more data rather than the only source. Where it is the only source, the as-of join with a bitemporal source is the minimum standard, and the gap between valid time and transaction time is the size of the error that remains.

The other controls from earlier stages still apply. A split by time is a grouped split, with the same requirement that no entity straddles the boundary, described in group and duplicate leakage, and the gap between training and validation windows must cover the maximum aggregation window so that the boundary rows do not share a trailing history. A pipeline that reconstructs features correctly at the row level and splits them carelessly at the window level has done the expensive half of the work and none of the cheap half.