Look-ahead bias in backtesting: five leaks I found in my own pipeline

Look-ahead bias in backtesting has one definition: the backtest used something on a date before anyone could have known it. The result looks better than any strategy you could have run, and nothing in the output says so.

The definition is easy. Finding it is not, because the leak is rarely in the strategy. It is in a join, a split, a loader or a price feed, written months earlier by someone (me) who was thinking about something else. These are five leaks I found in my own trading pipeline, how each one got in, and the pattern that removes them: point-in-time data and an as-of join.

What look-ahead bias is, with an example

The textbook example is the one that bit me first. A company's quarter ends on 31 December. Its results are published weeks later. If your table stores those results under 31 December and your backtest joins them to every trading day on or after that date, the model trades on profits in January that the market only learned in February.

Nothing about that join looks wrong. It is the obvious join. It is also a leak on every row.

Leak 1: fundamentals joined on the quarter end

My equity model's loader renamed quarter_end to report_date and as-of joined fundamentals on report_date <= date. The issue that found it (#125 in the repo) spells out the problem: under SEBI's listing rules, Indian companies can publish quarterly results up to 45 days after the quarter ends. So every quarterly row was available to the model up to about 45 days before it was public.

Two details made it worse:

• The table had no announcement-date column. There was no way to join on the real date even if I had wanted to.
• The table was a ClickHouse ReplacingMergeTree, which keeps only the latest version of each key. A restated quarter overwrote the original, so "what was known on date X" could not be rebuilt afterwards either.

Regulation 33 of the SEBI LODR Regulations gives listed companies 45 days from the end of a quarter to submit results, and 60 days from the end of the financial year for the audited annual results. The fix uses exactly those windows: each row's join key is now available_date = period_end + 45 (quarterly) or + 60 (annual). The lag is a required argument with no default, because a silent 0 is the bug. It is a conservative proxy: most companies file before the deadline, so the model sees some results later than it could have. That costs a little signal and never leaks.

How to avoid look-ahead bias with an as-of join

An as-of join matches each row on the left with the latest row on the right whose key is at or before it. Point it at the availability date, not the period date. In DuckDB:

import duckdb

con = duckdb.connect()
con.sql("""
CREATE TABLE spine AS SELECT * FROM (VALUES
  ('ACME', DATE '2024-01-02'),
  ('ACME', DATE '2024-02-13'),
  ('ACME', DATE '2024-02-14'),
  ('ACME', DATE '2024-03-01')
) t(symbol, date);

-- one row per quarter, keyed only by the period it describes
CREATE TABLE fundamentals AS SELECT * FROM (VALUES
  ('ACME', DATE '2023-09-30', 380.0),
  ('ACME', DATE '2023-12-31', 410.0)
) t(symbol, quarter_end, net_profit);
""")

print(con.sql("""
SELECT s.symbol, s.date,
       naive.net_profit  AS profit_on_quarter_end,   -- leaks
       honest.net_profit AS profit_when_public
FROM spine s
ASOF LEFT JOIN fundamentals naive
       ON s.symbol = naive.symbol AND s.date >= naive.quarter_end
ASOF LEFT JOIN (SELECT *, quarter_end + INTERVAL 45 DAY AS available_date
                FROM fundamentals) honest
       ON s.symbol = honest.symbol AND s.date >= honest.available_date
ORDER BY s.date
"""))

Output (DuckDB 1.5):

date        profit_on_quarter_end  profit_when_public
2024-01-02  410.0                  380.0
2024-02-13  410.0                  380.0
2024-02-14  410.0                  410.0
2024-03-01  410.0                  410.0

The naive join hands the model the December quarter on 2 January. The honest one waits until 14 February, 45 days after the quarter ended. DuckDB's ASOF JOIN needs one inequality on the ordering column, and the rest of the conditions must be equalities. Use ASOF LEFT JOIN so dates with no published quarter yet keep their row with a NULL instead of disappearing.

The same join in Polars, which is close to what my loader does now:

import polars as pl

LAG_DAYS = 45  # required, never defaulted: a silent 0 is the bug

out = spine.sort("symbol", "date").join_asof(
    fundamentals.with_columns(date=pl.col("quarter_end") + pl.duration(days=LAG_DAYS))
    .drop("quarter_end")
    .sort("symbol", "date"),
    on="date",
    by="symbol",
    strategy="backward",
)

join_asof with strategy="backward" (the default) takes the last right-hand row whose key is at or before the left key. Both frames must be sorted by the key. With by groups Polars cannot check the sort for you and warns, so sort explicitly.

