Direct Answer
Point-in-time (PIT) storage is a database design that records not just what a data value is, but when that value was first known to the system. Every record has two timestamps: the event time (when the market event occurred) and the known-as-of time (when your system received or recorded the data). This is called a bitemporal data model. When replaying history for a backtest, the simulation queries data using the known-as-of time as the filter: on simulation date D, the backtest sees only records whose known-as-of time is ≤ D.
Without point-in-time storage, a backtest run today on a database that has been updated with corrections, late-arriving data, and revised adjustment factors will access information that was not available at the historical decision dates. This is lookahead bias from the data layer, distinct from lookahead bias in the signal logic. The result is backtests that outperform live trading not because of strategy flaws but because the backtest accessed a more complete, corrected view of the world than the live system had at the time.
Key Takeaways
- Two timestamps, not one: Event time captures when a market event occurred. Known-as-of time captures when your system first knew about it. Both are necessary to support point-in-time queries.
- Corrections are appended, not overwritten: In a PIT database, a corrected value does not replace the original. It is added as a new record with a later known-as-of timestamp. Both versions coexist, enabling the query "what was the value known at time T?" to return the original, pre-correction value for historical simulation dates before the correction was received.
- Corporate action adjustments must be PIT: If a dividend adjustment factor is published on November 1 for an October 20 event, the adjustment was not known until November 1. A PIT backtest running October 25 data should not include the November 1 adjustment; a non-PIT backtest will include it and see "adjusted" prices that no one saw in real time on October 25.
- The bitemporal model applies to all reference data: Security master (symbol changes), index composition (index member additions/removals), analyst estimates (revised after the initial publication), and corporate action calendars (announced dates, effective dates) all have PIT characteristics that require bitemporal storage for correct historical simulation.
- Vendor data delivery lags are a form of known-as-of offset: Many fundamental data vendors deliver quarterly earnings data 1-3 days after the actual earnings release. If the data is stamped with the event date (earnings release date) rather than the delivery date, a backtest that queries by event date will see the data before it was actually delivered, another source of lookahead.
- PIT storage increases database complexity and size: Storing multiple versions of records with timestamps requires more storage, more complex queries, and more careful testing. The tradeoff is valid backtest results that cannot be obtained from a simpler overwrite-based database.
- Replay must enforce the PIT constraint at every data access: A backtesting engine that enforces PIT for price data but not for corporate action data will still have lookahead bias from the unprotected dimension. Every data join in the backtest must be evaluated for its PIT compliance.
- Reproducibility requires pinning the data snapshot: To reproduce a specific historical backtest result, the backtesting run must reference the same snapshot of the database that was used originally. If the database has since been updated, a "reproduce this run" query that uses the current database will get different results. Backtest runs should log the data snapshot version alongside the strategy parameters.
Core Concepts
The Bitemporal Data Model
Bitemporal data modeling was formalized in database theory in the 1990s and applied to financial data warehousing throughout the 2000s. The model distinguishes two time dimensions for every fact in the database. "Valid time" (equivalent to event time in market data contexts) represents when the fact was true in the real world. "Transaction time" (equivalent to known-as-of time) represents when the database system recorded or received the fact.
In a market data context: a trade executed at 10:05:00 ET (event time) and recorded in your database at 10:05:01 ET (known-as-of time) has both timestamps stored. If a correction arrives at 10:08:00 ET, the corrected value is stored with event time 10:05:00 ET (it corrects the original event) and known-as-of time 10:08:00 ET. Now the database contains two records for the same event: the original version known-as-of 10:05:01, and the corrected version known-as-of 10:08:00.
A point-in-time query "what was the price of ticker X at event time 10:05:00, as known at simulation time 10:06:00?" returns the original value, because the correction was not known until 10:08:00. A query "what is the current best known value for the same event?" returns the corrected value. This bidirectional queryability is the core value of the bitemporal model for backtesting and performance attribution.
Implementing a bitemporal database from scratch is complex. Practical alternatives include: using SCD Type 2 (Slowly Changing Dimension Type 2) tables where each update inserts a new row and closes the prior row's effective date range; using append-only event stores where records are never modified, only appended; or using specialized temporal database extensions (Microsoft SQL Server and DB2 have native system-versioned/bitemporal table support; PostgreSQL has no built-in SQL:2011 temporal table syntax as of PostgreSQL 14 and requires extensions like `temporal_tables` or trigger-based history tracking). For price data where corrections are infrequent, SCD Type 2 is typically sufficient. For tick-level data with frequent corrections, append-only stores with a versioned query layer are more scalable.
The index structure for PIT queries must support efficient range scans on both time dimensions. A query "give me all price records for ticker AAPL with event time in [10:00, 10:30] and known-as-of time ≤ simulation_date" requires an index on (ticker, event_time, known_as_of_time). Without this composite index, PIT queries on large historical datasets become full table scans. Partitioning by date on the known-as-of dimension and by ticker on the event dimension reduces query latency significantly for typical backtesting access patterns.
Sources of Lookahead from Non-PIT Data
Lookahead bias from the data layer is different from lookahead bias in the signal logic (e.g., using tomorrow's close in today's signal). Data-layer lookahead arises because the database contains information that was not available at the historical simulation date, even though the signal logic does not explicitly reference future data.
The most common sources in market data systems are: corrected trade prints (the cleaned historical database reflects corrections that arrived after the original print, which a live system would not have seen); revised corporate action adjustment factors (a vendor updates historical adjusted prices after discovering an error in an old adjustment factor, the new adjustment factors were unknown at the original dates); late fundamental data (quarterly earnings revised after initial estimates, if the revisions are stored with their original announcement date rather than their revision date, they appear "too early" in a PIT query); and delayed index rebalance data (an index announces additions and deletions on day D-5 but the composition change takes effect on day D, a backtest that adds the security to the simulated portfolio on the announcement date rather than the effective date uses the known-composition 5 days early).
For pure price data (OHLCV) from major exchanges, the PIT concern is relatively small, trade corrections are infrequent and minor. The PIT concern is larger for: (1) adjusted price series, where corporate action factor revisions change historical prices retroactively; (2) reference data and fundamental data, which are frequently revised; and (3) index composition data, where rebalance data is the primary lookahead source in index-following strategies.
The magnitude of the bias depends on the strategy. A daily momentum strategy that buys S&P 500 stocks may gain 0.1-0.3% annual return from using non-PIT index composition data (buying securities "announced but not yet effective"). A fundamental stock selection strategy that accesses revised earnings estimates with dates set to the original release date can gain 0.5-2.0% annual return from this non-PIT fundamental data, depending on how frequently analysts revise and how much the revisions change the signal ranking.
Historical Replay: Re-Creating the Real-Time Data Feed
Historical replay is the process of simulating the arrival of market data in the sequence and timing in which it originally appeared. Rather than loading a batch of data and computing signals over the entire dataset at once, a replay engine delivers each data event in chronological order at the time it was originally known, allowing the signal logic to run as if it were processing a live feed.
Replay is the most faithful representation of how a live strategy would have behaved. It preserves message ordering, the finite-horizon nature of live signal computation (the signal at time T sees only events before T), and the latency between data arrival and signal firing. A well-implemented replay engine can also inject synthetic events (e.g., simulated fill confirmations from a simulated broker) to model full order-execution feedback in a backtesting environment.
Implementing replay requires a PIT-correct data store plus a delivery mechanism that respects event ordering. Common implementations use: an in-memory priority queue (heap) sorted by known-as-of timestamp, pulling data in order; a message-based architecture where historical data is published to a message queue (Kafka, Redis Streams) and the strategy consumes it via the same API it would use for live data; or a time-stepping loop that advances a simulation clock and queries the PIT database for all records known between the prior and current simulation time.
The performance tradeoff in replay is speed versus fidelity. Full-fidelity replay of a year of tick data for 5,000 securities at original speeds would take a year to complete. Practical replay engines run at 10×, 1000× historical speed, compressing the time gaps between events. The signal logic runs as fast as the replay engine can deliver data, which can be thousands of times faster than real time for daily-bar strategies.
Implementing PIT for Key Data Types
For OHLCV price bars: store each bar with its bar close timestamp (event time) and the ingestion timestamp (known-as-of). For most sources, these will be close together (minutes after the bar closes for daily bars; seconds for minute bars from a real-time feed). When a vendor sends a corrected bar, store it as a new record rather than updating the original. Query with known-as-of ≤ simulation_date to get the version of the bar visible at the simulation date.
For corporate action adjustment factors: store each factor publication separately with the date the factor was received from the vendor. When computing a backtest's adjusted price series, select only adjustment factors with known-as-of ≤ simulation_date. This ensures that splits or dividends announced after the simulation date are not retroactively applied.
For index composition: store each composition change (addition or deletion) with the effective date (event time) and the announcement date (known-as-of). In a backtest, at each simulation date, query the composition that was both announced (known-as-of ≤ simulation_date) and effective (effective_date ≤ simulation_date). The two-condition filter correctly models that a security must have been both announced and effective to be tradeable in the simulated portfolio on that date.
Worked Scenario
A team runs an S&P 500 momentum strategy backtest on a standard non-PIT database and gets a Sharpe ratio of 1.8. They suspect data-layer lookahead from index composition data. They implement PIT storage for the index composition data and re-run.
- Non-PIT composition query: The original backtest loads the S&P 500 composition table and filters by effective_date ≤ simulation_date. It does not filter by announcement_date. So at simulation_date = March 1, it includes all securities effective by March 1, even those that were not announced until February 28 (the day before). This creates a one-business-day lookahead on average.
- PIT composition query: The corrected backtest adds a second filter: announcement_date ≤ simulation_date. Now the March 1 simulation sees only securities that were both announced and effective by March 1. Securities announced February 28 but effective March 1 now appear in the simulation on March 1 (not February 28).
- Performance difference: The PIT version shows a Sharpe ratio of 1.6, 0.2 lower than the non-PIT version. The 0.2 Sharpe difference represents the value of knowing index additions one day early. Index additions typically outperform in the announcement-to-effective window as index funds pre-position. The non-PIT backtest was capturing this effect by simulating trades one day before they were technically possible.
- Live validation: Running the PIT strategy live for 3 months produces a Sharpe ratio of 1.55, close to the 1.6 PIT backtest and much closer than the 1.8 non-PIT prediction. The PIT backtest correctly predicted live performance; the non-PIT backtest did not.
Measurement Framework
| Measurement | Question to Answer |
|---|---|
| PIT coverage (% of data tables) | What fraction of your database tables have known-as-of timestamps vs. only event timestamps? |
| Correction rate in PIT data (corrections/day) | How often are records corrected after initial storage, and is the correction rate captured in PIT metadata? |
| PIT vs. non-PIT Sharpe ratio difference | How much does enforcing PIT for each data dimension change backtest Sharpe? Large differences indicate significant data lookahead. |
| Announcement-to-effective lag (days, index events) | How long in advance are index changes announced? This is the maximum lookahead from non-PIT composition data. |
| Data delivery lag (event time to known-as-of, median) | How long after a market event does your database receive and record it? This bounds the lookahead from vendor delivery delays. |
| Snapshot pinning coverage (%) | What fraction of historical backtest runs are associated with a pinned data snapshot for reproducibility? |
Common Failure Modes
Treating Vendor Delivery Date as the Event Date
A data vendor delivers quarterly earnings data on days T+1 to T+3 after earnings releases. If the vendor timestamps the data with the release date T rather than the delivery date T+1 to T+3, a backtest that queries by release date will see earnings data available on the release date, before it was actually delivered to the system. This lookahead is especially harmful for fundamental data strategies that trade on earnings surprise, because the "signal" fires on the correct date but uses data that did not arrive until 1-3 days later.
The fix: always record the delivery timestamp (when the system first received the data from the vendor) as a separate field from the event timestamp. Use the delivery timestamp as the known-as-of timestamp. For fundamental data vendors that deliver with significant delays, this may push the effective signal date by 1-5 business days versus the event date, accurately modeling when the information was actually actionable.
Using a Non-PIT Database with an Adjustment-Based Signal
A moving average crossover strategy is backtested on fully adjusted prices from today's database. The adjustment factors as of today include corrections to historical factors that were applied when the vendor discovered past adjustment errors. For any date in the backtest where a correction to the adjustment factor was applied after the simulation date, the backtest sees adjusted prices that are different from what a live system would have computed at the time. If the correction moved the adjusted price by 0.5%, the 20-day moving average on the corrected series is 0.5% different from the 20-day moving average a live system would have computed, potentially changing signal direction at several crossover points.
Assuming PIT Is Necessary for All Data Dimensions Equally
Implementing PIT storage for every data dimension equally is expensive and may not be necessary. For tick-level trade prints from a major exchange, corrections are rare and small, implementing full PIT for tick data may cost 10× in storage versus the actual benefit. For fundamental data and index composition data, PIT is essential because corrections and delayed delivery have large signal impact. A risk-based approach to PIT investment: calculate the Sharpe ratio impact of each data dimension individually, and implement full PIT only for dimensions whose non-PIT bias exceeds an acceptable threshold (e.g., 0.1 Sharpe ratio units).
Not Propagating PIT Through All Signal Inputs
A strategy that correctly implements PIT for price data but uses a non-PIT index composition table will still have lookahead from the composition data. PIT compliance is only complete when every data dimension used in signal computation is stored and queried with PIT semantics. A PIT audit should enumerate every table, external data source, and derived dataset used in the backtest, and verify that each has a known-as-of timestamp that is correctly applied in the PIT filter.
Frequently Asked Questions
What is bitemporal data modeling?
Bitemporal data modeling is a database design approach that stores two time dimensions for each fact: "valid time" (when the fact was true in the real world) and "transaction time" (when the database system recorded it). This allows any fact to be queried as it was known at any past transaction time. For market data, valid time is the event time (when a trade occurred, when a price was set) and transaction time is the known-as-of time (when your system received and stored the data). ISO/IEC SQL:2011 standardized temporal table syntax; PostgreSQL does not implement this natively as of PostgreSQL 14 and requires extensions or manual trigger-based versioning to achieve the same effect.
How much does point-in-time storage increase database size?
For price bar data with infrequent corrections, the size increase is small, typically 1-5% of total storage for the correction records and version metadata. For heavily corrected data (late fundamental data, frequently revised reference data), the size increase can be 50-200% of the primary data, because multiple versions of each record are stored. The most practical approach is to use a "snapshot" model: take daily snapshots of slowly changing reference data (index composition, security master) and store each snapshot with its date. This increases storage linearly with the snapshot frequency but is simple to query and implement without a full bitemporal database engine.
What is the difference between event time and known-as-of time?
Event time (also called valid time or business time) is when a market event actually occurred in the real world, the timestamp on a trade execution, the date an earnings release was published, the effective date of a corporate action. Known-as-of time (also called transaction time or system time) is when your database system first recorded the fact. For real-time trade data, these differ by network and processing latency (milliseconds). For fundamental data delivered by a vendor, these may differ by days (the earnings were released on Monday but the vendor delivered the data to you on Wednesday).
Can I implement point-in-time storage without a bitemporal database?
Yes. The most common practical approach for quantitative research is the "daily snapshot" model: each day, take a snapshot of all reference and market data tables, storing the snapshot date alongside the data. Historical queries specify the snapshot date. This is simpler than a full bitemporal model and works well for data that changes slowly (index composition, security master, analyst estimates). For price data that may have intra-day corrections, you need a finer granularity, either hourly snapshots or append-only storage with version timestamps. The snapshot model is not as space-efficient as full bitemporal storage but is much simpler to implement and query.
How do I test whether my backtest has data-layer lookahead bias?
The most direct test is a "degradation test": run the backtest twice, once on the current database (which may have corrections and revisions) and once on a historical snapshot of the database captured at the same time the backtest claims to represent. Compare the signal values and returns at each date. If they differ, the current database contains information (corrections, revisions) that was not available at the simulation date. Large differences at known corporate action or announcement dates are the most common indicators. A simpler proxy test: re-run the backtest each month after the database updates, and track whether historical backtest results (already in the "past") change. If they do, your database is non-PIT for those data dimensions.
Is point-in-time storage necessary for daily-bar backtests?
It depends on what data the backtest uses. For a pure price-momentum strategy using only OHLCV price data with no corporate action adjustments and no reference data, PIT has minimal impact, daily price corrections are rare and their effect on daily-bar signals is usually small. For a strategy that uses adjusted prices (where adjustment factor revisions can change historical prices), index composition (where rebalance lookahead is common), or fundamental data (where vendor delivery delays are significant), PIT is necessary to avoid systematic lookahead bias. The safe answer: implement PIT for all data dimensions and verify with a degradation test, rather than assuming any particular dimension is safe to skip.
What is the SCD Type 2 approach and when is it appropriate?
Slowly Changing Dimension Type 2 (SCD Type 2) is a database design pattern where each update to a record creates a new row rather than overwriting the existing row. Each row has an effective_start_date and effective_end_date (or a current_flag). A point-in-time query uses "WHERE effective_start_date ≤ query_date AND (effective_end_date IS NULL OR effective_end_date > query_date)" to retrieve the version of the record active at the query date. SCD Type 2 is appropriate for slowly changing data (security master, index composition, corporate action calendars) where updates are infrequent. It is too storage-intensive for high-frequency data like individual trade prints.
How does historical replay differ from a standard backtest?
A standard backtest typically loads an entire dataset into memory or a dataframe, then steps through it chronologically, computing signals and simulated trades at each step. A historical replay engine delivers data events one at a time in the exact sequence they would arrive from a live feed, including out-of-order events, corrections, and the system-time delays of the original feed. Replay is slower (it cannot read ahead in the dataset) but more accurate: the signal code runs identically to how it runs in production, using the same API and processing logic. Standard backtests may use vectorized computations that would be impossible on a live feed; replay prevents this by structuring data access as a stream. Replay is the preferred approach for validating live strategies; standard backtests are faster for large-scale parameter sweeps.
How should index and universe membership be stored point-in-time?
Membership changes on announced dates, so a table of current constituents cannot answer what the universe was on a past date. Storing each membership as a row with an effective start and end, plus the date the change was known, allows both questions to be answered: which names were in the index on a date, and which were known to be in it as of a date. The distinction matters because additions are usually announced days before they take effect.
References
- Jensen, C. S., & Snodgrass, R. T. (1999). "Temporal Data Management." IEEE Transactions on Knowledge and Data Engineering, 11(1), 36-44. (Foundational bitemporal data theory)
- Johnston, T., & Weis, R. T. (2010). Managing Time in Relational Databases: How to Design, Update and Query Temporal Data. Morgan Kaufmann. (Comprehensive practical guide to SCD Type 2 and bitemporal implementations)
- Chen, A. Y., Poon, S.-H., & Hwang, T.-S. (2019). "Look-ahead Bias in Backtest Trading Strategies." Journal of Investment Strategies, 8(4). (Quantification of data-layer lookahead bias in different strategy types)
- PostgreSQL 14 CREATE TABLE documentation: PostgreSQL has no native SQL:2011 temporal table syntax; range types, exclusion constraints, and triggers are the standard workarounds
- Marcos López de Prado (2018). Advances in Financial Machine Learning, Chapter 6. Wiley. (Point-in-time feature engineering for ML-based trading)
Educational Disclaimer
Database implementation details vary by platform and version. Verify temporal table syntax and bitemporal query support with your specific database engine documentation before building production systems.