Point-in-Time Data Architecture for Backtesting

Look-ahead bias is usually treated as a backtest-code problem. It’s a schema problem: a data layer that stores only current values cannot answer “what did we know on date t?”, so every consumer reinvents (and fumbles) the correction. Build point-in-time into the architecture once and the whole pipeline inherits correctness. This is the design guide.

The core pattern: two timestamps, never one

Every fact gets event time (when the fact is about: the fiscal quarter, the trade date) and knowledge time (when you could have known it: announcement, feed arrival, revision receipt) — the bitemporal pattern. Updates never overwrite; they append a new row with a later knowledge time. The invariant that makes backtests honest: a query as-of knowledge-time K sees exactly the rows with knowledge_time ≤ K — restatements, corrections, and backfills become visible history instead of silent rewrites.

-- fundamentals, bitemporal
CREATE TABLE fundamentals (
  entity_id     text,        -- survivorship-safe id, not ticker (tickers get reused)
  period        date,        -- event time: fiscal period end
  field         text,        -- 'eps', 'revenue', ...
  value         numeric,
  knowledge_ts  timestamptz, -- when this value became knowable
  source        text,
  PRIMARY KEY (entity_id, period, field, knowledge_ts)
);

-- the as-of read every backtest uses
SELECT DISTINCT ON (entity_id, period, field) *
FROM fundamentals
WHERE knowledge_ts <= :backtest_date
ORDER BY entity_id, period, field, knowledge_ts DESC;

Schema by data class — where the pattern bites

Prices: mostly single-temporal (the close is knowable at the close) — but store unadjusted prices + a corporate-actions table, because adjustment factors are knowledge-timed facts: a split announced today changes the adjusted history, and pre-adjusted storage is a rolling rewrite of the past. Adjust at query time, as-of.

Fundamentals: the full bitemporal treatment above — this is the class where restatement leakage lives. If your vendor sells only current-vintage values, knowledge_ts = first_seen_at from your own ingestion builds PIT forward from today — imperfect for history, airtight from now on.

Universe & membership: an interval table (entity_id, list, valid_from, valid_to, knowledge_ts) — the survivorship and membership-leak killer. “Universe as of t” becomes a where-clause, not a research project.

Events: event studies need the event record’s knowledge time (when the feed reported it) separate from the event’s own timestamp — database backfill is a knowledge-time fact. Same table shape as fundamentals.

Reference data (identifiers, listings, tick sizes): slowly-changing intervals, keyed by a permanent entity id. The id decision is load-bearing: join everything on entity ids, map tickers per-date — ticker reuse is a silent wrong-join generator.

Making it usable: the loader contract

PIT schemas die of inconvenience when every query needs the DISTINCT ON dance. The fix is the shared loader: one function — load(fields, universe, as_of) — that only exposes as-of reads, so leakage requires deliberately bypassing the API rather than forgetting a clause. Materialize common vintages (e.g., month-end snapshots) for speed; the bitemporal base stays the source of truth. Storage-wise this all lives happily in Postgres at fundamentals scale, with the tick layer staying in Parquet — the two-tier split from the format guide.

The migration path from a naive store

1. Freeze and archive the current-vintage store (raw zone rules).
2. Start capturing knowledge time now — first_seen_at on every ingest; PIT correctness accrues forward immediately.
3. Backfill where vintage sources exist (vendor PIT products, your own historical pulls); label backfilled knowledge times as estimated.
4. Move consumers to the as-of loader one study at a time; the diff between old and new results is itself a leakage measurement worth writing down.

Common ways PIT architectures fail

• PIT tables, non-PIT joins: the fundamentals are bitemporal but the ticker-map or universe join isn’t — one current-vintage join re-contaminates everything downstream.
• The bypass habit: ad-hoc queries against base tables “just for exploration” leak into features; make the loader the path of least resistance.
• Vendor resets: a full-history refresh from the vendor arrives all with today’s knowledge time; distinguish provider vintage from your receipt or backfills poison the axis.
• Silent snapshot drift: materialized vintages not rebuilt after late-arriving data disagree with the base — reconcile them on schedule.

Questions to ask of any data layer (yours or a vendor’s)

1. Can it answer “what did we know on t?” by construction, or by convention?
2. Are corporate actions facts-with-timestamps or baked-in adjustments?
3. What happens to a restatement — new row, or overwrite?
4. Do joins run on permanent ids?

Where this connects

This architecture is the look-ahead guide made structural, the prerequisite for honest fundamental-factor research, and the reason vendor PIT capability is worth paying for: vendors selling knowledge-timed data let you skip the hardest backfill.

The bias it kills: look-ahead & PIT → · The adjustment layer: corporate actions → · The storage substrate: Parquet vs Postgres →