h5i-db / Cookbook / Risk & production / leakage-check: which of last night's backtests actually held up?

leakage-check: which of last night's backtests actually held up?

A research agent (or a parameter sweep, or a junior with a for-loop) runs forty backtests overnight. In the morning there are forty Sharpes. Reviewing them properly means re-deriving each one against the data as it stood at the decision instant, and nobody does that forty times, so in practice the top of the list gets promoted and the rest get deleted. That is exactly the selection procedure that promotes leaks.

leakage_check turns that review into a number. It runs one query twice, against the current head and against a decision read point, and reports the delta. The part of a metric that moves is the part that depended on data which had not arrived when the trade would have been placed: the alpha that evaporates in production.

This recipe measures a real one, then spends equal time on the two ways the check can mislead you: a vacuous result, and an event-time leak it is blind to by construction. A diagnostic you trust incorrectly is worse than none.

import numpy as np
import pandas as pd
import pyarrow as pa
import h5i_db

import cookbook_utils as cu

db = h5i_db.Database(cu.fresh_db("prod_leakage"), create=True)
rng = np.random.default_rng(11)

1. A panel with an arrival history#

The point of the exercise depends on the database being a record of when things arrived, not just of what they say now. So we load the vendor's first publication, then let corrections land later as their own commits, which is what a vendor feed actually looks like once you keep the history.

symbols = [f"STK{i:03d}" for i in range(60)]
panel = cu.make_daily_prices(symbols=symbols, days=500)

schema = pa.schema(
    [
        pa.field("ts", pa.timestamp("us", tz="UTC"), nullable=False),
        pa.field("symbol", pa.string()),
        pa.field("close", pa.float64()),
    ]
)
db.create_table("prices", schema, time_column="ts", sort_key=["ts", "symbol"])

first_publication = (
    panel.select(["ts", "symbol", "close"])
    .sort_by([("ts", "ascending"), ("symbol", "ascending")])
)
db.append("prices", first_publication, note="vendor delivery, first publication")

pdf = first_publication.to_pandas()
print(f"{len(pdf):,} rows, {pdf.ts.min().date()} .. {pdf.ts.max().date()}, version 1")
output
30,000 rows, 2023-01-02 .. 2024-11-29, version 1

2. The corrections arrive#

Three restatements, each a separate commit: a fat-finger print, a missed split, and a late adjustment. Because a range replacement rewrites the whole window, the replacement frame has to carry the innocent rows of that day through unchanged; the helper below reads the window, patches it, and hands the full window back.

def restate(day: pd.Timestamp, symbol: str, factor: float, note: str) -> int:
    """Replace one trading day, correcting a single symbol's close."""
    start_us = day.value // 1_000
    end_us = (day + pd.Timedelta(days=1)).value // 1_000
    window = db.read("prices", time_start=start_us, time_end=end_us).to_pandas()
    window.loc[window.symbol == symbol, "close"] *= factor
    fixed = pa.Table.from_pandas(
        window.sort_values(["ts", "symbol"])[["ts", "symbol", "close"]],
        schema=schema,
        preserve_index=False,
    )
    plan = db.plan_replace_range("prices", start_us, end_us, data=fixed, note=note)
    return plan.apply()["sequence"]


days = pd.DatetimeIndex(sorted(pdf.ts.unique()))
# Corrections land against days inside the sample, the way real ones do.
for day, sym, factor, why in [
    (days[-40], "STK003", 0.5, "missed 2:1 split"),
    (days[-25], "STK011", 0.97, "fat-finger print, vendor corrected"),
    (days[-12], "STK007", 1.02, "late dividend adjustment"),
]:
    seq = restate(day, sym, factor, why)
    print(f"v{seq}: {why} on {day.date()} ({sym})")

for v in db.versions("prices"):
    print(f"  v{v['sequence']:>2} {v['op']:<13} {v.get('note', '')}")
output
v2: missed 2:1 split on 2024-10-07 (STK003)
v3: fat-finger print, vendor corrected on 2024-10-28 (STK011)
v4: late dividend adjustment on 2024-11-14 (STK007)
  v 0 create        
  v 1 append        vendor delivery, first publication
  v 2 replace_range missed 2:1 split
  v 3 replace_range fat-finger print, vendor corrected
  v 4 replace_range late dividend adjustment

3. The metric, written once, as a single query#

leakage_check re-runs a query, so the whole strategy has to be expressible as one. That constraint is a feature: a metric you can hand to the database is a metric you can hand to any read point, including a past one.

Below is a plain cross-sectional momentum book: 21-day momentum, demeaned each day, weighted by conviction, held one day. The signal deliberately uses prev, the close before the return being earned, so the strategy itself contains no look-ahead. Everything we are about to measure comes from the data moving, not from the code cheating.

