h5i-db point-in-time data for backtests

Point-in-time data na record of wetin dataset hold for one past date. For backtest, e dey separate result wey you fit defend from result wey quietly use tomorrow numbers.

h5i-db na young open-source time-series database. Dem write am for Rust and e get Python API. E dey store every write as numbered version, and e allow any read pin earlier version.

Below na the leakage wey this pinning dey block. Dem measure am with real filing data. After that, you go see one scenario wey you fit run for laptop.

Wetin be point-in-time data for backtest?

Every market fact get two timestamps. Event time na when the thing happen. Arrival time na when people outside the firm wey report am first fit know about am. Quarterly holdings report dey describe positions wey dem hold on the last day of one quarter, but e fit reach public weeks later. So, model wey join data with event time alone go receive information wey nobody get at that time.

You fit measure the gap. Institutional managers dey file Form 13F after each quarter close. The panel below dey measure the number of days between the quarter wey filing describe and the day dem file am.

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 wey end 2026-06-30, 10688 filings reach an average of 34.1 days after the period wey dem cover. The panel repeat this measurement across 16 quarters. Manager fit still amend report long after filing am. So, the record wey describe past date fit continue to change after that date don pass.

Look-ahead bias na storage problem

Our guide to look-ahead bias for backtesting dey treat leakage like discipline: lag every feature and respect publication dates. Discipline go hold until person forget, and the failure go happen quietly. Backtest wey leakage enter go print better Sharpe ratio without showing any error.

Point-in-time storage dey move the guarantee down one layer. When the frame wey strategy receive come from a read wey pin to one version, row wey dem write after that version no fit show inside am, no matter wetin the strategy code do afterward. The check no longer be code review; e become property of the read.

Dividends show the timing gap from the other side. Company declare cash dividend first, then e go enter ex-dividend later. But table wey dem load today carry both dates for every payment, including payments wey dem never announce on the date wey simulation dey model.

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

For the month wey start 2026-07-01, declarations land average of 88.2 days before the ex-dividend date, and the panel cover 24 months of the same measurement. If you read modern dividend table using simulated date inside that gap, the payment don already dey there weeks before the announcement even exist.

Stored history dey get rewritten

Late arrival na one way failure fit happen. Restatement na the other one. Corporate actions dey rewrite prices wey don already print: after four-for-one split, every earlier price for adjusted series dey divide by four, and the series wey person download today no longer match the tape wey trader watch. Our note on split-adjusted price history dey explain the arithmetic. But wetin matter here na 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

For the quarter wey start 2026-04-01, 131 forward splits and 303 reverse splits take effect. Each one dey restate price history wey research pipeline fit don already cache. Versioned storage no dey stop the restatement. E record the new state as new version and keep the old one readable. Na this dey turn stale result into result wey person fit reproduce.

News timestamps get the same trap, but for smaller scale.

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 dey print round the clock, across all 24 hours of the New York day. The 09:00 hour carry 790 articles for the last 90 days, while the 20:00 hour carry 404. If person stamp evening headline onto that day 4:00 p.m. close, e dey give strategy wey trade the close several hours of hindsight.

One point-in-time scenario wey you fit run

Everything for here dey focus on the Python package. The project still get one Rust command line tool, but na separate install and this walkthrough no need am. The sample data dey generate locally, so no download dey involved.

1. Install the pinned release: pip install 'h5i-db==0.1.6', wey dem publish on 4 August 2026. E need Python 3.9 or newer and e bring pyarrow>=14. Prebuilt wheels cover Linux for x86-64 and arm64, plus Apple silicon macOS and Windows for 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. Use pq.write_table(pa.table({'ts': [d1, d2], 'symbol': ['ACME', 'ACME'], 'price': [10.0, 10.5]}, schema=schema), 'day1.parquet') write two invented rows to one local file, where d1 and d2 be timezone-aware datetimes.
4. Create the database and table, then name 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 one key: db.append('prices', pq.read_table('day1.parquet'), idempotency_key='load-day1'). The call go return the commit wey e make.
6. Run that exact line again. The project document say repeat wey carry the same key go find the commit wey e already produce and return am with "segments_added": 0 instead of writing the rows another time. Print db.versions('prices') for both sides of the retry and watch the version list remain unchanged.
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 e be before day two land: db.read('prices', version=1). The same method take as_of= and snapshot= arguments for the same work.

Step 6 na the one wey you need pay attention to. If duplicated append happen, e no go raise error. E go leave the table wrong from that moment, and every run after that go silently inherit the damage. Step 8 na the payoff: number wey you calculate for March fit calculate again for August from the same pinned version. Na this property our write-up of a reproducible backtest dey argue for at framework level.

Wetin di project dey claim, and wetin we check

The README start with one benchmark:

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

Na di project own measurement be dat, and e come from h5i-db README as dem retrieve am for August 2026. We no run am, and nothing for wetin follow depend on am.

For here, maturity pass speed for importance. When we dey write this, repository get 29 stars and dey version 0.1.6 under Apache-2.0 licence. This combination mean say small maintainer pool dey handle am, and API still fit change between point releases. No long public record dey show how di engine dey perform under load. If you pin di exact version and keep di parquet files wey feed di database, you get way to go back if new release change di behaviour. Keeping your own raw copy na di same habit wey protect you against vendor wey fit reshape history under your nose, na di idea wey dey run through survivorship bias for stock data.

FAQ

Point-in-time data na wetin?

Point-in-time data na dataset wey store timestamp for the time each fact first become knowable. So query fit reconstruct wetin person fit see for any date wey don pass. Ordinary “latest value” table no fit do this because e dey overwrite old values with today corrected numbers.

Versioned storage dey remove look-ahead bias?

No. Versioning dey fix one type of leakage: when a run reads values wey dem write after the simulated decision time. But feature construction fit still leak for other ways. For example, scaling a sample with statistics wey dem calculate from the sample full history.

Wetin idempotency key dey do during ingest?

E label one write so system fit recognise retry as that same write. Loader fit run again after crash without adding the same rows two times. Na this failure fit make table silently wrong.

h5i-db don ready for production?

Na version 0.1.6, and e get 29 stars for GitHub as at the time of writing, under Apache-2.0. Early software of this size fit still get plenty API changes and small public track record. Version pin plus your own copy of the source files na wetin make evaluation reversible.

Every panel wey dey above come with the SQL wey produce am, so open one and read how dem count the number. You fit ask the same questions for plain English on the Strasmore terminal.