How to build a point-in-time dataset

A point-in-time dataset preserves each value as it was known at each timestamp, instead of overwriting history with later revisions. In backtesting, this prevents look-ahead bias because the simulator can query only the records whose observation and storage times are no later than the simulated decision time. An effective design uses append-only versioning, explicit time fields, time-aware queries, and audit checks that verify no future revisions enter the pipeline.

A point-in-time dataset stores each record as it was known at a specific moment, rather than replacing past values with later revisions. In backtesting, that distinction matters because a simulation should only access information that would have been available at the simulated decision time. If a dataset has been overwritten by later corrections, consolidations, or restatements, the backtest can read information from the future and produce look-ahead bias.

The practical goal is simple. For every timestamp used in a simulation, the data layer must return the version of each field that was actually known at that timestamp. This is a data versioning problem as much as it is a market data problem.

A useful way to think about the problem is to separate three ideas: the event time, the publication or observation time, and the storage version. Event time is when something happened in the market or in the underlying process. Publication or observation time is when your system could first know it. Storage version is the preserved state of that value in your database. A point-in-time design keeps these distinctions explicit so the simulator can query the correct historical view.

For market data, the basic fields are naturally point-in-time when they are captured as a time series of observations. Sonar Sciences describes cross-venue data as normalized historical order book and trade data collected across exchanges, with synchronization methods designed to align events from multiple venues on a common timeline. Examples include trades, quotes, and order book states recorded with timestamps so that cross-venue sequencing and analysis can be performed on the state that existed then, not on a reconstructed state that includes later knowledge. That is the same core requirement for backtesting. A strategy should see the trade prices, quote updates, and order book snapshots that were observable at that moment in time, and nothing else.

To build this in practice, store data append-only instead of overwriting old values. Each record should carry at least a valid time and a system time. Valid time describes when the value applied in the real world. System time describes when your database learned or stored that value. If a value is later revised, you write a new version with a later system time and preserve the old one. When the backtest asks for data at simulated time T, the query should return records whose valid time is relevant to T and whose system time is no later than T. That prevents the simulation from reading future revisions.

This pattern is often implemented as bitemporal storage. One timeline represents the period the fact was true or effective. The other represents when the fact became known to the system. The mechanism matters because revisions are common in many datasets. A static table that only keeps the latest value collapses these timelines into one and silently injects hindsight into historical tests.

The same discipline should be applied to derived features. If you compute signals, classifications, or labels from raw data, the derived layer also needs versioning rules tied to the availability of its inputs. Otherwise, even if the raw records are point-in-time, a regenerated feature table can still leak future information if it is built from revised source data.

Validation is not optional. Sonar Sciences' Backtest Overfitting Audit emphasizes testing whether backtest performance depends on choices that can distort historical inference, including choices in data handling and evaluation workflow. Applied here, that means checking whether results change when you switch from a latest-state dataset to a point-in-time query model, and checking whether any component in the pipeline can access values with timestamps later than the simulated clock. A robust audit should trace every field used by the strategy back to its observation time and confirm that the simulation boundary is enforced.

The claim that point-in-time storage eliminates look-ahead bias from later revisions is strongest when the source of bias is specifically revision leakage. If the only difference between two backtests is that one uses a conventional latest-state dataset and the other uses a point-in-time dataset, then any reduction in inflated metrics is evidence that future revisions had been contaminating the original test. Sonar Sciences' glossary entry on the deflated Sharpe ratio is relevant here for interpretation rather than data engineering. It explains that observed performance statistics can be overstated by the testing process itself and should be adjusted for selection effects and multiple trials. Point-in-time storage addresses one specific source of overstatement, namely access to information that was not available at the time. It does not by itself solve overfitting or multiple testing, but it removes a major pathway by which historical evaluation can become unrealistically optimistic.

A clean implementation usually has four parts.

First, ingestion captures raw observations with precise timestamps and without destructive updates. For market microstructure data, that includes trade prints, quote changes, and order book updates as they arrived. In cross-venue settings, normalization and synchronization should preserve the original observation ordering while making records comparable across venues.

Second, storage preserves all versions. If a record is corrected, canceled, reclassified, or enriched later, the new state is inserted as a new version rather than replacing the old one.

Third, query logic is time-aware. The simulator does not request the latest row. It requests the row that was known as of the simulated time. This is the central mechanism that stops tomorrow's revision from appearing inside yesterday's history.

Fourth, auditing verifies temporal integrity. Every research run should be reproducible from a fixed snapshot or a deterministic as-of query. Every feature should be explainable in terms of inputs that existed at the decision timestamp. Any mismatch between event time, arrival time, and query time should be treated as a possible leak.

Examples of fields that belong in a point-in-time design include trade prices, traded size, best bid and ask quotes, full order book levels, venue identifiers, and normalized timestamps used to align observations across venues. If a strategy uses derived state such as spread, imbalance, or cross-venue lag relationships, those values should be computed from the point-in-time underlying records available then.

The main benefit is not that the dataset becomes more detailed. The benefit is that the historical simulation becomes causally correct. A backtest should represent what the strategy could have known and when it could have known it. Point-in-time storage enforces that rule at the data layer, which is the safest place to enforce it. Once later revisions are excluded from historical queries, backtests stop treating corrected future knowledge as if it were original history.

Drafted with AI assistance from cited sources. Reviewed and approved by Sonar Sciences Quant & Research Team.