h5i-db: Point-in-Time Data for Backtests

Point-in-time data is a record of what a dataset held on a past date, and in a backtest it separates a result you can defend from one that quietly read tomorrow's numbers. h5i-db is a young open-source time-series database, written in Rust with a Python API, that stores every write as a numbered version and lets any read pin an earlier one. Below is the leakage that pinning blocks, measured on real filing data, followed by a scenario you can run on a laptop.

What is point-in-time data in a backtest?

Every market fact carries two timestamps. Event time is when the thing happened. Arrival time is when it became knowable to anyone outside the firm that reported it. A quarterly holdings report describes positions held on the last day of a quarter and reaches the public weeks afterwards, so a model joining on event time alone gets handed information nobody had.

The distance is measurable. Institutional managers file Form 13F after the close of each quarter, and the panel below measures the days between the quarter a filing describes and the day it was filed.

WITH per_filing AS
(
    SELECT
        accession_number,
        any(toDate(parseDateTimeBestEffortOrNull(toString(period)))) AS period_end,
        any(toDate(filing_date))                                     AS filed_on
    FROM global_markets.stocks_13f_filings
    WHERE filing_date >= '2023-01-01'
    GROUP BY accession_number
)
SELECT
    toString(period_end)                                 AS period_end_date,
    countDistinct(accession_number)                      AS filings_count,
    round(avg(dateDiff('day', period_end, filed_on)), 1) AS avg_days_to_public
FROM per_filing
WHERE period_end IS NOT NULL
  AND filed_on >= period_end
  AND filed_on <= period_end + 400
GROUP BY period_end
HAVING filings_count >= 100
ORDER BY period_end

For the quarter ending 2026-06-30, 10688 filings arrived an average of 34.1 days after the period they cover. The panel repeats that measurement across 16 quarters. A manager can also amend a report long after filing it, so the record describing a past date keeps changing once that date has passed.

Look-ahead bias is a storage problem

Our guide to look-ahead bias in backtesting treats leakage as a discipline: lag every feature and respect publication dates. Discipline holds until someone forgets, and the failure is quiet. A leaked backtest prints a better Sharpe ratio and no error at all.

Point-in-time storage moves the guarantee down a layer. When the frame handed to a strategy comes from a read pinned to a version, a row written after that version cannot appear in it, whatever the strategy code does next. The check stops being a code review and becomes a property of the read.

Dividends show the timing gap from the other side. A cash dividend is declared first and goes ex later, and a table loaded today carries both dates for every payment, including ones that had not been announced on the date being simulated.

SELECT
    toString(toStartOfMonth(ex_dividend_date))                         AS month,
    round(avg(dateDiff('day', declaration_date, ex_dividend_date)), 1) AS avg_days_announced_ahead,
    countDistinct(ticker)                                              AS payers_count
FROM global_markets.stocks_dividends
WHERE ex_dividend_date >= toStartOfMonth(today() - 730)
  AND ex_dividend_date <  toStartOfMonth(today())
  AND declaration_date >= '1990-01-01'
  AND declaration_date <= ex_dividend_date
GROUP BY month
ORDER BY month

In the month beginning 2026-07-01, declarations landed an average of 88.2 days ahead of the ex-dividend date, and the panel covers 24 months of the same measurement. Read a modern dividend table at a simulated date inside that gap and the payment is already sitting there, weeks before the announcement existed.

Stored history gets rewritten

Late arrival is one failure mode. Restatement is the other. Corporate actions rewrite prices that have already printed: after a 4-for-1 split, every earlier price in an adjusted series is divided by four, and the series downloaded today no longer matches the tape a trader watched. Our note on split-adjusted price history works through the arithmetic. What matters here is the frequency.

SELECT
    toString(toStartOfQuarter(execution_date))    AS quarter_start_date,
    countDistinctIf(id, split_to > split_from)    AS forward_splits,
    countDistinctIf(id, split_to < split_from)    AS reverse_splits
FROM global_markets.stocks_splits
WHERE execution_date >= toStartOfQuarter(today() - 1460)
  AND execution_date <  toStartOfQuarter(today())
  AND split_from > 0
  AND split_to   > 0
GROUP BY quarter_start_date
ORDER BY quarter_start_date

In the quarter beginning 2026-04-01, 131 forward splits and 303 reverse splits took effect. Each one restates a price history that a research pipeline may already have cached. Versioned storage does not prevent the restatement. It records the new state as a new version and keeps the old one readable, which is what turns a stale result into a reproducible one.

