Point-in-Time Data Engineering for Quants
A backtest is only as honest as its information set. Point-in-time (PIT) engineering makes every input answer a strict question: what value was available to this strategy at timestamp t? That is different from asking what value is currently stored for the period containing t. Prices are usually close to PIT by construction; fundamentals, indexes, estimates, classifications, and alternative data often are not. A revised earnings field can turn a plausible alpha into retrospective fiction.
This is the operational form of the data biases discussed in backtesting biases and market data sources and cleaning. The solution is not a single asof join. It is a lineage-preserving system that records observation time, release time, vendor receipt time, and revision identity.
Model the two clocks
Every economically dated field needs at least two timestamps. validtime says the period to which a fact applies; availabletime says when a researcher or production system could have consumed it. A quarterly revenue figure valid for Q1 becomes tradable only at its filing or vendor delivery time.
| Field | Meaning | Example |
|---|---|---|
| entity_id | Stable economic identifier | issuer permanent ID |
| validfrom / validto | Period described by the fact | 2026-01-01 to 2026-03-31 |
| published_at | Public release time | 2026-05-04 20:15 ET |
| received_at | Time your pipeline received it | 2026-05-04 20:17 ET |
| ingested_at | Warehouse commit time | 2026-05-04 20:18 ET |
| revision_id | Immutable version | vendor payload hash |
Use receivedat for the conservative research clock when vendor latency matters. Use publishedat only if your live feed can reliably observe it at that time. Never overwrite revisions in a research table: an updated value is a new row with a new availability timestamp.
The as-of join
Feature construction selects the newest record available no later than each decision timestamp, separately for every asset. The join must also enforce the validity interval where applicable.
import pandas as pd
# observations: asset, available_at, metric, revision_id
# decisions: asset, decision_at
obs = observations.sort_values(['asset', 'available_at'])
dec = decisions.sort_values(['asset', 'decision_at'])
features = pd.merge_asof(
dec, obs,
left_on='decision_at', right_on='available_at', by='asset',
direction='backward', allow_exact_matches=False,
)
allowexactmatches=False encodes a useful default: a close-time release is not usable for a trade decided at the same close. Make execution timing explicit instead of letting a library choose the assumption. A daily factor calculated after the 16:00 auction belongs to the next executable session, not that day’s close.
Architecture: raw, canonical, snapshot
A durable layout separates concerns:
- Raw landing stores vendor files exactly as received, with checksums and source metadata.
- Canonical events normalize identifiers, timestamps, units, and revision semantics into append-only Parquet or a warehouse table.
- PIT snapshots materialize an as-of view for a defined universe and decision calendar.
- Feature artifacts record the snapshot ID, code commit, parameters, and output schema.
Partition event data by source and received_date, not just economic date. The latter makes late corrections hard to discover. Immutable object storage plus a manifest lets a failed or disputed backtest be replayed months later. This complements live deployment and monitoring: production should consume the same canonical contract as research, rather than a convenient dashboard export.
Corporate actions and universe history
PIT is more than timestamps. Securities need stable identities through ticker changes, mergers, spinoffs, delistings, and share-class conversions. Keep an effective-dated security-master mapping from permanent instrument ID to vendor ticker and exchange. Similarly, index membership is an event stream: a constituent list downloaded today cannot be applied to 2018.
A useful invariant is: a universe query at date d must return the membership published before d, while price adjustment should be driven by the corporate-action knowledge available under your chosen convention. Document whether splits are adjusted retrospectively for return continuity; that transformation is acceptable when it does not leak future tradability decisions.
Tests that catch temporal leakage
PIT quality needs tests beyond null counts. Seed a known restatement and assert that an earlier snapshot retains the old value. Test that no feature has availableat >= decisionat; verify that delisted names remain in historical universes; and compare a replayed snapshot checksum to its stored manifest. Randomly sample rows and expose lineage in a notebook or UI: source file, revision, and selected timestamp.
The most revealing test is a time-travel replay. Rebuild a past signal using only raw objects committed before a cutoff and compare it with the archived artifact. Differences should be explainable by a versioned code change, never silently by a vendor overwrite.
Key takeaways
- Treat availability time and economic validity time as separate first-class fields.
- Store revisions append-only and build features with explicit backward as-of joins.
- Version security masters, universes, data snapshots, code, and feature manifests together.
- Test temporal invariants and replay historical runs before trusting a Sharpe ratio.