Why SQL-native
The warehouse is the backtest engine.
Instead of pulling prices into a Python loop, every stage of the backtest is a versioned, tested, set-based dbt model that runs next to the data.
- Models chain adjusted prices → indicators → signals → round-trip trades → per-position daily P&L → a dense daily equity curve and scorecard (return, Sharpe, max drawdown, turnover, cost drag, and a hindsight-optimal benchmark). Strategies are config: adding one is a one-line edit, not new code.
- Limits measured, not assumed: a single-query simulator hit ClickHouse's 50,000-element AST ceiling at 18 iterations, so I re-architected it as three chained, materialized batches, with a per-year cap that fails the build loudly instead of silently truncating.
- The default run (the 50 most dollar-liquid US equities, 2015–2025, 3 strategies) builds in about 18 seconds; date filters prune a ~295M-row source read to ~16M rows for a one-year window.
Correctness by construction
Look-ahead can't compile. P&L has to add up.
The failures that usually stay invisible in backtests (future data leaking into signals, accounting drift between trades and equity) are blocked structurally and re-checked on every build.
- Look-ahead guard: forward returns live in a model nothing downstream reads, signals see only the indicator table, and a compile-time allowlist rejects forward-return columns, function calls and subqueries in strategy expressions. An independent reviewer fuzzed the gate with 16 escape payloads; every one failed.
- P&L identity test: summed daily P&L must equal summed net trade profit for every strategy and symbol, within a stated floating-point tolerance. One check pins costs, entry and exit marks, and the adjusted-price basis, at the grain where offsetting errors cannot cancel out.
- Honest accounting: a fixed capital base with an arithmetic equity curve (no phantom compounding under fixed-dollar sizing), transaction costs on both legs, and tagged exit reasons, so no position is ever left open or unpriced.
Verification & release
Checked against independent results, then shipped.
The design passed four review rounds before a line of SQL was written, and its core layers were re-derived independently, outside the warehouse, before release.
- A hand-computed toy series covering every exit path matches the production macros exactly; a real AAPL trade re-derived in Python matches field for field, with all 84 of its daily P&L rows at zero difference; the daily roll-up agrees with an independent aggregation to 3.6e-12.
- The market-data lakehouse it reads was cross-checked against the legacy screener on a live screen (P/E 0–20, market cap ≥ $100B): each path reproduced its reference SQL exactly, and all 54 symbols that differed between sources were traced to a cause, with zero unexplained.
- Promoted to production as its own weekly job, carved out of the nightly pipeline without touching anything else.