Half the time someone shows me a backtest that looks too good, the strategy is fine and the data is broken. A single candle with a high below its own open, a duplicate timestamp that doubled a bar, a wick that only printed on one exchange because someone fat-fingered a market sell into a thin book. The strategy caught the artifact, not the market. So before I test anything I run the same boring set of checks on the raw candles, and I have started treating that step as more important than picking the strategy, because a clean loss teaches you something and a dirty win teaches you nothing.
OHLCV means open, high, low, close, volume, one row per time interval. It looks simple, which is exactly why people trust it without looking. The problems are rarely dramatic. They are one bad row in fifty thousand, and that one row lands right on the day your equity curve does something impossible.
The five things that are usually wrong
Almost every issue I find falls into one of a few buckets, and each has a query you can run in about a minute.
- Gaps. Missing intervals. On a 1 hour series you expect a bar every hour, and when one is absent your indicators quietly reach back further than you think. Generate the full expected timestamp range at your interval, left join your data onto it, and look for the nulls. Crypto trades 24/7 so any gap is suspect. Equities do not, so you have to gap out weekends and holidays first or you will chase ghosts.
- Duplicate timestamps. Two rows claiming the same minute, usually from a bad merge or a paginated API that overlapped its pages. A
GROUP BY timestamp HAVING COUNT(*) > 1finds them instantly. This one is nasty because a naive backtester might sum the volume or take whichever row it saw last, and either way your bar is now fiction. - High-low violations. The high should be the max of open, high, low, close and the low should be the min. If
high < low, orhigh < open, orlow > close, the row is corrupt. Flag every row where those relationships break. It happens more than you would guess, especially in resampled data where the aggregation was done wrong. - Flash-crash wicks that only printed on one venue. A candle whose low is 30 percent under the surrounding bars, that recovers by the next interval, on one exchange only. Real prices move across venues together, roughly, within arbitrage limits. A wick that exists on Binance but not Coinbase or Kraken at the same minute is an execution artifact, not a market you could have traded.
- Volume units that switch. This is the sneaky one. Some feeds report volume in the base asset, some in the quote, and some switch partway through a historical export without telling you. Suddenly your BTC volume is denominated in dollars and it is off by a factor of tens of thousands. If any strategy touches volume, this will wreck it silently.
Queries I actually run
I keep these as a preflight script and point it at any new series before it is allowed near a backtest. In rough SQL terms:
- Gap check: build a calendar of expected timestamps, left join, count nulls. If gaps are under a fraction of a percent and scattered, I move on. If they cluster, something is structurally wrong with the feed.
- Duplicate check:
SELECT timestamp, COUNT(*) FROM candles GROUP BY timestamp HAVING COUNT(*) > 1. Any result at all means stop and investigate the source, do not just dedupe and continue. - OHLC sanity:
WHERE high < low OR high < open OR high < close OR low > open OR low > close. Zero rows is the only acceptable answer. - Wick check: compute each bar's low against a rolling median of the surrounding bars, and flag anything that deviates beyond a wide band, say several times the typical range. This over-flags on purpose. You want to look at every candidate by eye or against a second venue.
- Volume continuity: compute the ratio of volume to a rolling average and look for step changes that hold, not spikes. A single high-volume bar is a real event. A permanent 10,000x shift in the baseline is a unit change.
Repair or drop
Once you find the bad bars, the harder question is what to do with them, and the honest answer is that it depends on how the bar breaks. My rough rules:
Drop, do not repair, when the bar is a fabrication. A duplicate timestamp, a high-low violation, a one-venue flash wick. There is no true value to recover because the row never represented a tradeable price. For a wick specifically, I do not clip the wick down to the neighbors, because that invents a price too. I either drop the bar and let it become a gap, or I replace it with the same interval from a more liquid venue if I have one.
Repair when the bar is merely missing. Short gaps in an otherwise continuous series can be forward-filled for close, with open equal to the prior close and volume set to zero, so your indicators do not silently window back in time. But forward-filling a long gap is lying to yourself. If a symbol is missing a full day, that day was probably a real halt or a delisting or an outage, and pretending it traded flat will bias any strategy that assumes you could have acted.
When in doubt, drop and mark, do not silently patch. The worst habit is a cleaning script that quietly overwrites bad rows so the next person, often future you, has no idea the data was ever touched. Keep a flag column. Log how many rows you dropped and why. If a backtest suddenly looks incredible, the first thing I check is whether cleaning changed the very bars the strategy is trading around.
One failure mode that cost me real time: a resampled 4 hour series built from 1 minute bars where the aggregation took the last close but the first open of the wrong window, so every bar was shifted by one candle. Every single OHLC check passed, because each row was internally consistent. It was only wrong in relation to time. The tell was that a trivial momentum rule printed a suspiciously smooth equity curve, which is the universe telling you the data has lookahead baked in. Now I test every new pipeline against a dumb strategy first and treat a too-clean curve as a data bug until proven otherwise.
A preprocessing checklist
Before any series earns a backtest, I want to be able to answer yes to all of these. Expected timestamps generated for the correct trading calendar and gaps counted. No duplicate timestamps. Zero high-low violations. Wicks flagged against a rolling band and cross-checked against a second venue where possible. Volume confirmed in a single, known unit across the whole history. Repairs limited to short gaps, everything else dropped and logged. And a sanity backtest with a naive rule to catch alignment errors the row-level checks miss.
None of this is glamorous, and it is roughly 80 percent of why one person's backtest survives contact with live markets and another's evaporates. When we wired the backtesting engine at Blockcircle, most of the hard work went into the ingestion layer that runs these checks on every candle before it reaches a strategy, precisely because the bug you cannot see is the expensive one. You do not need our stack to do this. You need to run the five queries and refuse to trust a result until they come back clean.