Operate reproducible backtests

A backtest is a claim about the past, and it is worth what the evidence behind it is worth. Six months later a reviewer wants to run it again and get the same number.

The obstacle is that a research database keeps ingesting. Late prints land, vendors restate, and the same run reads a larger table than it did. Pinning the code is not enough, because the data moved underneath it.

Reproducibility is an operational property, not a random seed. This recipe pins a market-data cut, appends late data, proves that pinned runs remain unchanged, and inspects the run manifests needed for review.

Terms used here#

term	meaning
reproducibility	a rerun reading the same bytes and producing the same numbers, months later
pin	fixing a run's inputs to an exact version or snapshot
late data	rows for a period that arrive after that period was already read
coverage gate	a check that the pinned window actually contains the data the run assumed
run manifest	the record of what a run consumed and produced, which is what review reads

New to any of these? GLOSSARY.md defines them at more length, along with every other term the cookbook uses.

Build one 240-second tape, then split it into an approved 180-second cut and a late-arriving tail. This models a research database that continues to ingest after a run is certified.

input	rows	role
`instruments`	2	Stable venue and contract metadata
initial `book_deltas`	360	Approved L2 cut
initial `trades`	180	Approved print cut
late tail	180 book rows + 60 trades	Subsequent ingestion

import datetime as dt

import pandas as pd
import pyarrow.compute as pc

import h5i_db
from h5i_db import backtest
import cookbook_utils as cu

full = cu.make_backtest_fixture(steps=240)
base = dt.datetime(2026, 6, 1, 14, 0, 0)
cutoff = base + dt.timedelta(seconds=181)

initial_book = full["book_deltas"].filter(
    pc.less(full["book_deltas"]["ts_init"], cutoff)
)
late_book = full["book_deltas"].filter(
    pc.greater_equal(full["book_deltas"]["ts_init"], cutoff)
)
initial_trades = full["trades"].filter(pc.less(full["trades"]["ts_init"], cutoff))
late_trades = full["trades"].filter(
    pc.greater_equal(full["trades"]["ts_init"], cutoff)
)

print(f"initial book: {initial_book.num_rows:,} rows")
print(f"late book: {late_book.num_rows:,} rows")
initial_book.to_pandas().tail()

output

initial book: 360 rows
late book: 120 rows

	ts_init	ts_event	instrument_id	action	side	price	size	event_index	is_last	source_vendor
355	2026-06-01 14:02:58	2026-06-01 14:02:58	RATE-CUT-YES	snapshot	sell	0.5500	110.0	178	True	cookbook-sim
356	2026-06-01 14:02:59	2026-06-01 14:02:59	RATE-CUT-YES	snapshot	buy	0.5416	120.0	179	False	cookbook-sim
357	2026-06-01 14:02:59	2026-06-01 14:02:59	RATE-CUT-YES	snapshot	sell	0.5516	120.0	179	True	cookbook-sim
358	2026-06-01 14:03:00	2026-06-01 14:03:00	RATE-CUT-YES	snapshot	buy	0.5432	80.0	180	False	cookbook-sim
359	2026-06-01 14:03:00	2026-06-01 14:03:00	RATE-CUT-YES	snapshot	sell	0.5532	80.0	180	True	cookbook-sim

Create and load the approved cut. The table schemas are identical across initial and late batches, which lets append preserve one logical history.

db = h5i_db.Database(
    cu.fresh_db("04_reproducible_backtest_operations"),
    create=True,
)
db.create_table(
    "instruments",
    full["instruments"].schema,
    time_column="ts_init",
)
db.create_table("book_deltas", initial_book.schema, time_column="ts_init")
db.create_table("trades", initial_trades.schema, time_column="ts_init")
db.append("instruments", full["instruments"], note="reference data")
db.append("book_deltas", initial_book, note="approved 180-second book cut")
db.append("trades", initial_trades, note="approved 180-second trade cut")
db.snapshot(
    "approved-cut",
    tables=["instruments", "book_deltas", "trades"],
    note="Input approved by research controls",
)
db.versions("book_deltas")

output

