Market data quality checks that catch real bugs

Market data quality checks that catch real bugs share one trait: each names the field it reads and the action a failure obliges. Monitoring that alerts when a feed looks wrong catches almost nothing. The five checks below read a sequence number, two timestamps, a bid and an ask, a condition code, and a corporate action record, and each one says whether to stop quoting now or to open a ticket for the morning.

A sequence gap is the only proof that something is missing

Feeds number their messages per feed rather than per symbol, so one run of numbers covers every symbol the feed carries. That makes the sequence number the only field that can prove a negative. If you hold 11 and 13, message 12 existed and you do not have it.

Filter a feed down to one symbol first and the numbers come out full of holes by construction, since every other symbol was numbered in the same run. The panel below counts one liquid name's quote messages over a pinned hour against the span of sequence numbers they cover.

SELECT
    formatDateTime(toStartOfMinute(toTimeZone(sip_timestamp, 'America/New_York')), '%H:%i') AS et_time,
    count()                                                                     AS quote_count,
    round(100 * count() / (max(sequence_number) - min(sequence_number) + 1), 4) AS contiguity_pct
FROM global_markets.cache_stocks_quotes
WHERE ticker = 'AAPL'
  AND sip_timestamp >= '2026-03-10 14:00:00'
  AND sip_timestamp <  '2026-03-10 15:00:00'
GROUP BY et_time
ORDER BY et_time

In the minute beginning 10:00, that symbol accounted for 0.928% of the sequence numbers spanned, on 3991 quote messages. All 60 minutes in the panel are sparse the same way. Nothing is broken. That is the shape of a correct per symbol slice, and it is why continuity belongs in the feed handler, upstream of any symbol filter.

A real gap obliges work rather than a note. Request the retransmission if the feed offers one, and treat every book the gap could have touched as unreliable until a fresh snapshot arrives. Interpolating across the hole is always wrong: it manufactures a price no venue sent.

Exchange timestamp versus capture timestamp

Three clocks stamp a quote before you see it. The venue stamps when its matching engine published, the consolidated feed stamps when it processed the message, and your capture writes a third stamp on arrival. Our market data timestamps guide separates all three. Venue to capture measures two paths at once, which is why it makes the worst alert.

A busy moment or a slow venue fattens the tail and leaves the median where it was. A clock fault moves the median, and a median that goes negative, a message stamped as received before the venue published it, is arithmetically impossible without one. Alert on the median and on the sign, never on the maximum.

SELECT
    formatDateTime(toStartOfMinute(toTimeZone(sip_timestamp, 'America/New_York')), '%H:%i') AS et_time,
    round(quantileDeterministic(0.5)(toFloat64(toUnixTimestamp64Nano(sip_timestamp) - toUnixTimestamp64Nano(participant_timestamp)) / 1e6, toUInt64(sequence_number)), 3)  AS median_skew_ms,
    round(quantileDeterministic(0.95)(toFloat64(toUnixTimestamp64Nano(sip_timestamp) - toUnixTimestamp64Nano(participant_timestamp)) / 1e6, toUInt64(sequence_number)), 3) AS p95_skew_ms
FROM global_markets.cache_stocks_quotes
WHERE ticker = 'AAPL'
  AND sip_timestamp >= '2026-03-10 14:00:00'
  AND sip_timestamp <  '2026-03-10 15:00:00'
  AND participant_timestamp >= '2026-03-10 13:00:00'
GROUP BY et_time
ORDER BY et_time

In the minute at 10:00, the median gap between the venue stamp and the consolidated stamp ran 0.195 milliseconds, with the 95th percentile at 0.34. Read the two series as a floor and a ceiling: the bursts land on the upper line, and an alert set there fires all day without finding a defect.

A crossed book is usually a dropped side

A locked book is a bid equal to the ask. A crossed book is a bid above the ask. Inside one venue's book neither can last, since those orders would have traded, so a crossed quote from one venue means a message went missing or two updates landed out of order. Across a consolidated best bid and offer stitched from many venues, brief locks and crosses are ordinary, so the test is a rate against each symbol's own baseline. The cheaper sibling is the one sided test: a zero bid or a zero ask.

SELECT
    ticker                                                                     AS symbol,
    round(100 * countIf(bid_price = ask_price AND bid_price > 0) / count(), 4) AS locked_pct,
    round(100 * countIf(bid_price > ask_price AND ask_price > 0) / count(), 4) AS crossed_pct,
    round(100 * countIf(bid_price = 0 OR ask_price = 0) / count(), 4)          AS one_sided_pct
FROM global_markets.cache_stocks_quotes
WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'SPY', 'KO', 'XOM')
  AND sip_timestamp >= '2026-03-10 14:00:00'
  AND sip_timestamp <  '2026-03-10 15:00:00'
GROUP BY symbol
ORDER BY crossed_pct DESC

Across six household names in the same hour, sorted by crossed share, the top row is XOM: 0.2154% of its quote messages carried a bid above the ask, 0.4259% a bid equal to the ask, and 0% a zero on one side. Baselines differ by name, which is why this check runs per symbol.

A system that takes the midpoint of a crossed quote gets a mid above the offer and a negative spread, and every mark downstream inherits it. When the bid is not strictly below the ask, publish no mid, fall back to the last two sided quote, and label the mark stale.

Condition codes decide what counts as a last sale

Every print arrives with condition codes that say how it may be used: a print can be ineligible to update the last sale while still counting toward consolidated volume. A bar builder that ignores them produces highs and lows nobody could have traded at, plus a close that disagrees with the official one. The trade condition codes reference lists them, and how OHLCV bars are built shows where they enter the aggregation.

