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.k

The 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.

TableStartEndReason
`orders_curated`2026-08-122026-08-12email campaign to all customers, volume 1.6x
`orders_curated`2026-09-182026-09-19sale over 2 days, volume 2.4x
`orders_curated`2026-10-052026-10-05app 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 NULL

After 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?

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.