Tuning Thresholds vs History
A threshold decides how often someone gets pulled into an investigation. Set it too tight and people stop reading alerts; set it too loose and finance finds the incident first. The target is an alarm rate that fits the time the team has. Here, 3 engineers can each work about 5 quality alerts a week. Spread across 40 checks, that allows each check about 5 alerts per 90 days, and every threshold below is judged against that budget.
Backtest against 90 days of history
A backtest replays a candidate threshold over stored history and counts how often it would have fired. The distributional checks you already run produce one value per metric per day; store them in quality.metric_history and the backtest becomes a query. This one scores each day's row count for orders_curated against the mean and standard deviation of the 8 previous same weekdays, since a Sunday should be compared with other Sundays. It tries 4 values of k, the number of standard deviations that triggers an alert, over the last 90 days.
WITH scored AS (
SELECT
metric_date,
metric_value,
avg(metric_value) OVER (
PARTITION BY day_of_week(metric_date)
ORDER BY metric_date
ROWS BETWEEN 8 PRECEDING AND 1 PRECEDING
) AS baseline_mean,
stddev(metric_value) OVER (
PARTITION BY day_of_week(metric_date)
ORDER BY metric_date
ROWS BETWEEN 8 PRECEDING AND 1 PRECEDING
) AS baseline_sd
FROM quality.metric_history
WHERE table_name = 'orders_curated'
AND metric_name = 'row_count'
AND metric_date >= DATE '2026-05-31'
)
SELECT
t.k,
count_if(abs(s.metric_value - s.baseline_mean) > t.k * s.baseline_sd) AS alerts
FROM scored AS s
CROSS JOIN UNNEST(ARRAY[2.0, 2.5, 3.0, 4.0]) AS t (k)
WHERE s.metric_date >= DATE '2026-07-26'
GROUP BY t.k
ORDER BY t.kThe corrections log from the SLA work labels the answers. In this window, 4 of its entries changed the row count, so those 4 days are the incidents a row-count threshold should catch.
Before annotations, only k = 4.0 fits the budget, and it misses 3 of the 4 incidents. Most extra alerts at lower settings fired on events the team already understood, and those events also stretched the baseline for the 8 weeks after them, which made later real drops look smaller.
Annotate the anomalies you already know
An annotation is a row recording a deviation the team has already explained. It names the table and the reason, with a start and end date. Planned events get one before they happen, and unplanned ones get one after review. In both the backtest and production, annotated days stay out of the baseline and raise no alert.
| Table | Start | End | Reason |
|---|---|---|---|
| `orders_curated` | 2026-08-12 | 2026-08-12 | email campaign to all customers, volume 1.6x |
| `orders_curated` | 2026-09-18 | 2026-09-19 | sale over 2 days, volume 2.4x |
| `orders_curated` | 2026-10-05 | 2026-10-05 | app outage, real orders down 40% |
SELECT
h.table_name,
h.metric_name,
h.metric_date,
h.metric_value
FROM quality.metric_history AS h
LEFT JOIN quality.annotations AS a
ON a.table_name = h.table_name
AND h.metric_date BETWEEN a.start_date AND a.end_date
WHERE h.table_name = 'orders_curated'
AND h.metric_name = 'row_count'
AND a.table_name IS NULLAfter annotation, k = 2.5 catches all 4 incidents with 5 alerts, inside the budget. Give blocking checks a looser k than warning checks. A false warning costs an hour of reading. A false block holds a partition past 06:00 and spends the single spare day in the operational target.
- Do
- Backtest every new threshold against 90 days before it goes live
- Compare each day with the same weekday in earlier weeks
- Give every annotation an end date and a named owner
- Avoid
- Copy one k across all 40 checks
- Annotate a day only because it raised an alert
- Let annotated days feed the baseline
After an alert nobody could explain, a colleague annotates orders_curated from 2026-10-20 with no end date. What follows?
- The baseline improves, because one unexplained day stops stretching it for the next 8 weeks.
- Production alerting is unaffected, because annotations apply only to backtests run against history.
- The threshold widens on its own, because the annotation marks a new normal level for row count.
- Every day from Oct 20 on leaves the baseline and raises no alert, so the check is silenced.
About this lesson
This section is part of the Data Quality: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.