Volume and uniqueness

Twelve thousand rows. Is that fine? There is no answer to that question, and every volume monitor that gets muted was built by somebody who thought there was. The count only becomes a signal once you attach a comparison, and choosing the comparison is the design work. Three candidates come up every time:

• A fixed band. Cheap to write, needs no history, and wrong on any table with a weekly rhythm. Bellwether's is the ordinary case, not an unusual one: grocery delivery is quieter at the weekend and no single band holds both patterns while still being tight enough to catch a missing shard.
• The same weekday. Compares Tuesday against Tuesdays. Handles the weekly rhythm exactly, needs four weeks of history to be comfortable, and is the right default for anything driven by human behaviour.
• A robust band around the median. For tables with no weekly rhythm at all, where the useful question is only "is tonight unlike the recent past" and you want the estimate not to be dragged around by the incident you are trying to detect.

Worth being explicit, because the temptation is to think of volume as a general-purpose health check and it is nothing of the kind.

That last row is the honest boundary of tonight: a null-rate collapse leaves both of these monitors green, which is why distribution is a separate signal with its own session.

The band is 12,000 plus or minus 30 percent, so a floor of 8,400 and a ceiling of 15,600. A weekend night at about 5,400 rows is not near that floor, it is 36 percent below it, so every weekend night is an alert by construction. In a 30-night window that is eight false pages, and they buy nothing: night 16 at 4,920 rows is caught by the same-weekday comparison too.

Widening the band does not rescue it. To hold a weekend night inside the band you need a floor below 5,200, which means a band of roughly plus or minus 57 percent around 12,000, and a weekday night can then lose 6,000 rows without a peep. That is the trade in its clearest form: a band wide enough to be quiet is too wide to detect the thing you built it for.

Compare tonight against the median of the last four same weekdays, and alert outside plus or minus 25 percent. On Bellwether's history that monitor produces zero false pages in 30 nights and catches night 16 with the sentence 4920 rows vs a same-weekday median of 11986, -59 percent, which is a message somebody can act on without opening anything.

Median rather than mean, for a reason that matters more than it sounds. Your history contains the incidents you are trying to detect. A mean of four values is moved by roughly a quarter of any outlier in the window, so the night after a partial load your comparison point has dropped and your threshold has quietly loosened exactly when you need it tight. A median of four values is unmoved by one outlier at either extreme. The property you want in a monitor is that a past incident does not raise the bar for detecting the next one.

• Four same weekdays is four weeks of calendar, which is long enough to be stable and short enough that a real change in the business washes through within a month.
• Twenty-five percent is wide enough to absorb ordinary week-to-week movement on this table and tight enough that a missing shard of any size clears it. Set yours from your own spread, not from this page.
• Both directions. A count 40 percent above the median is a duplicate replay or a backfill running into today's partition. Firing only on the low side is the most common half-built volume monitor.
• Guard the cold start. With fewer than two same weekdays in history the monitor must return "not enough history" rather than a comparison against one night.

Plenty of tables have no weekly pattern: a machine-generated event stream, a reference table, a slowly growing dimension. There the same-weekday comparison throws away three quarters of your history for nothing, and what you want is a band around the recent median that is not dragged around by outliers.

Median absolute deviation is the standard tool. Take the median of the series, take the absolute deviation of every point from it, take the median of those. Multiply by 1.4826 and you have a robust stand-in for a standard deviation on roughly normal data, which lets you keep thinking in familiar multiples.

Then band at the median plus or minus three times sd_robust. Two cautions. On a strongly seasonal table this is the wrong tool and will simply widen until it holds the weekend, so check for rhythm first. And on a table with genuinely tiny variance the MAD can collapse towards zero, which makes the band absurdly tight; floor it at some absolute minimum that means something in the units of the table, exactly as a percentage floor is used on a null-rate monitor.

The duplicate rate is 1 - count(distinct key) / count(*), and on a large table it is a better monitor than a hard uniqueness assertion for three reasons that all show up in the first month.

• It degrades gracefully. A hard assertion has two states. A rate has a history, so you can see 0.6 percent creeping to 1.4 percent over a fortnight and fix the cause before it is an incident. A pass-or-fail check gives you no warning at all and then a build failure.
• It gives you a sentence. "Duplicate rate 8.4 percent against a limit of 2.0, up from 1.0 last night" is a message that survives being forwarded to somebody who does not know the table. "unique_order_id failed" is a message that requires a query before anybody can act.
• It tolerates a known floor. Many real tables have a small, understood duplicate population: a replayed event, a soft-deleted row, a late correction. A hard assertion on such a table is either disabled or wrapped in an exclusion list that nobody maintains. A rate lets you set the threshold above the known floor and monitor the deviation from it.

Set the threshold from the history, the way you set the freshness threshold. On this table the clean nights sit between about 0.6 and 1.4 percent, so a 2.0 percent limit is above every clean night and far below the 8.4 percent of the incident. It fires once in 30 nights and that once is night 19.

This is where most duplicate monitors are actually wrong, and the error is invisible because the monitor is green. Before you can measure uniqueness you have to answer one question about the table: what is one row? One order? One order line? One order line per warehouse per day? A duplicate monitor is only meaningful against the declared grain, and if the grain in your head differs from the grain in the table, the monitor measures nothing.

Run both rates side by side on any table you are unsure about. If dup_rate_order is 40 percent and dup_rate_grain is 0, the table is not one row per order and a monitor on order_id alone would page every single night until somebody muted it. If both are 0, one row is one order and the simple monitor is correct.

• Concatenate carefully. A naive a || b makes ('ab', 'c') and ('a', 'bc') collide. Use a separator that cannot occur in the values, and coalesce the nulls, or the whole key silently becomes null and the distinct count collapses.
• Nulls in a key are not distinct in the way you expect. count(distinct) ignores nulls entirely, so a key column that is 30 percent null produces a duplicate rate that looks alarming for the wrong reason. Monitor the key's null rate alongside its duplicate rate.
• Write the grain down next to the table. One sentence in the model's documentation. This is the single cheapest piece of documentation in a warehouse and the one most often missing.

The nastiest version of this failure is the one where a merge duplicates some rows and drops others, so the net row count barely moves. It is common because it has a single cause: a join key that changed cardinality upstream will both fan out the rows that now match several times and lose the rows that now match nothing.

What that does to your monitors is worth spelling out. Volume sees a net change of maybe one percent and passes. Freshness passes. The pipeline passes. The duplicate rate rises, because the fanned-out rows are genuinely duplicated at the declared grain, and it is the only signal that moves enough to cross a threshold.