Point-in-time data joins: test what your backtest could know

A point-in-time join selects the version of a record that was available at the decision time. Joining a financial fact to the period it describes can expose information that had not been published. Selecting the latest revised value can leak information even when the period itself is old.

This guide is for product teams commissioning a market-data platform, research workspace or replay system. It demonstrates one timestamp-selection property using synthetic records. It does not backtest a strategy, measure returns, recommend investments or establish that a production dataset is free of bias.

The reporting period is not the knowledge time

A record can describe a quarter ending in September but become available in October. A later correction can change its value again. QuantConnect's research guide describes this distinction between the period date and when a financial statement or revision was actually available. The engineering requirement is to retain the versions rather than replace history with the newest value.

Synthetic versions of one fact for one stable entity

Entity	Period described	Available from, UTC	Value
DEMO	2026-09-30	2026-10-03 09:00	100
DEMO	2026-09-30	2026-10-07 09:00	80, revised

At noon on October 1, neither version is available. At noon on October 4, the value is 100. At noon on October 8, the revised value 80 is available. A naive latest-value join returns 80 for all three decisions. It silently uses the revision before publication in the first two cases.

Agree the time contract before choosing a database

• Event or period time: when the underlying event occurred or the reporting period ended.
• Published or available time: when the source says the specific version became available.
• Received time: when the system under review actually received it.
• Version identity: a revision identifier or documented sequence that distinguishes corrections.

The correct cutoff depends on the question. Replaying what your live system could know requires its actual receipt history as well as publication availability. Studying a hypothetical historical feed can instead use a trustworthy point-in-time archive and an explicit delivery-lag assumption. Do not label the second exercise as a replay of your actual system.

Record the timezone and timestamp precision. If two revisions have identical availability times, define the ordering from documented source semantics or reject the ambiguity. A parser's row order is not an evidence-based tie-breaker. A stable instrument or entity identifier also matters, because a current ticker is not necessarily the same entity throughout history.

A small, executable selection example

This Python example uses only the standard library. The entity and period are explicit, timestamps require timezones, and ambiguous eligible versions are rejected. It deliberately returns a missing value when no fact was yet available.

from datetime import datetime

FACTS = [
    {"entity": "DEMO", "period": "2026-09-30", "available_at": "2026-10-03T09:00:00+00:00", "value": 100},
    {"entity": "DEMO", "period": "2026-09-30", "available_at": "2026-10-07T09:00:00+00:00", "value": 80},
]


def aware_time(value):
    result = datetime.fromisoformat(value)
    if result.tzinfo is None:
        raise ValueError("Availability and decision times need explicit time zones")
    return result


def known_value(rows, entity, period, decision_at):
    cutoff = aware_time(decision_at)
    eligible = [r for r in rows if r["entity"] == entity and r["period"] == period
                and aware_time(r["available_at"]) <= cutoff]
    if not eligible:
        return None
    stamps = [aware_time(r["available_at"]) for r in eligible]
    if len(stamps) != len(set(stamps)):
        raise ValueError("Ambiguous versions need a documented sequence rule")
    return max(eligible, key=lambda r: aware_time(r["available_at"]))["value"]


for decision in ["2026-10-01T12:00:00+00:00", "2026-10-04T12:00:00+00:00", "2026-10-08T12:00:00+00:00"]:
    print(decision, known_value(FACTS, "DEMO", "2026-09-30", decision))

Running the three decisions produces the following result. This output is generated from the tested synthetic fixture used to build this article.

Latest-value selection compared with known-at-the-time selection

Decision day, noon UTC	Naive latest value	Eligible value
2026-10-01	80	Not yet available
2026-10-04	80	100
2026-10-08	80	80

This is a selection demonstration, not a complete financial data model. It does not handle every economic series, corporate action, licence condition or exchange session. It also assumes that the supplied availability timestamps are trustworthy. A correct join cannot repair fabricated or retrospectively overwritten timestamps.

How this maps to an as-of join

In a larger dataset, a backward as-of join is often useful: select the latest eligible row whose availability time is at or before the decision time, within the same entity and fact scope. The pandas merge_asof documentation explains backward, forward and nearest matching, sorting and group keys. A nearest match can select a future row, so choosing a convenient default without checking the temporal contract is unsafe.

Filter and group at the correct fact granularity. In this example that includes the entity and reporting period. Other systems may need a metric name, currency, source and release vintage. Confirm what happens at an exact timestamp, when data is stale, when an instrument changes identity and when a period has no observation.

Test the data property, not a flattering performance chart

1. Before publication: the first fact is missing, rather than backfilled from the future.
2. Before revision: the original value remains visible.
3. After revision: the new version is selected from its availability time onward.
4. Future append: adding a later revision must not change an earlier decision's selected value.
5. Entity isolation: another entity's data must never fill a missing record.
6. Ambiguity and timezone: reject records whose ordering cannot be established instead of silently guessing.

These checks are useful even when the platform has no trading engine. A dashboard, alert or AI-generated research summary can also expose a future revision as if it were known earlier. If the system uses a language model with knowledge acquired after the simulated cutoff, a point-in-time data join alone does not establish that the generated reasoning is historically valid.

What to request from a trading or market-data platform developer

Begin with the decision the user needs to make and a versioned data contract. Identify licensed sources, historical coverage, publication and receipt timestamps, revision handling, missingness and permitted redistribution. Ask for the fixture and replay evidence before treating a strategy chart as a platform-quality test.

Separate read-only research, paper execution and live order execution. Each has different operating, approval and risk requirements. Do not assume that possessing an API key grants a redistribution licence or that a historically plausible simulation establishes executable fills.

Our trading and market-data platform development service starts with that bounded discovery and data-replay work. For a more general research or operational application, see custom software and web application engineering. The code here demonstrates one method and is not presented as an independently verified customer outcome.

Questions teams ask

Does an as-of join eliminate all look-ahead bias?

No. It can enforce one timestamp-selection rule when its keys and input timestamps are correct. Dataset revisions, universe membership, feature construction, model training and execution assumptions can introduce other sources of leakage.

Can we use a fixed reporting lag instead?

A declared lag can be a sensitivity assumption when actual historical availability is unavailable. It does not reconstruct each release or correction. Record that limitation and test plausible alternatives rather than claiming true point-in-time coverage.

Does the example prove a profitable trading strategy?

No. It selects synthetic fact versions and tests temporal consistency. It runs no strategy, places no order and makes no performance or investment claim.