data volume anomaly detection methods

TL;DR

The workhorse method is dead simple: keep a rolling baseline of row counts per table per day (mean and std over the last N days), and alert when today's count falls outside a few sigma. The refinements that make it actually usable are handling day-of-week seasonality (weekends differ from weekdays), excluding known backfill windows, and pairing volume checks with a null-rate check so you catch "same rows, empty columns" too.

The query

data volume anomaly detection methods

Use this when

• a pipeline silently delivered 10% of rows and nobody noticed for a week
• you need a first anomaly check that works on any table with a timestamp
• a data agent should sanity-check its own loads before declaring success

Not for

• validating column values or schema (volume checks only count rows)
• sub-minute streaming anomaly detection (this is a batch pattern)
• tables with fewer than a couple weeks of history (the baseline needs data)

Steps

1. Build a baseline table. Every day, record (table_name, date, row_count) into a small metadata table. Two weeks of history is the minimum useful baseline.

Expected output: the metadata table has one row per table per day, no gaps.

1. Compute rolling stats per table: mean and standard deviation of row_count over the trailing 14 or 30 days. Group by day-of-week if the table has weekly seasonality, which most event tables do.

Expected output: each table has a baseline mean/std, optionally per weekday.

1. Set alert bands. Start with mean +/- 3 sigma for paging and +/- 2 sigma for a warning; tighten after a week of watching false positives.

Expected output: a daily check query returns only genuinely unusual tables, not noise.

1. Exclude known anomalies from the baseline. Backfills, reprocesses, and incident days should be flagged (a boolean column on the metadata table) so they don't poison the mean.

Expected output: the baseline reflects normal days only; bands stay tight.

1. Add a companion null-rate check on the 2-3 most important columns. A load that delivers the right row count with NULLs everywhere passes a volume check and still ruins downstream.

Expected output: alerts fire for both missing rows and empty columns, each with a clear message naming the table and the deviation.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_N0lLyKJULyX7BaN-LCQb-w