SHARPE_SQL = """
WITH d AS (
  SELECT ts, symbol, close,
         lag(close, 1)  OVER (PARTITION BY symbol ORDER BY ts) AS prev,
         lag(close, 22) OVER (PARTITION BY symbol ORDER BY ts) AS prev22
  FROM prices
),
sig AS (
  SELECT ts,
         close / prev - 1.0   AS r,
         prev / prev22 - 1.0  AS mom
  FROM d
  WHERE prev IS NOT NULL AND prev22 IS NOT NULL
),
w AS (
  SELECT ts, r, mom - avg(mom) OVER (PARTITION BY ts) AS wt
  FROM sig
),
daily AS (
  SELECT ts, sum(wt * r) / nullif(sum(abs(wt)), 0) AS pnl
  FROM w GROUP BY ts
)
SELECT avg(pnl) / nullif(stddev(pnl), 0) * sqrt(252.0) AS sharpe FROM daily
"""

head_sharpe = db.sql(SHARPE_SQL).to_pandas().sharpe.iloc[0]
print("head Sharpe:", round(head_sharpe, 3))

# The data is a synthetic random walk with a common factor, so this number is
# noise around zero and its *level* means nothing. Everything below is about
# how much it moves when the read point changes, which is a property of the
# data's history rather than of the strategy.
output
head Sharpe: -0.845

4. The check itself#

The decision point is version 1, the state of the world when the research was done, before any correction had arrived. Everything after it is hindsight.

report = db.leakage_check(SHARPE_SQL, version=1)

print(f"leakage_detected : {report['leakage_detected']}")
print(f"vacuous          : {report['vacuous']}")
col = report["columns"][0]
print(f"head             : {col['head']:.3f}   (what the backtest reported)")
print(f"as-of v1         : {col['asof']:.3f}   (what it could actually have known)")
print(f"delta            : {col['delta']:+.3f} Sharpe")
print("\nwithheld commits:")
for w in report["withheld_versions"]:
    print(f"  {w['table']}: v{w['asof_version']} -> v{w['head_version']}")
output
leakage_detected : True
vacuous          : False
head             : -0.845   (what the backtest reported)
as-of v1         : -0.363   (what it could actually have known)
delta            : -0.482 Sharpe

withheld commits:
  prices: v1 -> v4

Read the absolute delta, in the metric's own units: this book's Sharpe moved by about half a point between what could be known and what is known now. The report also carries delta_pct, but a ratio metric sitting near zero makes that percentage explode: a Sharpe that goes from -0.36 to -0.85 is "-133%", which is arithmetically true and useless. Percentages are for metrics with a meaningful base; use the raw delta for the rest.

It is not a verdict that the strategy is broken. The restatements are genuine improvements to the data, and the corrected Sharpe is the more honest one. What the delta says is: this much of the result was not knowable at decision time, so a live run starting that day would not have traded this book. Use it as a haircut, and as a ranking key when you have forty of these and time to examine three.

print("\n".join(report["notes"]))
output
a zero delta does not prove the absence of look-ahead bias: this check only sees data-availability (arrival-axis) leakage across commits, not look-ahead inside a single snapshot (a window overrunning into future rows, a join reading later timestamps), which needs an event-time cutoff

5. Triage across a sweep#

Which is the actual workflow. Run the same check across every variant that came out of the overnight sweep and sort by exposure, not by Sharpe.

def with_lookback(n: int) -> str:
    """Same book, different momentum window. Asserted so a silent no-op in the
    substitution cannot quietly turn the sweep into three copies of one query."""
    sql = SHARPE_SQL.replace("lag(close, 22)", f"lag(close, {n})").replace("prev22", f"prev{n}")
    assert (n == 22) or (sql != SHARPE_SQL), f"lookback substitution failed for {n}"
    return sql


VARIANTS = {f"mom_{n}d": with_lookback(n) for n in (22, 66, 5)}

rows = []
for name, sql in VARIANTS.items():
    rep = db.leakage_check(sql, version=1)
    c = rep["columns"][0]
    rows.append(
        {
            "variant": name,
            "reported": c["head"],
            "knowable": c["asof"],
            "delta": c["delta"],
            "vacuous": rep["vacuous"],
        }
    )

triage = pd.DataFrame(rows).sort_values("delta", key=abs, ascending=False)
print(triage.round(3).to_string(index=False))
output
variant  reported  knowable  delta  vacuous
 mom_5d    -0.499     0.750 -1.249    False
mom_66d    -0.367     0.415 -0.782    False
mom_22d    -0.845    -0.363 -0.482    False

A gate follows directly: promote nothing whose reported edge survives only with hindsight. The threshold is a house parameter; the discipline is that it exists and is computed rather than eyeballed.

TOLERATED_SHARPE_MOVE = 0.15
promoted = triage[triage.delta.abs() <= TOLERATED_SHARPE_MOVE]
print(
    f"promoted {len(promoted)} of {len(triage)} variants "
    f"at |delta| <= {TOLERATED_SHARPE_MOVE} Sharpe"
)
if promoted.empty:
    print("  (none survive: a single missed split moves every variant in this book)")