[{'sequence': 0,
  'op': 'create',
  'committed_at_ns': 1785448965134837533,
  'rows': 0,
  'bytes': 0,
  'segments': 0,
  'execution_mode': 'direct'},
 {'sequence': 1,
  'op': 'append',
  'committed_at_ns': 1785448965193424941,
  'rows': 360,
  'bytes': 8283,
  'segments': 1,
  'note': 'approved 180-second book cut',
  'execution_mode': 'direct'}]

The strategy opens inside the approved cut and closes in the late tail. The same signals table is used for every run, so only the market-data read point changes.

column	type	meaning
`ts`	`timestamp[ns]`	Order-intent arrival time
`instrument_id`	`string`	Contract identifier
`side`	`string`	Buy to open, sell to close
`quantity`	`float64`	Units requested
`tag`	`string`	Stable audit label

signals = backtest.signal_table(
    [
        {
            "ts": base + dt.timedelta(seconds=30),
            "instrument_id": "RATE-CUT-YES",
            "side": "buy",
            "quantity": 50.0,
            "tag": "open-approved",
        },
        {
            "ts": base + dt.timedelta(seconds=210),
            "instrument_id": "RATE-CUT-YES",
            "side": "sell",
            "quantity": 50.0,
            "tag": "close-late",
        },
    ]
)
print(f"{signals.num_rows:,} rows x {signals.num_columns} columns")
signals.to_pandas()

output

2 rows x 10 columns

	ts	instrument_id	outcome	side	quantity	kind	limit_price	time_in_force	tag	reduce_only
0	2026-06-01 14:00:30	RATE-CUT-YES	0	buy	50.0	market	NaN	NaN	open-approved	False
1	2026-06-01 14:03:30	RATE-CUT-YES	0	sell	50.0	market	NaN	NaN	close-late	False

Store strategy intent after creating the market snapshot. The run fork pins the strategy table separately, so the snapshot remains a pure market-data approval point.

backtest.create_signal_table(db)
db.append("signals", signals, note="approved strategy intent")

output

{'table': 'signals',
 'sequence': 1,
 'op': 'append',
 'rows_total': 2,
 'segments_total': 1,
 'segments_added': 1,
 'segments_deduped': 0,
 'committed_at_ns': 1785448965294975372}

Run against the approved cut before late ingestion. Only the opening signal is reached because replay ends with the pinned data.

first = backtest.run(
    db,
    "approved-before-late-data",
    starting_cash=10_000.0,
    signals="signals",
    snapshot="approved-cut",
    equity_interval_nanos=10_000_000_000,
)
first

output

{'run_id': 'approved-before-late-data',
 'fork': 'bt-approved-before-late-data',
 'digest': 'e9d3cff467107b62fbdb005189f3a5e5d289eba406d16f3e63f55245cd9e4543',
 'starting_cash': 10000.0,
 'final_cash': 9974.47,
 'realized_pnl': 0.0,
 'commissions': 0.0,
 'funding_paid': 0.0,
 'fills': 1,
 'orders': 1,
 'records_processed': 360,
 'simulated_through_ns': 1780322580000000000,
 'equity_points': 19,
 'settlement_applied': False,
 'coverage': None,
 'liquidations': 0,
 'rejected_for_margin': 0,
 'self_trades_prevented': 0,
 'calibration_samples': [],
 'set_operations': [],
 'forecasts': 0,
 'mark_points': 19,
 'expirations': [],
 'metrics': {'orders_submitted': 1,
  'orders_filled': 1,
  'orders_cancelled_unfilled': 0,
  'orders_rejected_margin': 0,
  'orders_rejected_risk': 0,
  'orders_rejected_self_trade': 0,
  'orders_rejected_naked_short': 0,
  'orders_rejected_expired': 0,
  'fills_taker': 1,
  'fills_maker': 0,
  'book_gaps': 0,
  'liquidations': 0,
  'set_operations': 0,
  'set_operations_rejected': 0,
  'instruments_expired': 0},
 'warnings': ['RATE-CUT-YES outcome 0 left unsettled: no resolution is known for this market']}

Append the late tail as normal ingestion. Existing versions and the named snapshot remain readable; no data is copied into the snapshot.

db.append("book_deltas", late_book, note="late-arriving final minute")
db.append("trades", late_trades, note="late-arriving final minute")
print(db.versions("book_deltas")[-1])

output

