7Data Quality and Lineage

On 6 May 2010, between 2:40 and 3:00 in the afternoon, more than 20 000 trades in more than 300 US securities were executed at prices 60% or more away from where they had been minutes before, some at a penny, some at $100 000. After the close the exchanges and their self-regulator agreed to break all of them as clearly erroneous. A tick store that had captured those trades faithfully, and never learned that they had been cancelled, fed them to every backtest, volatility estimate and execution study that touched that afternoon, for as long as it kept the data. Nothing in the store was corrupt; the data were exactly what the feed had sent. What was missing was a layer that checks data against what it should look like, marks what fails instead of deleting it, applies the corrections that arrive later as new versions, and knows which results were built from what. This chapter builds that layer (firm.dataqual) and measures it on a simulated day with planted defects and, just as important, genuine market events that a careless rule mistakes for defects.

7.1 Checks: completeness, validity, consistency, timeliness

Four families of checks cover most market-data defects. Completeness: no messages missing (the feed’s sequence numbers have no gap, chapter 2), no instrument silent when it should be quoting. Validity: every value in its domain (positive prices and sizes, known instruments, timestamps inside the session). Consistency: the data agree with themselves (no bid at or above the ask, trade prices near the quotes) and with other sources (another feed, a vendor’s copy, the official close). Timeliness: the data arrived when the contract says. Listing 7.1 is the suite of the chapter, written as data: each rule has a kind, a severity, an owner and parameters, and firm.dataqual runs any suite over any partition.

def rules(reference_trades: pd.DataFrame, busted: list, halts=(), aware=True) -> dict:
    """The suite. aware=False is the first version: the spike rule does not check that a
    jump comes back, and the staleness rule does not know about trading halts."""
    md, big = "market data", 10**9
    return {
        "events": [Rule("sequence", "sequence", "error", md, {"column": "seq"})],
        "quotes": [Rule("positive prices", "bounds", "error", md,
                        {"column": "bid", "lo": 1, "hi": big}),
                   Rule("crossed book", "crossed", "error", md, {"bid": "bid", "ask": "ask"}),
                   Rule("stale quotes", "stale", "warning", md,
                        {"time": "ts", "max_gap": 30 * SEC, "by": "symbol",
                         "exempt": halts if aware else ()})],
        "trades": [Rule("positive trades", "bounds", "error", md,
                        {"column": "qty", "lo": 1, "hi": big}),
                   Rule("price spike", "spike", "error", md,
                        {"column": "price", "k": 8.0, "window": 50, "floor": 1e-3,
                         "confirm": aware, "by": "symbol"}),
                   Rule("broken trades", "busted", "error", "operations",
                        {"key": "match", "busted": busted}),
                   Rule("vendor trade count", "cross_source", "warning", md,
                        {"time": "ts", "bucket": 60 * SEC, "tol": 0.02,
                         "reference": reference_trades})],
    }

The day is the second of the chapter-4 week: two instruments, 47 472 events, 9 258 quotes and 1 190 trades. Two genuine events are part of it: SIM2’s trading is halted for 60 seconds at 40% of the session, and SIM2’s price level jumps by 3% at 60% of it, on news, and stays there. Into copies of the day, twenty times with twenty seeds, the chapter plants six kinds of defect: a gap of 200 events, three crossed quotes, eight trade prices off by a factor of ten (a decimal slip, up or down), a 90-second silence of SIM1’s quotes, four trades broken by the venue after the close, and, separately, a vendor’s later correction of an official close.

7.2 Anomaly flags, not deletions

Deleting a bad row is tempting and wrong. The row may be right (a genuine 3% jump looks like a spike to a naive rule); another consumer may need it (a surveillance system wants the erroneous trades, chapter 30); and a deletion cannot be audited or undone. A flag keeps all three options open: the backtest excludes error-severity rows, the surveillance system reads them, and the rule can be improved and rerun.

The first version of the suite flagged the planted defects and a good deal more. The spike rule compared each trade price with the median of the previous 50 trades of its symbol, in robust units: the median absolute deviation of the previous deviations, with a floor of 0.1% so that a one-tick move is never a spike. On the day of the jump it flagged every trade for the next few dozen, until the backward median caught up with the new level (Figure 7.3). A spike and a level shift look identical from behind; from ahead they do not, because a spike comes back. The data-quality run happens after the close, so it may look ahead:

