Point-in-Time Correctness and Historical Retrieval

One fact can carry five clocks, and temporal correctness means picking the one the learning protocol needs. Event time is when the underlying action or measurement occurred in the domain. Valid time is the interval during which a state or attribute is considered true in the domain. Availability time is when the feature value became accessible to the prediction system. System time is when the data platform stored or revised the record. Prediction time is the cutoff that separates allowed evidence from future information.

Two of those clocks are not a local convention. They are in a published ISO/IEC standard. SQL:2011 carries valid time as application-time period tables and transaction time as system-versioned tables. Kulkarni and Michels, the standard's own editors, state the separation flatly: “For any given row, its transaction time may arbitrarily differ from its valid time.” The standard allows at most one application-time period and one system-time period per table. It uses closed-open period semantics. And it adds periods as table metadata over a pair of ordinary DATE or timestamp columns, rather than as a new period data type.

That design is implemented, not merely specified. MariaDB Server documents that “System-versioned tables store the history of all changes, not only data which is currently applicable”, and that “System-versioned tables were first introduced in the SQL:2011 standard”. You query them through the FOR SYSTEM_TIME AS OF form. When a lesson says a fact has two dates, it is describing a table a reader can go and create.

A normal equality join can be structurally correct and still temporally impossible. Three joins over the same two tables answer three different questions.

A current-state join attaches the latest known dimension row to every historical event. It is simple, and useful for present-state reporting. It also rewrites past context after later changes, and it can leak corrections and future classifications: an old transaction receives today's merchant risk tier. A valid-time as-of join attaches the dimension version valid when the event occurred. It preserves changing domain state and requires non-overlapping validity intervals. It may still use values published after the prediction — contract terms active on the purchase date were true then, but the record of them may not have existed yet. An availability-aware join attaches the latest value available before the prediction cutoff. That matches the model's historical information set, handles delayed computation and publication, and needs feature-generation timestamps or snapshots. It is the only one of the three that can establish that a risk score was completed before authorization.

The third is not a hand-rolled pattern. It is a named relational primitive with defined semantics in independent engines. DuckDB spells it ASOF JOIN. Each left row is matched against the nearest preceding right row through an inequality on the ordering column, and the name comes from the question it answers: “Give me the value of the property as of this time”. Richard Wesley, writing on the DuckDB blog in 2023, gives the property that matters when the left table is a training spine: “Because AsOf produces at most one match from the right hand side, the left side table will not grow as a result of the join.” pandas.merge_asof implements the same rule. Its API reference says “This is similar to a left-join except that we match on nearest key rather than equal keys”, and the default direction, 'backward', “selects the last row in the right DataFrame whose 'on' key is less than or equal to the left's key”. One row in, at most one row out, taken from before the cutoff. That is the whole contract.

The process should run for millions of historical prediction times without changing semantics.

Start with the prediction timestamps. The example spine is the authoritative cutoff for every row, and nothing downstream is allowed to widen it. Record the feature timestamps — event, computation, availability, and revision time, as needed. One column cannot answer both when a value was true and when it could be read. Select the eligible history by filtering to values available before the cutoff and valid for the intended entity state. Aggregate within bounded windows, computing counts, recency, and statistics that never cross the cutoff. Then test against synthetic timelines: construct examples where a future value is numerically tempting, and verify that it is excluded.

For each feature, assert that its maximum availability timestamp does not exceed the example cutoff. Test validity intervals for overlap and gaps.

Create rows with future values that are numerically extreme. If those values change the feature, the join crossed the boundary.

Compare offline feature retrieval with logged online values for the same historical requests. Differences reveal transformation skew, clock mismatch, late data, or missing source state.

Finally, review features with unusually high predictive power. Leakage often produces dramatic gains, especially when the field is something the target itself later causes.

The KDD Cup 2008 mammography data shows how loud that signal can be. Patient IDs fell into bins with very different prevalence: 36% malignant below 20,000, and 1.7% above 4,000,000. The authors then recovered two of those bins from the 117 supplied features alone, with AUCs of 0.86 and 0.75. So the identifier was not neutral. Neither were the pixels.

The dangerous feature may look plausible because it genuinely correlates with the outcome. When the correlation is impossible, the scoreboard usually says so before anyone reads the schema.

The INFORMS 2010 Data Mining Contest, run on Kaggle, asked entrants to predict whether a stock's price would rise or fall over the next 60 minutes. 894 participants from 27 countries submitted 147 solutions. About 30 groups exceeded 0.9 AUC, and the top entry scored 0.99. An hour of stock direction is not a 0.99 AUC problem. Two things had gone wrong: explanatory variables carried values from after the prediction horizon, and the target stock could be identified from public price data. The organisers ended up publishing a second, separate “Not using future information ranking”, whose winners were entirely different people. Even with the rule stated, Kaufman and colleagues record, “it was still possible to build models that rely on data from the future”, and the organisers “had to admit that verifying future information was not used was impossible”. A rule the dataset cannot enforce is not a rule.

Nothing in that story needs a competition. The same shapes sit in ordinary business tables:

A warehouse may know today that a transaction was fraudulent. The model scoring that transaction two weeks earlier did not know that outcome. Using the corrected record as a feature creates future leakage. Point-in-time correctness requires every feature value to be one the system could legitimately retrieve at the prediction timestamp. The event having happened is not enough if the value was published later. Mutable dimensions create a similar problem. Joining an old order to the customer's current segment, or to a machine's current configuration, rewrites the context of the past.

Macroeconomic data show how wide that gap can be, because the publisher measures it. A quarterly GDP figure is not one series but a sequence of vintages. “The advance estimates for a quarter are released about 1 month after the quarter ends,” write Fixler and colleagues in the Bureau of Economic Analysis's own revision study in 2024. That first published number then moves. For 1999–2022 the mean absolute revision from each of the three current quarterly vintages to the latest estimate ran 1.18 to 1.29 percentage points, for real and nominal GDP alike. From the advance to the second estimate of nominal GDP alone it was 0.47 percentage point, and from the second to the third 0.34 percentage point. A model trained on today's stored series is trained on a number nobody could have read at the time, off by roughly a percentage point. The Federal Reserve Bank of Philadelphia built an archive for exactly this reason. Croushore and Stark described it plainly in 1999: “The data set consists of vintages, or snapshots, of the major macroeconomic data available at quarterly intervals in real time”.

The fix is temporal data modeling. Preserve timestamps, validity intervals, revisions, and snapshots, so historical examples can reconstruct the information state that existed then.