output
promoted 0 of 3 variants at |delta| <= 0.15 Sharpe
  (none survive: a single missed split moves every variant in this book)

6. Failure mode 1: a zero that means nothing#

Here is the part that decides whether this diagnostic helps you or lulls you. leakage_check compares two read points. If both resolve to the same version, which is the case for any database loaded in a single bulk ingest (the normal cold start), then it compares identical data and the delta is forced to zero. It has measured nothing, and a zero that means nothing looks exactly like a clean bill of health.

The report says so, in vacuous. Check that field before you read the number.

bulk = h5i_db.Database(cu.fresh_db("prod_leakage_bulk"), create=True)
bulk.create_table("prices", schema, time_column="ts", sort_key=["ts", "symbol"])
bulk.append("prices", first_publication, note="ten years, one commit")

bulk_report = bulk.leakage_check(SHARPE_SQL, version=1)
print(f"leakage_detected : {bulk_report['leakage_detected']}   <- looks clean")
print(f"vacuous          : {bulk_report['vacuous']}   <- but it checked nothing")
print(f"withheld commits : {len(bulk_report['withheld_versions'])}")
print()
print(bulk_report["notes"][0])
bulk.close()
output
leakage_detected : False   <- looks clean
vacuous          : True   <- but it checked nothing
withheld commits : 0

VACUOUS: the as-of point resolved to the same version as head for every table, so both runs read identical data and the delta below is necessarily zero. This database has no arrival history to check against (typically a single bulk ingest). Ingest continuously, or reconstruct arrival history from the vendor's publication timestamps, before trusting an arrival-axis result

7. Failure mode 2: the leak it cannot see#

leakage_check measures the arrival axis: rows that exist now but had not been published yet. It is blind to the other axis: rows that were always in the table, read at a moment you should not have been reading them.

The classic version is a signal that uses the same day's close to trade that same day's close. Below, mom is built from close instead of prev, which makes the strategy trade on information it earns simultaneously. The Sharpe is absurd, the leak is total, and the check reports essentially nothing.

LEAKY_SQL = SHARPE_SQL.replace("prev / prev22 - 1.0  AS mom", "close / prev22 - 1.0 AS mom")
assert LEAKY_SQL != SHARPE_SQL, "the leaky-signal substitution did not apply"

leaky_report = db.leakage_check(LEAKY_SQL, version=1)
lc = leaky_report["columns"][0]
inflation = lc["head"] - col["head"]
print(f"leaky Sharpe     : {lc['head']:.2f}  (honest signal: {col['head']:.2f})")
print(f"inflation from the leak : {inflation:+.2f} Sharpe")
print(f"arrival delta           : {lc['delta']:+.2f} Sharpe")
print(f"vacuous                 : {leaky_report['vacuous']}")
output
leaky Sharpe     : 16.29  (honest signal: -0.85)
inflation from the leak : +17.14 Sharpe
arrival delta           : -5.62 Sharpe
vacuous                 : False

Note what the check did and did not tell you. It reports a real arrival delta for this query, because the restatements move the leaky signal too, but that delta is measuring the restatements, not the peeking. Nothing in the report separates a signal that reads its own bar from one that does not, because that is not what it measures. Whatever the delta comes out to, large or small, it is not a verdict on look-ahead, which is what the report's own notes say on every run.

The event-time axis needs a different tool: a cutoff applied to the scan itself.

In SQL you can write the bound by hand. Each step of a walk-forward evaluation is exactly this: decide at T, seeing only rows stamped at or before T. The check below is deliberately a row count rather than another Sharpe, because comparing Sharpes across the bound would conflate the cutoff with the shorter sample it produces, which is its own way of lying with numbers.

decision = days[-60]
cutoff = decision.strftime("%Y-%m-%dT%H:%M:%SZ")
bound = f"WHERE ts <= TIMESTAMP '{cutoff}'"
visible = db.sql(f"SELECT count(*) AS n FROM prices {bound}").to_pandas().n.iloc[0]
total = db.sql("SELECT count(*) AS n FROM prices").to_pandas().n.iloc[0]
print(f"decision date {decision.date()}: {visible:,} of {total:,} rows readable")
print(f"future rows hidden: {total - visible:,}")
output
decision date 2024-09-09: 26,460 of 30,000 rows readable
future rows hidden: 3,540

The trouble with writing that bound by hand is that you have to remember it every single time, in every subquery of every variant, forever, and a leaked backtest looks like a good one, so the day you forget is the day you get promoted.

The CLI makes that bound structural instead of remembered: the session is pinned and no query inside it can reach past the instant, including one that explicitly asks:

h5i-db query prod_leakage.db "<the same SQL>" \
  --decision-time 2026-05-02T00:00:00Z \
  --embargo 1d

Use both: the cutoff to stop event-time leaks happening, leakage_check to measure the arrival leaks that no cutoff can prevent, because they are caused by the world learning something after you did.

Takeaways#

db.close()