Leak 2: regime labels fitted on the full history

When I rebuilt the equity model (ADR-0061), the market-regime features came out with neutral impact. The design record names the reason: the hidden Markov model's labels "have look-ahead bias (trained on full history including test period)."

This is a quieter leak than the fundamentals one. No row carries a future value. But the model that assigns "bull" or "bear" to a day in 2023 was fitted on 2024 and 2025 too, so the boundaries between states already know how the test period turned out. Any fitted step counts: regime models, scalers, clustering, even a z-score using the full-sample mean. Fit them inside each training window. How the regime model runs in production now is in market regime detection in production.

Leak 3: a validation split by alphabet

My crypto model reported validation scores of 0.70 to 0.82. Those numbers were wrong in two ways.

For versions 1 to 84 there was no time split at all. The loader returned the frame sorted by (symbol, date), and the trainer took the first 80% of rows for training. So "validation" was the last fifth of symbols alphabetically, over the same dates the model trained on. It learned what the market did on each day from one set of coins and was tested on another set over the same days.

The training stats date the change exactly. On an identical data window, the validation score went from 0.7016 to 0.2199 at version 85, the first with a real time cut-off. (The score itself was also the wrong statistic, a pooled correlation that rewards knowing each day's average move. The per-day rank correlation on tradeable pairs, the honest one, has a median of 0.081.)

A train/test split leaks whenever training rows can come from after validation rows. Random splits do it. Row-order splits on a frame that is not sorted by time do it. Split by date, and leave a gap at least as long as your prediction horizon.

Leak 4: today's live bar saved as a close

yfinance returns an in-progress bar for the current day. My pipeline used it as the source of index prices. On its first unattended run, a blocking calendar check caught a partial bar that had been published as an end-of-day close, on a date the rest of the price data had not reached yet.

A backtest reading that row would see a close before the close happened. The fix is one line: drop the current day, in the exchange's time zone, from every yfinance fetch. The lesson is broader. Any feed you call during market hours can hand you a partial row that looks exactly like a final one.

Leak 5: the table that forgets its revisions

The fifth is less a bug than a property of the storage. A ReplacingMergeTree, like an upsert into any table keyed on (symbol, period), keeps only the latest value. When a company restates a quarter, the old number is gone. A backtest over that table uses the corrected figure on dates when only the original existed.

For the newer fundamentals source, NSE's XBRL filings, the design record notes the fix in one sentence: the raw Parquet tree "keeps full revision history if point-in-time reconstruction is ever wanted." The database holds the latest view. The file archive holds every version as it arrived.

What is point-in-time data?

Point-in-time data stores what was known on each date, not what is true now. In practice that means three things on every row:

1. The period it describes (the quarter, the day).
2. The date it became known (published, downloaded, snapshotted).
3. Every version kept, never overwritten.

Then every read is an as-of read: the latest version known on or before the date you are asking about.

One design detail from my pipeline is worth copying. For a daily snapshot of a vendor's verdict on each stock (ADR-0067), the snapshot date is taken from the source table's own last-updated time, not the wall clock. A day the source did not refresh produces no new date, and the as-of read carries the previous one forward. The as-of read is per symbol, so a delisted name keeps its last known status instead of losing it. That is point-in-time data built from today forward. History before the first snapshot cannot be recovered, because nobody stored it.

For macro data the same problem shows up as revisions to published statistics. That story is in how I built a paid-grade data product on free public data. The checks that caught a related case in model outputs are in data quality checks that catch real bugs.

A look-ahead checklist

Before trusting a backtest, I now ask:

1. Does every input row carry the date it became known, separate from the period it describes?
2. Is every join to periodic data an as-of join on that availability date?
3. Is the train/validation split by time, with a gap?
4. Is every fitted step (scaler, regime model, feature selection) fitted only on training data?
5. Is the current day dropped from every live feed?
6. Are revisions kept, or does the store overwrite them?

So: how do you avoid look-ahead bias?

Store when you knew something, not only what it was, and join on that. Most leaks are not clever. They are the obvious join, written once and never looked at again. The as-of join is a few lines; the hard part is having the availability date to join on, which means keeping it from the first day you ingest.

That last point is a storage decision, and it is the part Local-First Analytics does cover: an append-only raw zone where every file is kept as delivered, stamped with when it arrived and never modified, with Parquet layers rebuilt from it. Keep that and an as-of join over your history is reproducible. The book does not cover backtesting or as-of joins themselves. Chapter 1 is free to read; the rest is on Amazon.

More in this series: survivorship bias in backtesting and adjusted close vs close.