Point-in-time foundation
A universe that remembers the companies that died.
The legacy universe filter stamped today's trading status onto every historical date, silently removing ~30% of the 2021 universe from every backtest. I replaced it with one built from each security's real listing lifespan.
- Ingested an institutional fundamentals vendor's 10 core tables into ClickHouse with Dagster (107.6M-row backfill, prices from 1998, fundamentals from 1990), refreshed by a daily incremental that lands ~5.9M rows in ~14 minutes and deduplicates cleanly; added a second raw feed of 55.9M rows with prices back to 1962.
- Built a security master on permanent IDs, ticker-rename history, index-membership spells and listing intervals, resolved into a monthly membership grid where a delisted name stays present on every date it traded; 71% of the feed's 21,850 equities are since-delisted.
- Swapping research consumers onto the new universe recovered 30.4% more since-delisted common stock at 2021 anchors; fundamentals are gated on the actual filing date, keeping first-reported values and 57k+ restated periods so no feature uses a number before it was public.
Calculation layer
~180M rows rebuilt, then proven.
Prices, valuations, ratios, metrics, growth, momentum and forward labels were rebuilt on the point-in-time foundation, and each table shipped only after matching an independent oracle.
- Five calculation tables, ~180M rows: a 3.1M-row × 1,442-column fundamentals metrics table at exact parity with the prior pipeline (median difference 0.0000), 39M rows of daily valuations from a true as-of join on filing date, and 46M rows of daily OHLCV built in 17 seconds by one server-side SQL insert (price identity 0.000000).
- Every calculation is locked in by a golden before/after fixture harness and a calc-contract drift guard, so an upstream change can never silently alter a published number.
Validation
Validation that measures content, not activity.
A green job is not proof of good data. Every table is checked for what it contains and how current it is before anything downstream reads it.
- Backfill gates reconcile every ingested table to the vendor's full feed within 1% and check coverage by period type, so quarterly, annual and as-reported history all arrive intact.
- Fail-closed freshness and content checks (business-date frontier, watermark sanity, cross-feed agreement) track whether data is actually advancing, not just whether jobs ran.
Bulk lakehouse
A replayable bronze, silver, gold lakehouse.
For a bulk market-data vendor, I designed and built a lakehouse where every served number traces back to the exact bytes the provider sent.
- 20 bulk datasets on Dagster schedules: exact provider bytes kept in a content-hashed archive for replay, typed into ClickHouse bronze, then modeled to silver and gold in dbt, including a 304.5M-row silver daily-price table with 0 duplicate keys and 14 gold backtest tables.
- Two-layer rate limiting (a per-endpoint cooldown circuit breaker plus a fail-closed credit ledger in the warehouse) logged zero rate-limit errors across a week of proof runs; a restore drill rebuilt bronze from the archive with hashes verified and a matching logical digest.
- Cut serving-path query memory 2.5–3× (to ~100–140 MiB under a 1 GiB cap) by tracing peaks to per-thread read buffers; all of it built to a written data-platform standard enforced by dbt contracts and CI gates.