Point-in-Time Correct Training Data: As-Of Joins, Snapshots and Late-Arriving Records

This guide covers the joining mechanics and what to write into a licensed data request. It sits in the tabular, time-series and transactional data hub; column-level target leakage and split construction are covered elsewhere.

Define the prediction point before you look at a single column

Every row needs an explicit prediction timestamp and an allowed feature window, or no join logic can be checked. A practical leakage audit starts by mapping the prediction point, defining the target event and stating which feature window is allowed, before anyone reviews code [1]. For a churn model, the prediction point might be "first day of the billing month at 00:00 UTC"; the label might be "cancellation within 60 days"; the allowed window might be "events with event_time < prediction_ts and system_time < prediction_ts."

Write these three items into the spine table (often called the entity, label or "driver" table) as columns, not as tribal knowledge. A spine row typically holds entity keys such as account_id, a prediction_ts, the label and a label_observed_ts. Everything else is joined onto that spine.

How an as-of join works, and where it silently breaks

An as-of join attaches, for each spine row, the most recent feature value stamped at or before the spine row's timestamp. Feature stores such as Feast implement this as a point-in-time join keyed on an event timestamp in the entity dataframe, and the same pattern appears as pandas merge_asof with direction="backward", Spark range joins, or ASOF JOIN in engines that support it. The spine timestamp acts as the upper bound on which feature values may be returned.

Feature stores typically also apply a time-to-live per feature view, which bounds how far back the join looks; a TTL that is too short produces nulls rather than stale values. That is the correct failure, but buyers often misread it as missing data from the supplier. Common silent breaks:

• Inclusive vs exclusive bounds. A value stamped exactly at prediction_ts may have been written by a batch job that ran after the decision. Decide whether the bound is <= or <, and apply it to system time.
• Timezone drift. Spine in UTC and features in unlabeled local time shift every comparison by the UTC offset, so a join can leak hours of future data on every row, and the offset itself changes at daylight-saving transitions.
• Date-only stamps. A field stored as DATE (no time) for an event that happened at 23:50 will join to a prediction made at 09:00 the same day.
• Aggregates built on the whole table. A rolling 90-day spend computed once over the full history and then joined is not as-of correct, even if the join itself is.

Historical snapshots vs current-state data

Current-state extracts overwrite history, so any feature derived from a mutable field reflects today's value, not the value at prediction time. Fields such as account status, owner, segment, credit limit, plan tier, risk rating and ticket priority are routinely updated in place in CRM, ERP and billing systems. A model trained on a current-state customer table learns that "status = churned" predicts churn, which is useless.

There are four ways a supplier can preserve history, and they differ in fidelity:

Change data capture tools such as Debezium emit row-level change events from database logs and document transformations that identify which fields changed in an update [2]. Lakehouse formats offer a related path: Apache Iceberg keeps table snapshots, and tags can retain named historical snapshots for later reads [3]. If a supplier's warehouse already runs on such a format, ask whether retained snapshots or tags cover your window before asking them to build SCD history from scratch. Field-level audit histories are discussed in more depth in our guide to field-level audit trails.

Late-arriving and restated records need two clocks

Late-arriving and restated records can only be handled correctly when every row carries both event time and system (ingestion or recorded) time. Event time says when something happened in the business; system time says when the source system knew it. A refund processed on 3 March for a 14 February order, a chargeback posted weeks later, a backdated contract amendment, or a journal entry reversed after month-end close all have event times earlier than their system times.

If you join on event time alone, a training row with prediction_ts of 20 February will "see" the 3 March refund, which no production model could have seen. The fix is a bitemporal filter: include a fact only when event_time <= prediction_ts and system_time <= prediction_ts. Restatements need one more rule: keep the superseded version and the replacement as separate rows with their own system times, rather than letting the extract overwrite the original amount.

Ask specifically how the source handles:

• Reversals and credit memos: separate rows linked to the original, or an edited original?
• Soft deletes: is there a deleted_at, or does the row vanish?
• Batch posting: do bulk loads stamp system_time with load time or with the original entry time?
• Clock sources: are timestamps from the application server, the database or the client device?

For sequence-oriented work on the same kinds of records, see timestamped business event sequences.

Multi-table history: keys and time must agree across systems

Relational training data is only as-of correct if every table in the join graph carries usable time columns, not just the fact table. RelBench, a benchmark for deep learning on relational databases, uses temporal splits so models cannot use future data to predict earlier events, which assumes timestamps exist on the rows that feed each prediction [4]. When an order table has timestamps but the customer and product dimension tables are current-state, every dimension join leaks.

Two practical consequences for extracts. First, foreign keys must be stable over time: if a CRM merges duplicate accounts and rewrites account_id on historical orders, the history of the merged-away account disappears. Ask for a merge or survivorship log. Second, extracts pulled from several systems on different days need a documented extraction timestamp per table, because the system-time ceiling differs per source. Packaging guidance for linked records is in packaging linked records from multiple systems, and multi-table modeling is covered in relational database datasets for machine learning.

As-of-correct features come before temporal splits and preprocessing

Point-in-time correct features are a precondition for any temporal split, not a substitute for one. Time-series validation guidance says to train only on the past and to set a gap equal to the forecast horizon so validation mirrors production [5]; a split built on leaky features still reports inflated scores. Preprocessing has the same trap: fitting scalers, encoders or imputers on all rows before splitting leaks the test distribution, so transformations must be fit on training data only [6].

Order the pipeline as: build the spine, apply bitemporal as-of joins, compute windowed aggregates relative to each prediction_ts, split by time with a horizon gap, then fit preprocessing on the training fold. For held-out periods used as evaluation sets, see post-cutoff evaluation data.

Extract requirements to put in the data request

The cheapest point to secure history is the data request, because suppliers export what is asked for and current-state is the default. The table below is a request block you can adapt; the general structure of a request is covered in how to write a data request for suppliers.

Illustrative example: invented to show structure; it does not describe an available dataset.

Two quick tests on a sample catch most problems. Rebuild one feature both ways, from the history table and from the current-state table, and compare distributions on old spine rows; identical distributions mean the "history" is a current-state copy. Then plot recorded_ts minus event_ts: a single spike at zero often means the supplier stamped both columns with load time. For refresh cadence once history is in place, see how often AI buyers want fresh data.

Where SourceX fits for time-stamped business history

SourceX sources operational datasets from US companies on request, including support and sales histories, engineering records, and finance and legal workflows, and manages the licensing process; nothing is held in stock and a request does not guarantee a match. Buyers describe the data they need, such as SCD2 history or bitemporal timestamps, and SourceX looks for US businesses that hold it, with every release approved by the supplying company. You can describe requirements like these on the SourceX buyer page.

Each dataset is rights-reviewed for ownership and consents and delivered under a license that defines records, uses, term and delivery. Personal details such as names, emails, phones and account numbers are removed or replaced before delivery, the method is recorded and a sample is checked, though no method is perfect. That matters here because replacing account numbers must keep keys consistent across history tables, so ask for the method documentation alongside the diligence materials.

Request point-in-time correct training data

If your features depend on history that operational systems normally overwrite, write the snapshot, change-log and two-clock requirements into the request from the start. SourceX works through Find, Assess, Agree, Transact and Manage, and nothing is contracted until a supplier agrees. Describe the history you need at sourcex.si/buyers.