I pulled a few years of hourly candles from an exchange API once, ran a backtest, and got a Sharpe that made me suspicious in the good way and then the bad way. The strategy looked incredible. It was incredible because roughly a fifth of the candles were quietly missing, and the gaps were not random. They clustered around the exact volatile stretches where the strategy did its worst work in reality. The API had returned a clean-looking array with no error, no null, no warning. It just skipped the rows it did not have and handed me a shorter list. Nothing about the response told me half a day of a crash was gone.
That is the whole problem with historical OHLCV data in one sentence. The failures are silent. A missing candle looks exactly like a candle that never needed to exist, and your code has no way to tell the difference unless you go looking. So most of the work of building a trustworthy dataset is not fetching. It is proving to yourself that what you fetched is complete and honest before you let a backtest touch it.
Gaps are the easy part, if you check for them
The nice thing about gaps is that they are detectable with arithmetic. You know your interval. Hourly candles should be spaced one hour apart, daily ones a day apart, and so on. So you sort by timestamp, take the difference between each candle and the one before it, and flag anything that is not exactly one interval. Any diff larger than the interval is a hole. Any diff smaller, or a diff of zero, means you have duplicates, which happens more than you would think when you stitch overlapping API pages together.
Here is the validation I run before trusting any candle table, roughly in this order:
- Count expected candles between your first and last timestamp for the interval, then compare to the actual row count. If actual is lower, you have gaps. If it is higher, you have duplicates.
- Compute the gap between consecutive timestamps and list every row where that gap is not exactly one interval. This gives you the exact locations, not just a count.
- Check for zero or negative volume runs. Long stretches of zero volume on a liquid pair usually mean the exchange was serving flat filler, not real trades.
- Sanity check the OHLC relationships on every row. High should be the max of open, high, low, close, and low should be the min. When those break, you are looking at bad data or a mismatched schema, and it is worth stopping right there.
- Look for impossible single-bar moves. A price that doubles and halves inside one candle on a major pair is almost always a bad print or a decimals problem, not a real wick.
None of this is clever. It is just the stuff that never gets written because everyone assumes the vendor did it. The vendor did not. I have yet to find a single source, free or paid, that I did not eventually catch handing me something wrong. Assume yours is the same and you will be right most of the time.
Stitching sources without lying to yourself
Once you know where the holes are, the temptation is to fill them, and this is where people quietly corrupt their own data. Forward-filling a missing candle by copying the last close feels harmless. It is not, because now you have a bar with zero range and zero volume sitting in the middle of what was actually a violent move, and any strategy that reads range or volume will treat that calm as real. If you must fill, fill from a second independent source for that exact window, and tag the row so you know later that it was patched and where it came from.
When you stitch two exchanges together, the two things that will bite you are timestamp conventions and price level. Some sources stamp a candle with its open time, others with its close time, so an hourly bar from one lines up an hour off from the other unless you normalize. Get everyone onto UTC open-time, or whatever you pick, but pick one and enforce it. On price level, the same pair can trade at meaningfully different prices on different venues, especially in thin conditions, so a naive splice leaves a visible step in your series exactly at the seam. Prefer to use one primary source for a continuous range and only reach for a secondary source to patch specific gaps, rather than alternating between them bar by bar.
The rule of thumb I settled on is that a backtest should always be able to answer where every candle came from. If your pipeline cannot tell you that a given day was primary, patched, or synthetic, you cannot trust a result that depends on that day, and you will not know which results those are.
Splits, renames, and the pairs that disappear
Equities have splits and crypto mostly does not, but crypto has its own version of the same trap, which is the redenomination or the rename. A token does a token swap, a project rebrands, an exchange renames the market, and suddenly the same economic thing has two ticker histories that do not join up, or one ticker now points at a completely different asset. For traditional markets the classic version is a stock split where the raw price series has a cliff in it that is not a real move at all. In both cases the fix is the same idea. You need adjusted, continuous series where the corporate action or the rename has been accounted for, and you need to keep the mapping of what became what.
The one that quietly ruins backtests is delisting. A pair gets delisted, the exchange stops serving its history through the current API, and if you only ever screen the assets that exist today, your universe is made entirely of survivors. Every coin that went to zero, every pair that got pulled, every project that died is simply absent, so your backtest is running on the winners that were lucky enough to still be listed. That is survivorship bias, and it makes almost any long-biased strategy look better than it was, because the losers were removed from the sample before you ever saw them.
The defense is to treat delisted assets as first-class data. When you first ingest a pair, snapshot its full history to your own store and never delete it, even after the exchange stops serving it. Keep a status field that says active, delisted, or renamed, with the date the status changed. Then when you build a test universe for a past period, select the pairs that were tradable then, not the pairs that are tradable now. It is a small schema decision that you have to make early, because you cannot backfill a history the exchange has already stopped serving. Once it is gone from their side, it is gone unless you kept it.
What to actually do before you trust a dataset
Run the gap and duplicate check, the OHLC sanity check, and the zero-volume scan on every table, and refuse to backtest anything that fails until you understand why. Keep a provenance column so every candle knows its source and whether it was patched. Store delisted and renamed pairs permanently with dated status flags, and build historical universes from what was live at the time. Prefer one primary source per range and patch surgically instead of blending. When we built the market data layer behind Blockcircle's backtester, most of the hard engineering was not the fetching, it was this, the boring validation and the refusal to throw anything away.
The honest version of all this is that you will still miss something. Data quality is not a state you reach, it is a set of checks you keep running, and the useful shift is going from assuming your candles are clean to assuming they are guilty until a query proves otherwise. That mindset costs you an afternoon of writing validation and saves you from trusting a result that was never real.