WITH
    prints AS
    (
        SELECT
            toInt32(arrayJoin(conditions)) AS code,
            count()                        AS trades,
            sum(size)                      AS shares
        FROM global_markets.stocks_trades
        WHERE ticker = 'AAPL'
          AND sip_timestamp >= '2026-03-10 14:00:00'
          AND sip_timestamp <  '2026-03-10 15:00:00'
        GROUP BY code
    ),
    code_names AS
    (
        SELECT
            toInt32(id) AS code,
            any(name)   AS code_name
        FROM global_markets.stocks_condition_codes
        WHERE asset_class = 'stocks'
          AND type = 'sale_condition'
        GROUP BY code
    )
SELECT
    concat(toString(p.code), ' ', if(empty(n.code_name), 'unmapped', n.code_name)) AS condition_label,
    p.trades                                                                       AS trade_count,
    round(100 * p.shares / (SELECT sum(shares) FROM prints), 2)                     AS share_of_hour_volume_pct
FROM prints AS p
LEFT JOIN code_names AS n ON n.code = p.code
ORDER BY p.trades DESC
LIMIT 12

The most common code on the tape that hour was 37 Odd Lot Trade, on 109162 prints covering 30.8% of the hour's shares. A print can carry several codes, and that column double counts some shares rather than summing to 100. The 9 codes in view are the ones a filter has to hold an opinion about. Treating them as decoration is how an odd lot print becomes the day's high.

Splits and symbol changes across the vendor boundary

In your own storage a symbol is a key. Across the vendor boundary it is a label that gets reassigned, and a split rewrites the meaning of every price before it. Why ticker symbols break datasets covers the identity half. The price half is mechanical: for each corporate action, compare the declared ratio against what the series does across the effective date.

WITH splits AS
(
    SELECT
        ticker,
        execution_date,
        any(split_from) AS from_shares,
        any(split_to)   AS to_shares
    FROM global_markets.stocks_splits
    WHERE execution_date >= '2024-06-01'
      AND execution_date <= '2026-06-30'
      AND split_to > split_from
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, execution_date
)
SELECT
    concat(s.ticker, ' ', formatDateTime(s.execution_date, '%b %e, %Y')) AS split_label,
    round(s.to_shares / s.from_shares, 2)                                AS declared_ratio,
    round(
        argMaxIf(toFloat64(d.close), d.date, d.date <  s.execution_date)
      / argMinIf(toFloat64(d.close), d.date, d.date >= s.execution_date), 2
    )                                                                    AS close_ratio
FROM global_markets.stocks_daily_aggs AS d
INNER JOIN splits AS s ON s.ticker = d.ticker
WHERE d.date >= '2024-05-20'
  AND d.date <= '2026-07-10'
  AND d.date >= s.execution_date - 7
  AND d.date <= s.execution_date + 7
GROUP BY s.ticker, s.execution_date, s.from_shares, s.to_shares
HAVING countIf(d.date <  s.execution_date) > 1
   AND countIf(d.date >= s.execution_date) > 1
   AND max(d.volume) > 2000000
ORDER BY s.execution_date DESC
LIMIT 10

The panel pins 10 forward splits on liquid names. The first row, DLLL Jun 26, 2026, declares a ratio of 8, and the close on the last session before the effective date divided by the close on the first session on or after it comes to 1.08. Where the two columns track each other, the series carries prices as they printed and your code owes the adjustment. Where a close ratio sits near 1 against a declared ratio well above it, the provider has already back adjusted. Mixing the two inside one series produces an overnight move of several hundred percent.

Which checks belong on the hot path

A stale mark and a bad print fail differently, and the difference decides placement. A bad print is loud: a negative spread, a high no bid supports. A stale mark is silent. The last good value keeps serving, every field is well formed, and nothing looks wrong until a position is marked against a price the market left twenty minutes ago.

Inline, per message, failing closed:

• the two sided gate, so no mid is published off a crossed quote
• the condition code filter, ahead of the last sale and the bar
• a per symbol staleness timer that expires a quote on wall clock time

End of day, allowed to be slow and to read a second source:

• sequence gap accounting per feed and session
• timestamp skew distributions per venue
• closing price and volume reconciliation against the exchange summary
• a corporate action replay over the affected history

The check almost everyone skips: revisions

History changes after it is written. Exchanges cancel and correct prints after the close, providers reprocess a bad session, and one reference data fix can rewrite months of a symbol's identity. When historical market data is revised goes through the mechanics.

The failure is quiet and it lives in research. A backtest reading today's copy of a 2019 session tests decisions nobody could have made at the time. The bias runs one way and it flatters: corrections mostly remove the prints that looked anomalous, which are exactly the prints a strategy fired on. Keep an ingest timestamp on every row, keep the superseded version instead of overwriting it, and the check becomes a diff over a settled window.

FAQ

Which market data quality checks matter most?

Sequence continuity on the raw feed, venue to feed timestamp skew, a locked and crossed book test, a condition code filter ahead of last sale and bar updates, and corporate action reconciliation. Revision diffs are the ones most often missing altogether.

How do I tell a clock fault from a slow venue?

Watch the median, not the maximum. Load and venue slowness fatten the tail and leave the median near where it was. A negative delay between publication and receipt can only be a clock fault.

Why does a backtest need unrevised history?

A backtest run on revised data fills at prices that were not on the screen at the time, and the revisions that matter most are cancellations of unusual prints. The test comes out looking better than the period could have been.

Open the SQL under any panel to see which field each check reads, then point the same queries at your own symbols on the Strasmore terminal.