What is a volume check? Row count checks in SQL

Count what arrived, not what exists

A volume check looks at one period of data, not the whole table. The total row count of an orders table only ever grows, so it never looks wrong. What matters is how many rows landed yesterday, or in the last hour, compared with what that period normally brings.

That makes it the natural partner of a freshness check. Freshness tells you the pipeline wrote something. Volume tells you it wrote enough.

The simplest version: a floor

Pick a number that is clearly below a bad-but-normal day and alert under it.

SELECT count(*) AS rows_yesterday
FROM orders
WHERE created_at >= current_date - 1
  AND created_at <  current_date;

In arcodash you point a monitor at rows_yesterday with the < operator and your floor, and put the query on a schedule. Any scheduler works the same way: add HAVING count(*) < 50 to the query to get a result only when the day is thin.

Set the floor generously. The point is catching “way off”, not an ordinary slow Tuesday.

Compare with the same weekday

A fixed floor breaks the moment volume has a weekly shape or grows. Compare yesterday with the average of the same weekday over the past four weeks instead.

WITH daily AS (
  SELECT created_at::date AS day, count(*) AS n
  FROM orders
  WHERE created_at >= current_date - 29
    AND created_at <  current_date
  GROUP BY 1
)
SELECT y.n AS yesterday, round(avg(h.n)) AS same_weekday_avg
FROM daily y
JOIN daily h
  ON h.day < y.day
 AND extract(dow FROM h.day) = extract(dow FROM y.day)
WHERE y.day = current_date - 1
GROUP BY y.n
HAVING y.n < 0.5 * avg(h.n);

It returns a row only when yesterday is under half its usual weekday count. The row count anomaly post goes further into choosing the comparison window.

When a threshold is the wrong tool

If you do not want to pick the number, arcodash can learn it. A baseline monitor compares each new value with the median of the table’s recent history and triggers on outliers. When the AI provider is configured, “Suggest checks” defaults row count checks to baseline monitors. There is no seasonality model, so a strong weekly pattern can still produce false positives. See data observability monitors for the recipes.

Where volume checks go wrong

• Checking the wrong period. Run the check after the load window, not during it, or every morning starts with a false alarm.
• Late-arriving rows. If rows are backfilled a day later, yesterday’s count grows after you checked. Count by load time, or check a day behind.
• Legitimate quiet days. Holidays and launches move volume. Mute or snooze rather than loosening the threshold for good.

The SQL data check generator writes a volume check against your own table, along with freshness, null and duplicate checks.