Your Backtest Can Pass Every Unit Test and Still Know the Future

A two-timestamp schema and a future-corruption test can catch silent look-ahead leakage in financial feature pipelines.

A backtest does not need an obvious bug to cheat.

The feature calculations can be correct. The train/test split can be chronological. Every unit test can pass. Yet the model may still be reading financial data that was revised, restated, or simply unavailable when the historical decision was supposed to occur.

This is one of the nastier properties of look-ahead leakage: the pipeline can be internally consistent while the experiment is historically impossible.

The fix starts with a distinction that deserves to be explicit in every time-sensitive data model:

When was this fact true, and when could the system actually know it?

Those are different timestamps.

Event time is not knowledge time

Suppose a company reports revenue for a quarter ending June 30.

At least four dates may matter:

• period_end: the economic period the fact describes
• published_at: when the filing became publicly available
• ingested_at: when your system received and processed it
• decision_at: when the model supposedly made its prediction

A naïve feature pipeline often joins only on period_end:

SELECT
  d.symbol,
  d.decision_at,
  f.revenue
FROM decisions AS d
JOIN fundamentals AS f
  ON d.symbol = f.symbol
 AND f.period_end <= DATE(d.decision_at);

This looks chronological. It is not necessarily point-in-time correct.

A quarter can end weeks before its filing becomes public. A vendor can backfill a corrected value months later. A pipeline outage can delay ingestion. Joining only on the period being described says nothing about whether the value was available to the model.

The minimum eligibility rule is:

max(published_at, ingested_at) <= decision_at

The later of publication and ingestion becomes the earliest moment the system could legitimately use the record.

Give every fact an availability timestamp

A useful schema for versioned financial facts might look like this:

symbol
metric
period_end
value
published_at
ingested_at
source_id

Then derive:

available_at = max(published_at, ingested_at)

Do not overwrite an earlier observation when a new filing, amendment, or vendor revision arrives. Append another version.

That provides two useful properties:

1. The current application can still select the newest accepted value.
2. A historical experiment can reconstruct only the values available before its cutoff.

The SEC company-facts API illustrates why this distinction matters. Its XBRL records include reporting-period dates, values, accession numbers, forms and filing dates. The API is updated as submissions are disseminated, while the SEC also documents post-acceptance corrections and removals.

A production pipeline should capture the highest-precision dissemination timestamp available from its source. A filing date alone may be too coarse for intraday decisions.

A single “latest value” table cannot reliably reconstruct every earlier information state.

Build features with an as-of boundary

The following BigQuery-style SQL is illustrative. It selects, for each decision and metric, the latest reporting period that had become available by the decision timestamp.

It assumes published_at and ingested_at are non-null timestamps. If either may be missing, define the fallback policy explicitly instead of letting null behavior decide it silently.

WITH eligible_facts AS (
  SELECT
    d.decision_id,
    d.symbol,
    d.decision_at,
    f.metric,
    f.period_end,
    f.value,
    f.published_at,
    f.ingested_at,
    f.source_id,
    GREATEST(f.published_at, f.ingested_at) AS available_at
  FROM decisions AS d
  JOIN fundamental_fact_versions AS f
    ON d.symbol = f.symbol
  WHERE GREATEST(f.published_at, f.ingested_at) <= d.decision_at
),
ranked_facts AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY decision_id, metric
      ORDER BY period_end DESC, available_at DESC, source_id DESC
    ) AS version_rank
  FROM eligible_facts
)
SELECT * EXCEPT (version_rank)
FROM ranked_facts
WHERE version_rank = 1;

The exact ranking rule depends on the metric.

For some features, you want the newest fiscal period. For others, you need a fixed period, trailing window or exact vintage. The important invariant is not the ranking expression. It is that no candidate row crosses the information boundary.

Pandas provides merge_asof for backward-looking time joins, but the same warning applies: the join key should represent availability, not merely the date the observation describes.

Add a test that poisons the future

Timestamp filters are necessary, but they are easy to break during later refactoring.

A stronger test treats the pipeline as a black box:

1. Calculate features at a historical cutoff.
2. Corrupt every source value that became available after that cutoff.
3. Calculate the features again.
4. Assert that the output is identical.

If changing the future alters a historical feature, some path is leaking information backward.

Here is a compact Python version. It assumes the timestamp fields are timezone-aware datetime objects.

from copy import deepcopy


def available_at(row):
    return max(row["published_at"], row["ingested_at"])


def build_features(rows, symbol, decision_at):
    eligible = [
        row
        for row in rows
        if row["symbol"] == symbol
        and available_at(row) <= decision_at
    ]

    latest = {}

    for row in eligible:
        metric = row["metric"]
        rank = (
            row["period_end"],
            available_at(row),
            row["source_id"],
        )

        if metric not in latest or rank > latest[metric]["rank"]:
            latest[metric] = {
                "rank": rank,
                "value": row["value"],
            }

    return {
        metric: selected["value"]
        for metric, selected in latest.items()
    }


def assert_no_future_dependency(rows, symbol, decision_at):
    baseline = build_features(rows, symbol, decision_at)
    poisoned = deepcopy(rows)

    for row in poisoned:
        if available_at(row) > decision_at:
            row["value"] = "__FUTURE_SENTINEL__"

    repeated = build_features(poisoned, symbol, decision_at)

    assert repeated == baseline, (
        "Historical features changed after future data "
        "was corrupted. Possible look-ahead leakage."
    )

This is a form of metamorphic testing. You may not know the one perfect feature vector for every historical date, but you do know a relationship that must hold:

Data unavailable at the cutoff must not influence the result.

Run the test against intermediate transformations as well as the final model matrix. Otherwise, a leaky aggregate can be materialized upstream and appear harmless downstream.

Warehouse time travel is not enough

Database time travel is useful, but it solves a different problem.

For example, BigQuery time travel retains changed or deleted table data within a configurable two-to-seven-day window, with seven days as the default. That helps with recovery and recent table reconstruction.

It does not automatically tell you when an external fact became public, when your pipeline first ingested it, or which vendor revision a year-old model would have seen.

System-version history and domain-level availability timestamps complement each other:

• Warehouse history answers: “What did this table contain?”
• available_at answers: “What was this experiment allowed to know?”

For durable research, preserve explicit fact versions rather than depending entirely on a short infrastructure-retention window.

Where the test can still miss leakage

A passing future-corruption test is not proof that the whole backtest is clean.

It will not automatically detect:

• labels calculated with future-adjusted prices;
• a security universe reconstructed from today’s constituents;
• delisted companies missing from the dataset;
• hyperparameters selected after repeatedly inspecting the holdout;
• revised macroeconomic series stored without vintage information;
• documents whose publication timestamps are recorded too coarsely;
• an upstream vendor feed that already replaced historical values.

The test is best treated as one invariant in a broader evaluation system.

You still need time-ordered validation, immutable experiment records, versioned datasets, explicit universe construction, and a final holdout that is not repeatedly used for model selection.

The practical rule

If a feature table has only one timestamp, ask what that timestamp means.

If the answer is “the date this fact describes,” the table is probably missing the date that matters most to a backtest: when the fact became knowable.

Store both. Filter on knowledge time. Then poison the future and prove that the past does not move.

A backtest should not merely reproduce its calculations.

It should reproduce the information constraints of the decision it claims to simulate.

Sources

• SEC EDGAR Application Programming Interfaces
• SEC: Accessing EDGAR Data
• pandas merge_asof documentation
• BigQuery data retention with time travel

AI-assistance disclosure: This article was developed with AI assistance for structure, drafting and code review. The author reviewed and is responsible for the technical claims, examples, code and sources.