KM Kanav Mehta Quantitative AI Research

QuantLink AI · Backtesting

A backtester with no Python loop.

Kanav designed and built a backtesting engine expressed entirely as dbt SQL models on ClickHouse: signals, a round-trip trade simulator, a dense daily equity curve and a per-strategy scorecard, with no pandas and no simulation loop. It runs where the data lives, blocks look-ahead by construction, reconciles every dollar of P&L to its trades, and rebuilds in production every week.

Back to Projects

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.

  • Fourteen 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 Kanav re-architected it as three chained, materialized batches of at most ten round trips each, with a 30-per-year cap that fails the build loudly instead of silently truncating.
  • Default run: the 50 most dollar-liquid US equities with complete 2015–2025 history (2,766 trading days each), 3 strategies, 414,900 signal rows and 7,184 trades; the simulator chain builds in about 18 seconds, and 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 five tagged exit reasons (trailing stop, profit target, time stop, year boundary, end of data), 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 (30 = 30, 84 = 84), and all 54 symbols that differed between sources were traced to a cause, with zero unexplained.
  • Promoted to production as its own weekly job, cleanly carved out of the nightly pipeline (81 nodes moved, no others touched); the first scheduled run built all 14 tables in about 2 minutes.