Building a Deterministic Market-Data Replay Engine for Backtesting on QuestDB
Most backtests leak the future. Here's how I built a deterministic replay engine on QuestDB — ASOF JOIN, Parquet-tier storage, and a Spring Boot strategy harness that streams a full trading day in event-time order with strict point-in-time correctness.
The first backtest I ever trusted turned out to be a liar. It showed a Sharpe north of 3, a drawdown curve that looked like a staircase going one direction, and fills so clean I should have known. The strategy bought when a signal fired and sold a few seconds later, and in the backtest it always sold at a price that existed only after the decision was made. I had joined my trades to a quote table on timestamp equality, and because my quote snapshots were sampled a hair later than my signal, every single fill borrowed a price from a moment that hadn't happened yet. The future was leaking in through a JOIN clause.
That bug cost me a weekend and a lot of embarrassment, and it taught me the thing that most people building strategy code never internalize: a backtest is a simulation of what you knew at each instant, and the hardest part is not the strategy logic, it's refusing to let the engine know anything it couldn't have known. This post is how I built a replay engine that enforces that discipline — on top of QuestDB, with a Spring Boot harness, using real captured order-book data instead of toy CSVs.
Why timestamp-equality joins lie to you
Market data does not arrive on a clock. A trade prints at 09:31:04.218734, the top-of-book quote that preceded it was set at 09:31:04.102881, and the next quote update lands at 09:31:04.251002. If you want to know the prevailing quote at the moment of the trade, you need the most recent quote at or before the trade's timestamp. Not the closest. Not the next one. The last one that already existed.
A naive JOIN ON trades.ts = quotes.ts almost never matches, so people "fix" it by rounding timestamps to the nearest second or by matching whichever quote sits closest in time, before or after. Rounding destroys ordering inside the second — exactly where equities microstructure lives. Matching the nearest quote regardless of direction quietly lets a quote that printed after the trade attach itself to that trade, so your fill prices are built from information the market hadn't produced yet. Both tricks flatter the equity curve, and you only find out how much when the strategy meets a live spread and the edge isn't there.
The operation I actually want is a temporal, backward-looking join, and in QuestDB it's spelled ASOF JOIN. Given a trade on the left, it reaches back to the latest quote whose timestamp hasn't passed the trade's timestamp — the quote that was standing when the trade printed, never a later one. That single "at or before" rule is the entire point-in-time guarantee, written as a join instead of as a page of defensive application code.
The capture pipeline underneath
Before any replay, you need data you trust. I run a live depth-capture service that ingests top-of-book (mbp-1) and trade events for a nine-name basket — AAPL, MSFT, NVDA, AMZN, TSLA, META, SPY, QQQ, and AMD — straight into QuestDB over the InfluxDB line protocol. The ingestion path writes nothing it didn't receive: no enrichment, no derived columns, no spread or mid computed at write time. It records exactly what came off the wire, with the exchange timestamp kept as the designated timestamp, plus a separate ingestion timestamp so I can measure capture latency afterward without tangling it up with event time.
On a normal session those nine names generate between 28 and 41 million rows, the bulk of it quote churn in SPY and NVDA. The reason I keep ingest_ts as its own column rather than trusting wall-clock-at-read is that it lets me answer a concrete question later: subtracting ts from ingest_ts across a full day, my median capture lag sits around 1.1 ms with a p99 near 6 ms — the socket-read-to-WAL-commit cost, nothing exotic. That number matters because it becomes the floor for the order latency I simulate.
The table that matters looks like this:
CREATE TABLE market_events (
ts TIMESTAMP, -- exchange/event time, designated
ingest_ts TIMESTAMP, -- when our capture wrote it
symbol SYMBOL CAPACITY 4096 CACHE,
event_type SYMBOL, -- 'quote' or 'trade'
bid_px DOUBLE,
bid_sz LONG,
ask_px DOUBLE,
ask_sz LONG,
trade_px DOUBLE,
trade_sz LONG,
seq LONG -- exchange sequence number
) TIMESTAMP(ts) PARTITION BY HOUR WAL;
Two choices on that table pay off repeatedly. The first is PARTITION BY HOUR. The 09:30–10:00 open is a different animal from the 12:00 lull — the opening hour alone can hold a quarter of the day's rows — and hour partitions let a replay of just the auction touch a handful of physical chunks instead of dragging the whole day off disk. I didn't take that on faith. On one AAPL session, a single-symbol trade-plus-quote scan over the 09:30–10:30 window ran in 0.6 s against PARTITION BY HOUR versus 2.9 s when I rebuilt the same table as PARTITION BY DAY and forced it to prune inside one giant daily partition. The second choice is carrying seq, the exchange sequence number: when two events land on an identical nanosecond stamp — which happens far more than you'd hope, especially during bursts — seq gives me a total order to break the tie. Determinism dies the instant two same-timestamp events can replay in different orders across two runs.
Partitions older than seven days get converted to QuestDB's Parquet tier (ALTER TABLE market_events CONVERT PARTITION TO PARQUET WHERE ts < dateadd('d', -7, now()), driven on a nightly cron). The trade is real and measurable: on my box a native QuestDB partition for a busy session sits around 2.1 GB, and the Parquet-converted version of the same partition lands near 430 MB — roughly a 5x reduction thanks to columnar compression on the heavily repeated symbol and side data. The cost shows up on first touch: a cold scan of a converted partition runs about 3–4x slower than the native one (the 0.6 s scan above becomes roughly 2.2 s cold, then warms back down once the OS page cache is primed). For replaying history I'll take that trade every day — I'm bounded by correctness, not by milliseconds. You can read more about how I think about storage and ingestion trade-offs in my projects.
Expressing point-in-time correctness in SQL
Here is the query at the heart of the whole engine. I take every trade in a session and attach the top-of-book quote that was prevailing at the instant each trade printed:
SELECT
t.ts,
t.symbol,
t.trade_px,
t.trade_sz,
q.bid_px,
q.ask_px,
q.bid_sz,
q.ask_sz,
(q.ask_px - q.bid_px) AS spread,
(q.bid_px + q.ask_px) / 2.0 AS mid
FROM (
SELECT * FROM market_events
WHERE event_type = 'trade'
AND symbol = 'AAPL'
AND ts IN '2026-10-15'
) t
ASOF JOIN (
SELECT * FROM market_events
WHERE event_type = 'quote'
AND symbol = 'AAPL'
AND ts IN '2026-10-15'
) q;
The ASOF JOIN guarantees q is the newest quote whose ts is <= t.ts, so no future quote can glue itself to a trade. The strategy I'd fooled myself with was a dumb mean-reversion scalp on AAPL: when the last trade printed more than two spreads below the trailing 30-second VWAP, buy one lot, exit after 5 seconds or a half-cent of profit, whichever came first — tested over the 2026-09 to 2026-10 sessions in the capture. Under the old ts = ts equality join (which, because my quote snapshots were stamped a touch later than trades, kept handing the exit the next quote) it showed a Sharpe around 3.1. Re-run with ASOF semantics and a fill model that pays the prevailing ask on entry and bid on exit, the same strategy printed a Sharpe of 0.4. The 3.1 was the equality join leaking the exit quote backward; the 0.4 was the strategy's actual, unglamorous edge. Painful, but honest.
One subtlety is easy to trip over: the join only behaves if both sides are already in timestamp order. QuestDB gets you that for free on a timestamp-partitioned table, and crucially it does not check the ordering — feed it unsorted input and it returns a confidently wrong answer instead of an error. Worse, dropping an ORDER BY into an ASOF subquery can strip the designated-timestamp property off the intermediate result and break the join quietly. So I keep ASOF subqueries to plain WHERE filters and never re-sort inside them.
Streaming a day in event-time order
The SQL gives me correctness within a single joined view, but a replay engine has to do more than answer one query. It has to walk the entire session forward, one event at a time, in the exact order those events occurred, and hand each one to a strategy that is only allowed to see the past. That's the Spring Boot harness.
The core abstraction is an event stream pulled from QuestDB over a cursor instead of being loaded into memory. Forty-odd million rows won't sit comfortably in heap, and — the part that matters more — the moment a full day lives in a List, some well-meaning strategy author will index into it and read ahead. A cursor only moves forward, so the lookahead temptation never even exists. The data arrives one event at a time and is gone once consumed.
@Component
public class MarketEventReplayer {
private final DataSource questDb;
public MarketEventReplayer(DataSource questDb) {
this.questDb = questDb;
}
/**
* Streams every event for a session in strict event-time order.
* seq is the tiebreaker so two events sharing a nanosecond
* timestamp always replay in the same order across runs.
*/
public void replay(LocalDate session, String symbol, EventSink sink) {
String sql = """
SELECT ts, event_type, bid_px, ask_px, bid_sz, ask_sz,
trade_px, trade_sz, seq
FROM market_events
WHERE symbol = ? AND ts IN ?
ORDER BY ts, seq
""";
try (Connection c = questDb.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, symbol);
ps.setString(2, session.toString());
ps.setFetchSize(10_000); // keep the cursor streaming
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
sink.onEvent(MarketEvent.from(rs));
}
}
} catch (SQLException e) {
throw new ReplayException("replay failed for " + symbol
+ " on " + session, e);
}
}
}
The ORDER BY ts, seq is the determinism contract. Two runs over the same partition produce byte-identical event sequences. The setFetchSize keeps the JDBC driver from trying to haul the whole result set across at once; events trickle through as the strategy consumes them.
The strategy harness that cannot see the future
A strategy, in my framework, is an object that reacts to events and emits orders. The critical design rule is that the harness never passes a strategy anything but the current event and whatever state the strategy has already accumulated from past events. There is no getNextQuote(), no lookahead buffer, no "peek" method anywhere in the interface. The absence is the feature.
public interface Strategy {
/** Called once per event, in strict event-time order. */
List<Order> onEvent(MarketEvent event, BookState book);
}
public final class BookState {
private double bidPx, askPx;
private long bidSz, askSz;
private long asOfNanos; // event time of the last update applied
void applyQuote(MarketEvent e) {
this.bidPx = e.bidPx();
this.askPx = e.askPx();
this.bidSz = e.bidSz();
this.askSz = e.askSz();
this.asOfNanos = e.tsNanos();
}
public double mid() {
return (bidPx + askPx) / 2.0;
}
public double spreadBps() {
double m = mid();
return m == 0 ? 0 : (askPx - bidPx) / m * 10_000.0;
}
public long asOfNanos() {
return asOfNanos;
}
}
BookState is the only market context a strategy gets, and it only ever reflects quotes that have already been replayed. When a trade event arrives, the book still shows the quote state from before that trade, which is exactly what the strategy would have seen live. The asOfNanos field is my paranoia made visible — if a fill simulator ever tries to use a price whose timestamp is ahead of the current event, I can assert against it and blow up the run rather than quietly bank a fantasy profit.
Deterministic fills, not optimistic ones
The other place backtests lie is the fill model. Assuming you always get filled at the mid, or that your market order executes at the quote you saw rather than the quote that exists when the order actually reaches the book, manufactures returns from nothing. My fill simulator takes the opposite stance: a market buy pays the ask that is prevailing when the order lands, after a configurable latency, and it eats through available size if the order is large.
@Component
public class FillSimulator {
private final Duration orderLatency; // decision -> book arrival
public FillSimulator(ReplayProperties props) {
this.orderLatency = props.getOrderLatency(); // e.g. 900 microseconds
}
public Fill simulate(Order order, BookState bookAtArrival) {
long arrivalNanos = order.decisionNanos()
+ orderLatency.toNanos();
// The book passed in is the state as of arrivalNanos, resolved
// by the engine via ASOF semantics -- never the decision-time book.
if (bookAtArrival.asOfNanos() > arrivalNanos) {
throw new LookaheadException(
"fill used quote from the future: book asOf="
+ bookAtArrival.asOfNanos() + " arrival=" + arrivalNanos);
}
double fillPx = switch (order.side()) {
case BUY -> bookAtArrival.askPx();
case SELL -> bookAtArrival.bidPx();
};
long available = order.side() == Side.BUY
? bookAtArrival.askSz()
: bookAtArrival.bidSz();
long filledQty = Math.min(order.qty(), available);
return new Fill(order.id(), fillPx, filledQty, arrivalNanos);
}
}
The latency parameter is where a backtest earns or loses its credibility. Set it to zero and you're claiming your order teleports to the exchange, which is a claim no real system can make. I calibrate it against the capture latency I measured earlier — the gap between ingest_ts and ts in the raw table gives me a sober sense of how far behind the live world I actually operate. The LookaheadException is not decoration; it has fired during development and caught a bug where I resolved the arrival-time book with a closest-match instead of an as-of match. That exception has saved me from publishing nonsense twice. The habits here are close cousins of the ones I wrote about in transactional outbox and idempotent consumers — in both worlds, correctness means refusing to let the system act on information it shouldn't have or process the same thing twice.
Wiring it together with Spring configuration
The pieces assemble into a runnable backtest through ordinary Spring wiring. I keep the replay parameters in a typed properties class so a backtest is fully described by its configuration — same config, same data, same result, every time.
@ConfigurationProperties(prefix = "replay")
public record ReplayProperties(
LocalDate session,
List<String> symbols,
Duration orderLatency,
boolean strictLookaheadChecks) {
}
@Service
public class BacktestRunner {
private final MarketEventReplayer replayer;
private final FillSimulator fills;
private final ReplayProperties props;
public BacktestRunner(MarketEventReplayer replayer,
FillSimulator fills,
ReplayProperties props) {
this.replayer = replayer;
this.fills = fills;
this.props = props;
}
public BacktestResult run(Strategy strategy) {
LedgerSink ledger = new LedgerSink(fills, strategy);
for (String symbol : props.symbols()) {
replayer.replay(props.session(), symbol, ledger);
}
return ledger.settle();
}
}
Because BacktestResult is a pure function of ReplayProperties plus the strategy code plus the immutable QuestDB partition, two runs are reproducible to the cent. When a colleague says "I can't reproduce your numbers," the first thing I check is whether they're pointed at a partition that got rewritten — which is why I treat replayed partitions as append-only and never backfill a session after it has been used in a published result.
What the production reality actually costs
A few things I did not expect when I started. First, the single biggest performance lever was not JVM tuning, it was QuestDB partitioning granularity combined with fetch size — getting the cursor to stream rather than buffer turned a nine-minute replay into forty seconds. Second, SYMBOL columns in QuestDB are interned and cheap to filter, so per-symbol replay scans are far faster than the generic string-column intuition suggests. Third, the Parquet tier is genuinely fine for backtesting even though it is slower per scan — I am not latency-sensitive when replaying history, I am correctness-sensitive, and correctness is a property of the data and the joins, not the storage tier.
The discipline that matters most has nothing to do with any of the tooling. It is the willingness to make your engine throw an exception the instant it touches information from the future, and to keep that check on in production rather than switching it off because it's inconvenient. Every optimistic assumption you let slide — fills at the mid, zero latency, closest-quote joins — is a small loan against reality that gets called in the moment you trade live. I would rather have a backtest that reports a boring 0.4 Sharpe I can trust than a glamorous 3 that evaporates on contact. If you want to talk through the capture side or the strategy harness, reach me through my contact page, and there's more on the systems I've built across trading and fintech in my experience.
Go instrument your fill model with a lookahead assertion this week, point it at one real session, and watch what your "winning" strategy does when it can no longer see tomorrow's prices.