News timestamps carry the same trap in miniature.

SELECT
    formatDateTime(toTimeZone(published_utc, 'America/New_York'), '%H:00') AS et_hour,
    countDistinct(id)                                                     AS articles
FROM global_markets.stocks_news
WHERE published_utc >= today() - 90
GROUP BY et_hour
ORDER BY et_hour

Headlines print around the clock, across all 24 hours of the New York day. The 09:00 hour carried 790 articles over the trailing 90 days, and the 20:00 hour carried 404. Stamping an evening headline onto that day's 4:00 p.m. close hands a strategy trading the close several hours of hindsight.

A point-in-time scenario you can run

Everything here stays on the Python package. The project also ships a Rust command line tool, a separate install this walkthrough does not need. The sample data is generated locally, with no download.

1. Install the pinned release: pip install 'h5i-db==0.1.6', published on 4 August 2026. It wants Python 3.9 or newer and brings pyarrow>=14. Prebuilt wheels cover Linux on x86-64 and arm64, plus Apple silicon macOS and Windows on x86-64.
2. Describe the data once, after import pyarrow as pa and import pyarrow.parquet as pq: schema = pa.schema([('ts', pa.timestamp('us', tz='UTC')), ('symbol', pa.string()), ('price', pa.float64())]).
3. Write two invented rows to a local file with pq.write_table(pa.table({'ts': [d1, d2], 'symbol': ['ACME', 'ACME'], 'price': [10.0, 10.5]}, schema=schema), 'day1.parquet'), where d1 and d2 are timezone-aware datetimes.
4. Create the database and the table, naming the time column: db = h5i_db.Database('pit.db', create=True) then db.create_table('prices', schema, time_column='ts').
5. Ingest the file under a key: db.append('prices', pq.read_table('day1.parquet'), idempotency_key='load-day1'). The call returns the commit it made.
6. Run that exact line again. The project documents that a repeat carrying the same key finds the commit it already produced and returns it with "segments_added": 0 instead of writing the rows a second time. Print db.versions('prices') on either side of the retry and watch the version list hold still.
7. Ingest a second day under idempotency_key='load-day2', then query across both: db.sql('SELECT symbol, count(*) AS n, avg(price) AS px FROM prices GROUP BY symbol').to_pandas().
8. Read the table as it stood before day two landed: db.read('prices', version=1). The same method takes as_of= and snapshot= arguments for the same job.

Step 6 is the one to sit with. A duplicated append raises no error. It leaves the table wrong from that moment on, and every run afterwards inherits the damage silently. Step 8 is the payoff: a number computed in March can be recomputed in August from the same pinned version, the property our write-up of a reproducible backtest argues for at the framework level.

What the project claims, and what we checked

The README leads with a benchmark:

over 4.5× faster than DuckDB and Polars on OHLCV+VWAP rollups over 20M rows

That figure is the project's own measurement, quoted from the h5i-db README as retrieved in August 2026. We did not run it, and nothing above depends on it.

Maturity matters more than speed here. The repository carried 29 stars at the time of writing and sits at version 0.1.6 under the Apache-2.0 licence. That combination means a small maintainer pool and an API that can still move between point releases. There is no long public record of the engine running under load. Pinning the exact version and keeping the parquet files that fed the database leaves a way back if a release changes behaviour. Keeping your own raw copy is the same habit that guards against a vendor reshaping history underneath you, the theme running through survivorship bias in stock data.

FAQ

What is point-in-time data?

Point-in-time data is a dataset stored with the timestamps at which each fact became knowable, so a query can reconstruct what was visible on any past date. A plain "latest value" table cannot do that, since it overwrites the past with today's corrected numbers.

Does versioned storage eliminate look-ahead bias?

No. Versioning fixes one class of leakage, the kind where a run reads values written after the simulated decision time. Feature construction can still leak in other ways, such as scaling a sample by statistics computed over its full history.

What does an idempotency key do during ingest?

It labels a write so a retry is recognised as the same write. A loader can then re-run after a crash without appending the same rows twice, which is the failure that leaves a table silently wrong.

Is h5i-db ready for production?

It is version 0.1.6 with 29 stars on GitHub at the time of writing, under Apache-2.0. Early software of that size carries API churn and a thin public track record, and a version pin plus your own copy of the source files are what keep an evaluation reversible.

Every panel above ships with the SQL that produced it, so open one and read how the number was counted. The same questions can be asked in plain English on the Strasmore terminal.