{'sequence': 2, 'op': 'append', 'committed_at_ns': 1785448965781930488, 'rows': 480, 'bytes': 13672, 'segments': 2, 'note': 'late-arriving final minute', 'execution_mode': 'direct'}

Re-run the approved snapshot and run once at latest. The pinned result must remain identical. The latest run can reach the closing signal and therefore represents a different research input.

pinned_again = backtest.run(
    db,
    "approved-after-late-data",
    starting_cash=10_000.0,
    signals="signals",
    snapshot="approved-cut",
    equity_interval_nanos=10_000_000_000,
)
latest = backtest.run(
    db,
    "latest-after-late-data",
    starting_cash=10_000.0,
    signals="signals",
    equity_interval_nanos=10_000_000_000,
)

comparison = pd.DataFrame(
    [
        {"run": "pinned before append", **first},
        {"run": "pinned after append", **pinned_again},
        {"run": "latest after append", **latest},
    ]
).set_index("run")
comparison[
    [
        "fills",
        "orders",
        "records_processed",
        "final_cash",
        "realized_pnl",
    ]
]

output

	fills	orders	records_processed	final_cash	realized_pnl
run
pinned before append	1	1	360	9974.47	0.00
pinned after append	1	1	360	9974.47	0.00
latest after append	2	2	480	10002.48	2.48

Assert the evidence reviewers care about. Pinned runs have identical economics and event counts. Latest intentionally differs.

stable_fields = (
    "fills",
    "orders",
    "records_processed",
    "final_cash",
    "realized_pnl",
    "commissions",
)
assert all(first[field] == pinned_again[field] for field in stable_fields)
assert latest["records_processed"] > first["records_processed"]
assert latest["fills"] > first["fills"]
print("Pinned result survived subsequent ingestion unchanged.")

output

Pinned result survived subsequent ingestion unchanged.

Coverage gates turn incomplete data into a hard failure. Here the approved cut cannot satisfy a window extending through the late tail.

try:
    backtest.run(
        db,
        "coverage-must-fail",
        starting_cash=10_000.0,
        signals="signals",
        snapshot="approved-cut",
        window=(
            base + dt.timedelta(seconds=1),
            base + dt.timedelta(seconds=240),
        ),
        minimum_coverage=0.95,
    )
except h5i_db.InvalidInputError as error:
    print(f"Rejected as intended: {error}")
else:
    raise AssertionError("the incomplete approved cut passed its coverage gate")

output

Rejected as intended: [invalid_input] coverage 74.9% of [1780322401000000000, 1780322640000000000) is below the required 95.0%; the window loaded [1780322401000000000, 1780322580000000001) and is missing 59999999999 ns

Each run fork carries a one-row manifest and detailed result tables. Keep the manifest, source snapshot name, strategy version, and configuration in the review packet. The fill table remains the execution source of truth.

audit_rows = []
for label, report in (
    ("pinned-before", first),
    ("pinned-after", pinned_again),
    ("latest", latest),
):
    run_db = db.fork(report["fork"])
    manifest = run_db.read("bt_run").to_pandas().iloc[0].to_dict()
    manifest["label"] = label
    manifest["fork"] = report["fork"]
    audit_rows.append(manifest)
    run_db.close()
audit = pd.DataFrame(audit_rows).set_index("label")
audit[
    [
        "run_id",
        "config_digest",
        "records_processed",
        "final_cash",
        "realized_pnl",
        "fork",
    ]
]

output

	run_id	config_digest	records_processed	final_cash	realized_pnl	fork
label
pinned-before	approved-before-late-data	e9d3cff467107b62fbdb005189f3a5e5d289eba406d16f...	360	9974.47	0.00	bt-approved-before-late-data
pinned-after	approved-after-late-data	12f787c49cafa9216a356ff23148b1ce5ffc74e9412420...	360	9974.47	0.00	bt-approved-after-late-data
latest	latest-after-late-data	6162db326c6a1fca96c66b6df1eabb3953f6d2df70f579...	480	10002.48	2.48	bt-latest-after-late-data

Takeaways#

Pin market data before accepting a backtest result.
Keep strategy intent versioned separately from the historical data cut.
Re-running a named snapshot is stable after later appends.
Coverage gates reject truncated windows before they produce plausible metrics.
Preserve the run fork, manifest, digest, and fills as one reviewable artifact.

db.close()