Distribution drift from metadata only

Reading rows to compare distributions is a solved problem with well-understood tools: a two-sample test, a divergence between histograms, a drift score per column. All of them need the values, which means a full scan or a sample, which means a cost per column per night on every table you care about.

The metadata alternative is to describe the shape with a handful of numbers that a single aggregate pass produces anyway, and then watch those numbers over time. A null rate is not a distribution. Four numbers that each move when the distribution moves are close enough to catch the failures that matter, at a cost of one query per table per night, which is what makes it the layer that survives the second invoice.

It is fifth in the order for a reason, and the reason is precision rather than difficulty. The null-rate query is easier than the same-weekday volume median. What makes it last is that a distribution statistic has no natural threshold: lateness has a promise, volume has a same-weekday history, uniqueness has a key. A null rate has only its own past, so every threshold is learned, and a learned threshold on a signal with almost no variance is the single most reliable way to manufacture false pages.

• Instrument it last so you have at least two weeks of history to learn from before you switch on an alert.
• Record it from night one even so, because the history is the only thing that makes the threshold defensible later.
• Expect to tune it more than any other monitor in the set. This is the one where the first threshold is usually wrong.

What it catches. A field that went optional upstream, an authentication or lookup step that stopped running, a join that started missing on the right side. Anything where the row still arrives but one of its values does not.

What it costs. One aggregate pass for every column you name, all in the same query. On a nightly partition this is the cheapest statistic on the list.

Its characteristic false alarm. The near-zero baseline. A column that is null 0.4 percent of the time has a trailing standard deviation measured in hundredths of a percentage point, so an ordinary night at 0.7 percent is more than three standard deviations out. This is night 8 in Bellwether's history and it is a page nobody wanted, covered properly in part 2.

The monitor itself is a trailing window with a sigma rule, computed exactly the way the bench in session 8 computes it: take the null rate for the last 14 nights, take their mean and standard deviation, and fire when tonight exceeds the mean plus three standard deviations.

What it catches. Two opposite failures with one number. Cardinality collapsing means a code list shrank or a lookup started returning a default for everything, so 40 distinct ship_region values became 3. Cardinality exploding on a column that should be low means a join fanned out or a free-text field got pointed at the wrong source.

What it costs. The most of the four. count(distinct) is a shuffle on most engines, and on a wide table with many columns it is the statistic that shows up on the bill. Approximate counting functions cut the cost hard and are accurate enough for a drift signal, which is the right trade here: you are watching a ratio move, not auditing a number.

Its characteristic false alarm. A legitimate new category. Product codes, promo codes and region codes get added by the business without telling anybody, and cardinality is by design sensitive to exactly that. Watch the ratio rather than the raw count, and set the band wider than instinct suggests.

One thing worth separating: cardinality on a key column is not this statistic. That is the duplicate rate from session 3, it has a defensible absolute threshold, and it belongs in its own monitor.

What it catches. The failures that a rate cannot see because they affect one row. A single order dated 1970 from a null-to-epoch conversion. A negative amount from a refund path that started writing to the orders table. A maximum that jumps by three orders of magnitude the night somebody changed a currency field from pounds to pence.

What it costs. One aggregate pass, and it comes free alongside the null-rate query.

Its characteristic false alarm. Genuine extremes. The first order over 1,000 pounds in a year is a real order, and a max-based monitor is right to notice and wrong to page. This is the statistic to log and chart rather than to alert on, with two exceptions worth an actual page: a value outside the physically possible range for the column, and a min or max that changes type of magnitude, such as a date before the company existed.

Store min and max for dates and amounts, alert only on the impossible, and read the chart weekly. That division is what stops this one becoming the monitor everybody mutes.

What it catches. The proportion of rows in each value of a low-cardinality column: status, channel, currency. Its unique contribution is the failure where one category stops arriving entirely while the row count holds steady, because something else grew to fill the gap.

What it costs. One group by, and the result is a handful of rows per night per column. Store it long and wide rather than as a single number, because the interesting version of the alert names the segment.

Its characteristic false alarm. Real mix changes. A marketing push moves the app share from 30 to 45 percent in a week, which is a true change in the business and a false page about the data. The mitigation is a wider band plus a minimum absolute share, so a segment that was 2 percent and became 3 percent does not page.

