Anomaly Detection

Anomaly Detection

Statistical Anomaly Detection on Row Counts and Metrics

Fixed bounds ("100 to 300 orders a day") are wrong twice a year and silent the rest. Anomaly detection learns them from history: the robust z-score compares a value with the median of a trailing window, in units of the median absolute deviation (MAD, times 1.4826), so one past spike cannot inflate the baseline as it would a mean and standard deviation. DuckDB 61,228 computes both as window aggregates:

anomaly.sql: robust z-scores for daily placed orders in the event tableSQL

-- Flag days far from the previous 28 days (robust z-score from the median and MAD).
SET TimeZone = 'UTC';
CREATE SECRET (TYPE s3, KEY_ID 'booknest-admin', SECRET 'booknest-secret-2026',
  ENDPOINT 'localhost:31900', URL_STYLE 'path', USE_SSL false, REGION 'us-east-1');
ATTACH 'warehouse' AS lake (TYPE iceberg, ENDPOINT 'http://localhost:31181',
  AUTHORIZATION_TYPE 'none');
.mode column
WITH daily AS (                    -- orders placed per day, as the event stream reports them
  SELECT ts::DATE AS day, count(*) AS orders FROM lake.booknest.order_events
  WHERE type = 'order_placed' GROUP BY ALL),
scored AS (
  SELECT day, orders,
         median(orders) OVER w AS med, 1.4826 * mad(orders) OVER w AS sigma
  FROM daily WINDOW w AS (ORDER BY day ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING))
SELECT day, orders, med, round(sigma, 1) AS sigma, round((orders - med) / sigma, 1) AS z
FROM scored WHERE day >= DATE '2025-02-01' AND abs((orders - med) / sigma) > 3.5
ORDER BY day;

Output

...
day         orders  med    sigma  z
----------  ------  -----  -----  ----
2025-03-23  220     182.0  9.6    3.9
2025-11-07  211     177.0  8.9    3.8
2026-02-20  220     184.5  8.9    4.0
2026-04-29  136     183.5  13.3   -3.6
2026-07-01  900     185.5  10.4   68.8

Of 516 scored days, five crossed the threshold. One is unmistakable: 1 July, the 900 orders Small Commits and Compaction streamed in 15 minutes, 68.8 spreads above normal. The other four are the price of a threshold on noisy data; a real monitor would also model the weekly cycle before paging anyone. Score every metric that matters (rows per load, null rates, revenue per channel) and keep the scores, since slow drift shows only in their trend. Monte Carlo 335,091 , Soda Cloud and Bigeye sell this as a service; the open routes are a scheduled query like this one or Elementary's anomaly tests in dbt 37,942 .

MENU