How to clean tick data

A cleaning workflow for cross-venue tick data: deduplication, event ordering, bad-print filtering, and a traceable record of every transformation. Each stage exists because a defect it removes would otherwise flow silently into every candle, indicator and backtest built on top.

Tick data is the rawest form of market data: individual trades and quote updates, each with a timestamp, a price and a size. Everything a researcher builds sits on top of it. A candlestick is nothing but a summary of the ticks inside its interval, so a defect in the ticks becomes a defect in the candle, and from there in every indicator and backtest that reads the candle. That is the whole argument for cleaning: not tidiness, but the fact that errors at the bottom of the stack are invisible from the top.

A workable cleaning workflow has four stages, and each one exists to remove a specific defect class.

The first stage is deduplication. The same market event can reach a research dataset twice: a feed reconnect replays a window, an ingestion job retries a batch, or two collection paths overlap. A deduplication stage defines an identity for an event from the fields the feed provides, such as instrument, venue, timestamp, price, size and any sequence or trade identifier, and drops records that repeat that identity. The right identity key depends on the schema of the feed at hand, which is why the key itself belongs in the transformation record described below.

The second stage is ordering. Analysis assumes events are examined in the order they happened, but data rarely arrives that way: transport delays, retries and multi-venue collection all interleave records out of sequence. When work spans venues, the problem is sharper, because each venue stamps events with its own clock and its own delivery path. The ordering stage sorts by event time within an instrument and venue, and flags records whose position had to change, because a record that moved is a record whose original placement would have misled.

The third stage is bad-print filtering. Some records are simply wrong: a price orders of magnitude away from its neighbours, a zero or negative size, a malformed field. Filters catch values that are impossible on their face and values that are wildly inconsistent with the surrounding market context. The threshold for "wildly inconsistent" is a research decision, and it should be written down and applied mechanically rather than judged record by record, because a hand‑picked exception is a bias with a memory.

The fourth stage is the transformation record. Every drop, reorder and filter decision is logged with what was changed and under which rule. This is the stage that makes the other three trustworthy: a cleaned dataset with no record of its cleaning cannot be examined, compared or reproduced, and a workflow that cannot be reproduced quietly becomes an untracked source of research variance. When two researchers get different results from the same raw feed, the transformation record is where the difference is found.

The order of the stages matters. Deduplicate before ordering, because duplicates distort the sequence they sit in. Order before filtering, because a print can only be judged against its true neighbours. And log everything from the first stage onward, so that no record disappears without an entry saying why.

None of this requires exotic tooling. It requires the discipline of treating data preparation as part of the experiment rather than a chore before it, the same discipline that separates a testable strategy from an anecdote. A researcher who can say exactly what was removed from the raw feed, and why, owns their results. One who cannot is trusting whatever the feed happened to deliver.

Drafted with AI assistance from cited sources. Reviewed and approved by Sonar Sciences Quant & Research Team.