• A new category appearing is the case worth a separate rule. status gaining held is not a schema change, the column list is identical, and it is the exact failure that breaks every downstream case statement. Segment share is where that gets caught.
• A category disappearing is the mirror image and the more dangerous one, because a missing segment reads downstream as a genuine absence rather than as an error.
• Row count blindness is the reason it earns a slot. If mobile orders vanish and web orders grow by the same amount, volume is perfect, freshness is perfect, uniqueness is perfect, and every channel-split report in the estate is wrong.

Night 8 in Bellwether's history is an ordinary night. customer_id null rate came in at 0.7 percent against a trailing mean of 0.4 percent. Nothing changed upstream, nothing broke, and the monitor paged.

The arithmetic is not in dispute. The trailing nights sat between roughly 0.3 and 0.5 percent, so the standard deviation of that window is under a tenth of a percentage point, and three of those puts the alert line at about 0.6 percent. A night at 0.7 percent is genuinely outside it. The rule fired correctly on a difference of three hundredths of a percentage point, which is not a difference anybody in the business could act on.

That is the structural problem with a relative rule on a signal that barely moves: as variance goes to zero, any deviation at all becomes many sigma. Two fixes, both cheap, both with a stated cost:

• A floor on the absolute change. Require the null rate to have moved by at least some number of percentage points, or to exceed some absolute level, before the sigma rule is allowed to fire. Two percentage points removes night 8 and keeps night 25. The cost is stated plainly: any real drift smaller than two points is now invisible, so a column creeping from 0.5 to 1.8 percent null over a month passes silently.
• A minimum-variance guard. Floor the standard deviation before you multiply it, at something like a quarter of a percentage point. This keeps the rule relative for signals that do vary and stops it becoming a hair trigger for signals that do not. The cost is a threshold you now have to justify per column rather than one rule for the table.

A third option worth naming: require two consecutive nights outside the band. It halves the noise on wobbles and doubles the detection time on real drift, which on a nightly table is 24 extra hours of a wrong number in front of people.

The moment you write "trailing mean plus three standard deviations" you have crossed into statistical anomaly detection, and you have done it with the weakest possible detector: a global rule, no seasonality, no trend term, no robustness to the outlier that poisons your own window. It works, it catches an 11.4 percent jump on a 0.5 percent baseline, and it should not be mistaken for the state of the art.

The algorithms that do this properly are a body of work in their own right, and they belong to learn-anomaly-detection-with-phoebe: rolling and seasonal decomposition detectors that model the weekly shape instead of averaging it away, robust statistics that a single spike cannot capture, isolation forest and other one-class methods for the multivariate case where no single column looks wrong but the combination does, and the evaluation apparatus for choosing between them.

The line between the two is worth stating as a working rule. Metadata monitoring owns the statistics that fall out of a load: rates, counts, extremes and shares, watched with rules simple enough that anybody on the rota can read the alert and understand why it fired. Statistical detection owns the models that need training, tuning, seasonality and their own validation. Reach for the second only when the first has been running long enough to show you which columns actually move.

A detail the arithmetic produces that is easy to miss and expensive in practice. After night 25 comes into the trailing window, the mean and the standard deviation of that window are computed over a set that contains an 11.4 percent value alongside a dozen values near 0.5. The standard deviation jumps by a factor of roughly 25, and the alert line for the following two weeks sits around 10 percent.

So the fortnight immediately after an incident is the fortnight in which the monitor is least able to see the next one. A null rate creeping to 5 percent on night 27 would pass without comment. This is the opposite of what anybody wants from a monitor and it is a direct consequence of using a mean and a standard deviation over a raw window.

• Exclude known incident nights from the trailing window. This requires a table of incident dates, which you should be keeping anyway for the scoring in session 8.
• Use a median and a median absolute deviation instead of a mean and a standard deviation. A single spike moves a median almost not at all, which is the same argument session 3 made for volume bands.
• Winsorise the window by clipping the top and bottom value before computing. Crude, effective, and one extra clause.

Robust statistics are the right default for every trailing threshold in the set, and the reason this session uses the mean and standard deviation version is that it is what the bench in session 8 evaluates, so the false page on night 8 is a number you can reproduce rather than a claim.