SQL alert when a metric drops

Two jobs, kept apart

Getting a message into Slack and deciding that something is wrong are separate problems. Delivery to Slack is the plumbing. This page is the detection logic: what the query computes so that “the metric dropped” becomes a number you can alert on.

The failure it catches is the quiet one. Signups, orders or events fall by half because a tracking change shipped, a payment provider is rejecting cards, or a job stopped. There’s no error, only a lower number, and a lower number doesn’t look wrong on its own. It looks wrong next to the same number from last week.

Pattern 1: percent change against a comparable window

Compare yesterday, as a complete day, with the same weekday last week:

WITH d AS (
  SELECT
    count(*) FILTER (WHERE created_at >= date_trunc('day', now()) - interval '1 day'
                       AND created_at <  date_trunc('day', now()))      AS yesterday,
    count(*) FILTER (WHERE created_at >= date_trunc('day', now()) - interval '8 days'
                       AND created_at <  date_trunc('day', now()) - interval '7 days') AS same_day_last_week
  FROM signups
)
SELECT yesterday, same_day_last_week,
       coalesce(round(100.0 * (yesterday - same_day_last_week) / nullif(same_day_last_week, 0), 1), 0) AS pct_change
FROM d;

With 60 signups yesterday and 100 the same weekday a week earlier, pct_change is -40.0. The alert is on pct_change with < and -30. The nullif stops a divide by zero when last week had no data, and the coalesce turns that into 0. Without it the result is NULL, and the alert goes unknown rather than ok.

Schedule it once a day, a little after midnight UTC. A complete day needs a complete day’s data, so run it after your load finishes, not at 00:01. The schedule editor takes times in UTC.

Pattern 2: same hour, for faster signal

A daily check tells you tomorrow. If the metric matters within the hour, compare the last hour with the same hour yesterday:

WITH w AS (
  SELECT
    count(*) FILTER (WHERE created_at >  now() - interval '1 hour') AS now_n,
    count(*) FILTER (WHERE created_at >  now() - interval '1 day 1 hour'
                       AND created_at <= now() - interval '1 day')   AS yday_n
  FROM signups
)
SELECT now_n, yday_n,
       coalesce(round(100.0 * (now_n - yday_n) / nullif(yday_n, 0), 1), 0) AS pct_change
FROM w;

Eight signups now against eleven at this time yesterday is -27.3. That’s why small counts are the enemy here. Three versus five is -40 percent and means nothing. Only use hourly windows on metrics that run to dozens or hundreds per hour, or widen the window to four hours. Swap interval '1 day' for interval '7 days' to compare with the same hour last week.

Pattern 3: let a baseline monitor pick the limit

Percent limits need you to know how much variation is normal. A baseline monitor skips that. Point it at a plain daily count:

SELECT count(*) AS signups_yesterday
FROM signups
WHERE created_at >= date_trunc('day', now()) - interval '1 day'
  AND created_at <  date_trunc('day', now());

Create the alert as Baseline instead of Threshold. It needs 7 observations before it can fire, which on a daily schedule is a week, and details are on metric monitoring. It is plain statistics, with no AI provider or key involved.

It flags unusual values in both directions, so a spike alerts as well as a drop. If you only care about drops, use pattern 1.

The caveat is real: there’s no seasonality model. A metric that is far lower every weekend will flag every Saturday. Comparing weekday to the same weekday, as pattern 1 does, is the better fit for those metrics.

Set it up

The check is a saved, published query with a schedule, an alert on one column, and one or more destinations. Alerts only evaluate when their query refreshes. The condition reads the first row, so each query above returns exactly one.

When a drop alert fires, the notification includes a statistics line about what changed against recent values. With an AI provider configured and a data source AI profile, it can also include an AI root-cause note produced from up to three read-only queries. That is a hint about where to look. The routine in why did this metric drop is how you confirm it.

Honest limits

• One metric per alert. Splitting by plan or country is one alert per segment, or a query that returns the worst segment’s change.
• Percent change on small numbers is noise. Add an absolute floor in the SQL, for example only compute the change when last week’s count was at least 30, and return 0 otherwise.
• A real campaign or holiday looks like an anomaly. The alert can’t know about it; snooze it for the day instead of widening the limit permanently.
• Baseline monitors need 7 observations first and don’t model weekly patterns.
• Catching a metric that went to exactly zero because the data stopped is a freshness question. Run the freshness check beside this one so you can tell “fewer signups” from “no data”.

Two checks worth adding early are a null rate check and a reconciliation check.

For the basic checks around these patterns, the SQL data check generator produces freshness, row count, null and duplicate queries you can schedule as they are.