Point-in-Time Joins

Intro

At serving time, a model only ever sees a feature’s most recent value. So when training rows are reconstructed from history, each row has to carry the feature value that existed at that moment, not the value the feature holds today. A join that respects this is point-in-time correct: for a label at time T, every feature resolves to the latest value known at or before T. A join that ignores it just grabs whatever’s newest in the table, which for a label sitting in the past is a value from that label’s future.

The figures below should make this concrete: one customer, one feature (total_spend), and three prediction times sitting at different points in its history.

Tooling

The as-of rule is the same everywhere: for prediction time T, a feature resolves to the latest event with event_time <= T. Different tools expose it under different names.

• SQL warehouses. Engines that support it natively call this an ASOF JOIN (Snowflake, ClickHouse, DuckDB): match rows on the entity key, apply feature_ts <= event_ts, and keep the closest prior match.

1
2
3
4
5
6
  SELECT t.*, f.value
  FROM training_rows t
  ASOF JOIN features f
    ON t.entity_id = f.entity_id
    AND f.feature_ts <= t.event_ts
  

• pandas. merge_asof does the identical match locally on a DataFrame; direction="backward" is the as-of cutoff spelled out as a keyword.

1
2
3
4
5
6
  pd.merge_asof(
      training_rows, features,
      on="event_ts", by="entity_id",
      direction="backward",
  )
  

• Feature stores. Stores without a native ASOF primitive expose the same idea through get_historical_features(entity_df) (Feast, Tecton, Hopsworks all follow this shape), where entity_df carries one event_timestamp per label row and the store resolves every requested feature to the latest value at or before it. It’s also why a feature store’s offline and online paths share one feature definition instead of two: the online path is this same rule with event_ts pinned to now.
• No native ASOF (e.g. Delta Lake / Spark SQL). Join on the entity key with a <= filter, then collapse to one row per label with a window function:

1
2
3
4
5
6
7
8
9
10
11
12
  SELECT * EXCEPT (rn) FROM (
    SELECT t.*, f.value,
           ROW_NUMBER() OVER (
             PARTITION BY t.entity_id, t.event_ts
             ORDER BY f.feature_ts DESC
           ) AS rn
    FROM training_rows t
    JOIN features f
      ON t.entity_id = f.entity_id
     AND f.feature_ts <= t.event_ts
  ) WHERE rn = 1
  

It’s less efficient than a native ASOF JOIN: every matching entity/timestamp pair gets materialized before the window function trims it down to one row, so on large tables this needs entity_id partitioned or bucketed ahead of time to stay fast.

• Approximate join, when freshness doesn’t matter much. Instead of matching the exact latest prior event, join on a fixed date offset, e.g. feature_date = label_date - 1 day. This turns the join into a plain equi-join on a date key, so it’s cheap and needs no window function or ASOF support at all. It’s only safe when a day or two of feature staleness is a non-issue for the prediction; a feature that changes fast (an account balance, a live session state) will be visibly wrong under this rule even though the join runs fine.

Conclusion

A point-in-time join is a join where, for a label at time T, every feature resolves to the latest value known at or before T, nothing from T’s future. In every case where that rule breaks, the model hasn’t learned to predict the outcome; it’s learned to read a value that, at serving time, doesn’t exist yet. Ship it and it degrades the moment it meets a real customer, because in production the future never exists yet.

Getting the join operator right isn’t the whole job, either:

• Queryability lag. The cutoff should be when a value became queryable, not when the underlying event happened, so a support ticket logged at 11:58pm but not indexed until the next day’s batch run wasn’t actually available at 11:59pm.
• Timezones. Label and event timestamps need to be compared in the same timezone; a silent UTC/local mismatch shifts the cutoff by hours and can flip a pick near a stream’s event boundary.
• Ties. A feature event landing on the exact same timestamp as the label needs a rule decided up front for whether it counts as “known” at that instant, rather than whatever the join engine does by default with equal keys.

Use the right way to keep the future out of the training data.