A nightly freshness check alerts when a table's daily row count deviates more than 20% from the previous day. Volume follows a weekly pattern: weekends run about 40% below weekdays. Roughly how many alerts a month does this produce before anything is actually wrong, and what should replace it?
Show the full answer Hide the answer
The arithmetic
The comparison is day over day, so a breach happens at each transition across the 40% step, not on every weekend day.
- Friday to Saturday: a 40% drop breaches the 20% band. One alert.
- Saturday to Sunday: roughly flat. No alert.
- Sunday to Monday: a rise of about 67% from the weekend base. One alert.
That is 2 alerts per week, so about 8 to 9 a month, every month, with nothing wrong. Add public holidays and month-end batches and it is comfortably into double figures in some months.
Why this matters more than the number
A check that fires 8 times a month for known reasons is muted within a quarter, and after it is muted the real failure passes silently. The cost of a bad threshold is not the noise, it is the loss of the control: teams do not delete these alerts, they route them to a channel nobody reads, and the dataset is then unmonitored while appearing monitored.
What to replace it with
- Compare against the same weekday, not yesterday. One line of change, and it removes the entire weekly cycle from the signal.
- Use a band from history: the median of the last 8 same-weekday values with a tolerance of a few median absolute deviations. Seasonality and drift are handled without anyone maintaining a number.
- Alert on freshness separately from volume. "No rows at all by 07:00" is a different failure from "fewer rows than expected", it needs a different response, and it is the one that should page.
- Keep an absolute floor as a backstop: zero rows, or below a level the business knows is impossible. Statistical checks fail on the day the history itself is wrong.
Why the other options fail
- About 1 assumes the check compares against a weekly baseline already. It is what people expect the system to do, which is why the noise is a surprise.
- About 20 counts every weekend and holiday day rather than the transitions between levels. A day-over-day comparison is flat inside a weekend, which is the subtlety that makes this worth working through.
- About 30 would mean roughly one alert a day, which is what a poorly set absolute threshold produces — a real failure mode, but not this one.
When not to replace the crude threshold
On a brand-new pipeline with no history, a crude band is better than nothing and can be replaced in a month. The mistake is not starting there; it is leaving it in place once the data has shown its shape, which is usually visible after two or three weeks.