def _rows_spike(df, p):
    """Deviation of each value from the median of the previous `window` values of its
    symbol, in robust units: the median absolute deviation of those previous deviations,
    floored at `floor` (a relative move below which nothing is a spike, e.g. a tick). With
    `confirm`, the value must also deviate from the median of the next `window` values: a
    spike comes back, a genuine move of the level does not (a batch check may look ahead)."""
    out = []
    w, k, floor = p["window"], p["k"], p.get("floor", 1e-3)
    for _, g in df.groupby(p.get("by", "symbol"), sort=False):
        x = np.log(g[p["column"]].astype(float))
        behind = x.rolling(w, min_periods=5).median().shift(1)
        ahead = x[::-1].rolling(w, min_periods=5).median().shift(1)[::-1]
        dev = x - behind.fillna(ahead)       # a day's first rows have no history: look ahead
        mad = dev.abs().rolling(w, min_periods=5).median().shift(1).fillna(0.0)
        scale = np.maximum(1.4826 * mad, floor)
        z = dev / scale
        hit = z.abs() > k
        if p.get("confirm"):
            hit &= ((x - ahead.fillna(behind)) / scale).abs() > k
        for i in g.index[hit]:
            out.append((int(i), f"robust z {z[i]:.1f}"))
    return out

The staleness rule failed the same way on the halt: 60 seconds without a quote from SIM2 is a defect in the feed only if the market was open. The rule must know the market’s state — the venue’s trading-action messages (chapter 2’s events include them) — and not count time spent halted (Listing 7.3).

def _rows_stale(df, p):
    """Gaps longer than max_gap between consecutive rows of a symbol, not counting the time
    inside the `exempt` windows [(symbol, t0, t1)] in which the market itself was silent (a
    trading halt, a scheduled pause)."""
    out = []
    exempt = p.get("exempt", ())
    for sym, g in df.groupby(p.get("by", "symbol"), sort=False):
        t = g[p["time"]].to_numpy()
        for k in np.flatnonzero(np.diff(t) > p["max_gap"]):
            quiet = sum(max(0, min(t[k + 1], t1) - max(t[k], t0))
                        for s, t0, t1 in exempt if s == sym)
            if t[k + 1] - t[k] - quiet <= p["max_gap"]:     # the market's state explains it
                continue
            gap = (t[k + 1] - t[k]) / 1e9
            out.append((int(g.index[k + 1]), f"no update for {gap:.1f} s"))
    return out

Table 7.1 is the result over twenty seeds. Every planted defect is found by both versions of the suite; the difference is entirely in the false flags: 495 trades wrongly flagged as spikes and 20 halts wrongly flagged as stale feeds by the naive suite, none by the aware one.

A planted defect that every rule finds is not a test of the rules; the confounders are. A surveillance detector of One Quant Book 9 caught all its planted spoofers until legitimate look-alikes were added (its chapter 29), and the same lesson holds here: plant the genuine events the real market has, and measure the false flags.

7.3 Cross-source checks

A single source can be complete by its own sequence numbers and still wrong: a normaliser that dropped a message type, a compaction that lost a file. The chapter’s check counts trades per minute in the store and in a vendor’s copy of the day and flags minutes that differ by more than 2%: over the twenty seeds it raised 16 flags, the minutes in which the planted gap of 200 events happened to contain trades. It does not say which trades are missing, only where to look; that is the right division of labour between a cheap check that runs everywhere and an expensive investigation that runs where it points.

7.4 Vendor corrections and versions

The chapter’s vendor delivers SIM2’s official close at 16:30 on the day, and a corrected close at 07:10 the next morning. Both are versions of one fact in Book 7’s bitemporal store: asked at 23:00, the close is the first; asked at 08:00, the second. The broken trades of the hook are the same pattern: a list of cancellations, delivered after the close, applied as flags (busted) on the trades they name. What a correction changes is not only the fact but everything computed from it, and that is lineage.

7.5 Lineage and provenance

firm.dataqual’s lineage records each dataset version as a node, named by the content hash of its data (Book 7’s firm.workflow.content_hash), with an edge from each input version it was built from (Listing 7.4). A correction produces a new version of the input; every node downstream of the old version is stale. In the chapter’s example (Figure 7.4), the corrected close makes the marks, the P&L and the research features stale, and leaves the minute bars, built from the trades alone, untouched.

    def add(self, name: str, data, inputs=(), code: str = "") -> tuple:
        node = (name, content_hash(data))
        self.inputs[node] = tuple(inputs)
        self.code[node] = code
        return node

    def downstream(self, node: tuple) -> set:
        out, todo = set(), [node]
        while todo:
            n = todo.pop()
            for m, ins in self.inputs.items():
                if n in ins and m not in out:
                    out.add(m)
                    todo.append(m)
        return out

What quality is worth shows in the numbers of the day (seed 0). With the planted defects kept, SIM1’s VWAP is 2.69% too high and SIM2’s 1.08%, and SIM2’s realised variance of trade-price changes is 24 000 times its true value: eight decimal slips outweigh a day of genuine trading. With the error-severity rows excluded, both VWAPs are within 0.03% of the truth (the residue is the trades lost in the gap, which no flag can restore) and the realised variance equals the true one.

7.6 Tutorial: plant, flag, correct

7.7 Build: the data-quality layer

7.8 Exercises

7.9 Problem: The Afternoon That Was Cancelled

7.10 Interview questions