Row count anomaly detection in SQL, no fixed floors

The usual row count check looks like this: count today’s rows, alert if it’s under some number. You pick the number on a Tuesday, it works great for a month, and then Sunday comes and it pages you for nothing.

Say a table usually gets ~1,200 rows on a weekday and ~300 on a weekend. A floor of 1,000 fires every Saturday. A floor of 250 never fires on a Wednesday, even when the load dropped 70%. No single number is right for both.

The fix is to stop asking “is this big enough?” and ask “is this normal for this weekday?” That’s one window function.

Build the daily counts first

Start with a calendar, then join your table onto it:

SELECT d::date AS day, count(o.id) AS n
FROM generate_series(current_date - 35, current_date - 1, interval '1 day') d
LEFT JOIN orders o
  ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY 1
ORDER BY 1;

The generate_series plus LEFT JOIN is the important part. If you just GROUP BY created_at::date on the table, a day with zero rows doesn’t appear at all. No row, nothing to compare, nothing to alert on. The worst failure your check can see (the load didn’t run) is exactly the one it would silently skip.

count(o.id) rather than count(*) matters here too. With the left join, count(*) would report 1 for an empty day.

I use a half-open range (>= start, < next day) instead of casting created_at to a date. It’s the same result, but the cast stops Postgres from using an index on the timestamp. On Snowflake or BigQuery you’d swap generate_series for GENERATOR or GENERATE_DATE_ARRAY; the shape is identical.

Compare to the same weekday

Now add a baseline. Partition by day of week, order by date, and average the four previous rows in that partition:

WITH daily AS (
  SELECT d::date AS day, count(o.id) AS n
  FROM generate_series(current_date - 35, current_date - 1, interval '1 day') d
  LEFT JOIN orders o
    ON o.created_at >= d AND o.created_at < d + interval '1 day'
  GROUP BY 1
), scored AS (
  SELECT day, n,
         avg(n) OVER (
           PARTITION BY extract(dow FROM day)
           ORDER BY day
           ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING
         ) AS baseline
  FROM daily
)
SELECT day, n, round(baseline) AS baseline
FROM scored
WHERE day = current_date - 1
  AND n < 0.6 * baseline;

Because the calendar has no gaps, each weekday partition has exactly one row per week, so “4 preceding rows” means “the same weekday over the last 4 weeks”. The frame ends at 1 PRECEDING, so the day being checked never contributes to its own baseline. I fetch 35 days so yesterday has four full weeks behind it.

I tested this against a fake table doing ~120 rows on weekdays and ~30 on weekends, with yesterday’s load deleted. It returned one row: yesterday, 0 rows against a baseline of 30. On a normal day it returned nothing.

That’s the contract I like for any data check: zero rows means healthy. The query only returns something when there’s something to look at, and the result itself tells you what broke.

Check yesterday, not today

Notice day = current_date - 1. If you run this at 9am and compare today’s count to a full-day baseline, it fails every single morning, because the day isn’t over.

You have two options. Check the last complete day, which is simple and what I’d do. Or scale the baseline by how much of the day has elapsed, which breaks on tables with lumpy intraday traffic. Take the first one unless you need an alert within the hour.

Also run the check after your load finishes, not at midnight sharp. A check scheduled before the nightly job will reliably catch the job being “late”.

Pick the tolerance from history

The 0.6 in the query is a guess. Don’t keep guesses. Backtest it: run the same logic over the last 90 days and count how often each threshold would have fired.

WITH daily AS (
  SELECT d::date AS day, count(o.id) AS n
  FROM generate_series(current_date - 130, current_date - 1, interval '1 day') d
  LEFT JOIN orders o
    ON o.created_at >= d AND o.created_at < d + interval '1 day'
  GROUP BY 1
), scored AS (
  SELECT day, n,
         avg(n) OVER w AS baseline,
         stddev_samp(n) OVER w AS sd
  FROM daily
  WINDOW w AS (PARTITION BY extract(dow FROM day) ORDER BY day
               ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING)
)
SELECT count(*) FILTER (WHERE n < 0.6 * baseline)       AS fires_60pct,
       count(*) FILTER (WHERE n < 0.8 * baseline)       AS fires_80pct,
       round(min(n / NULLIF(baseline, 0)), 2)           AS worst_ratio,
       count(*) FILTER (WHERE n < baseline - 3 * sd)    AS fires_3sd
FROM scored
WHERE day >= current_date - 90
  AND baseline IS NOT NULL;

Pick the tightest threshold that would have been quiet on days you know were fine, and loud on the days you know were broken. If 0.8 fires six times in 90 days and you remember only one real incident, 0.8 is too tight. If worst_ratio over the quarter is 0.72, a 0.6 threshold has never once fired and you have room to tighten it.

Fetch 130 days rather than 90 so the oldest day you score still has its four-week baseline.

Alerts people ignore are worse than no alerts. Backtesting is the cheapest way to avoid building one.

Holidays and one-off dips

Holidays will fire this check. Christmas looks like a broken load: a Sunday-sized day sitting on a weekday.

Two options. Accept it: one alert on December 25th that you read in ten seconds is a fine price for not maintaining a calendar. Or keep a small table of known quiet days and add AND day NOT IN (SELECT day FROM quiet_days). Only bother with the second if you’re actually getting paged on it.

The nastier version is the day after. A zero-row day drags down the next week’s baseline for that weekday, so the check can go quiet when it shouldn’t. If your loads fail often enough for this to matter, swap avg for a median with percentile_cont(0.5) WITHIN GROUP (ORDER BY n) over the same four rows. Window frames can’t do that in Postgres directly, so you’d self-join, which is more query than most people want. I’d live with the average until it bites.

Ratio or standard deviation?

The backtest query has both. Which to use:

• Ratio (n < 0.6 * baseline): easy to explain, easy to tune, and it behaves the same on a table with 50 rows a day or 5 million. Use this by default.
• Stddev band (n < baseline - 3 * sd): adapts to how noisy the table is. Good when volume swings naturally, say a 40% spread between Mondays. But with only four samples per weekday, the standard deviation itself is noisy. One odd week can make the band either absurdly wide or absurdly tight.

If you want the band, widen the window to 8 weeks (8 PRECEDING) and fetch more history. With four points, I trust the ratio more.

For tables under a few dozen rows a day, neither works well. Poisson noise alone will trip the ratio. Use an absolute floor for those, or a freshness check on the latest timestamp instead.

Running it on a schedule

Any scheduler that can run SQL and alert on a non-empty result works: a cron job and a psql call is enough. If you’d rather not run it yourself, arcodash can run the query on a schedule and alert Slack, email or PagerDuty. Monitors there check a threshold on one column, so wrap the final select as SELECT count(*) AS bad_days FROM (...) t and alert when bad_days > 0.

Start with the ratio, backtest it once, and adjust the number only when you have data saying so.

For a quick row count check on your own table before building the weekday baseline, the SQL data check generator writes one in your database’s dialect.