🛡 Quality, Testing & Ops · Chapter 4

Freshness, volume and anomaly monitoring

The worst data incidents don't throw errors. The job succeeds, the table is valid — and it simply has 40% fewer rows than it should. Only monitoring that knows what ‘normal’ looks like can see that.

Study this lesson free in the app →

The basics

Data monitoring is continuously watching a table's vital signs — how fresh it is, how many rows arrived, how its values are distributed — and alerting when they drift from normal.

Why it exists: tests check rules you thought of in advance (fare >= 0). But the most common real failure is silent: an upstream filter changes, one city's feed stops, a partner sends half a file. Every row is valid. The job is green. Only a comparison against history notices that Tuesday usually has 12,000 Shillong rides and today has 9,000.

The mental model — three vital signs:

• Freshness — how old is the newest data? now() − max(event_ts) vs an SLA
• Volume — did the expected amount arrive? row count vs a baseline
• Distribution — do values look like they usually do? null rate, average fare, share per city

The hard part is not computing them. It is defining normal so that alerts fire on real problems and stay quiet on Sundays and Diwali.

Words to know: SLA, baseline, seasonality, z-score, false positive.

Explained

Tuesday 4 November. Meghalaya Cabs' nightly load: ✅ succeeded. dbt tests: ✅ all 212 pass.

Shillong rides: 9,000. The last four Tuesdays: 12,100, 11,800, 12,400, 12,000.

A driver-app release had stopped sending events from older Android phones. No row was invalid. No job failed. Only a monitor that remembered last Tuesday could notice.

The milk delivery

Your milkman delivers two packets every morning at 6:30.

• 9:00 and no milk → freshness problem. Something stopped.
• Milk arrives on time but only one packet → volume problem. Something partial.
• Two packets on time, but it's toned milk instead of full cream → distribution problem. Something changed.

You notice all three without a written rule because you know what normal looks like. Monitoring is teaching the pipeline what normal looks like — including that on Sundays he comes at 7:15 and that's fine.

The three vital signs, precisely

Freshness — now() − max(event_ts). Compare with the consumer's SLA: ops dashboard 15 min, finance daily by 06:00. Also watch load freshness (max(_loaded_at)): a job can run on time and reload yesterday's file, so loaded-at is fresh while event time is stale.

Volume — row count per load or per hour. Compare to a seasonal baseline: same weekday over the last 4–8 weeks, not ‘yesterday’. Monday vs Sunday differs 30% for a cab company with no problem at all.

Distribution — per-column profiles: null %, distinct count, mean / p50 / p95 of fare, share of rides per city or payment method. A UPI share dropping from 72% to 41% overnight means something broke in payments logging, even though every row is valid.

Schema — columns added, removed or retyped. Cheap to watch, often the first sign.

A seasonal volume monitor in SQL

WITH daily AS (
  SELECT city, ride_date, count(*) AS n
  FROM fct_rides
  WHERE ride_date >= current_date - 57
  GROUP BY 1, 2
), baseline AS (
  SELECT t.city, t.n AS today_n,
         avg(h.n)         AS mean_n,
         stddev_samp(h.n) AS sd_n
  FROM daily t
  JOIN daily h
    ON h.city = t.city
   AND h.ride_date IN (t.ride_date - 7, t.ride_date - 14,
                       t.ride_date - 21, t.ride_date - 28)   -- same weekday
  WHERE t.ride_date = current_date - 1
  GROUP BY 1, 2
)
SELECT city, today_n, round(mean_n) AS expected,
       round((today_n - mean_n) / nullif(sd_n, 0), 1) AS z
FROM baseline
WHERE abs((today_n - mean_n) / nullif(sd_n, 0)) > 3
   OR today_n < 0.7 * mean_n;            -- floor for low-variance cities

Shillong: mean 12,075, sd 250, today 9,000 → z ≈ −12.3. Alert.

Freshness as configuration (dbt sources)

sources:
  - name: app_events
    loaded_at_field: _loaded_at
    freshness:
      warn_after:  {count: 30, period: minute}
      error_after: {count: 2,  period: hour}
    tables:
      - name: ride_completed
      - name: driver_location
        freshness:                       # tighter for the live map
          warn_after:  {count: 5,  period: minute}
          error_after: {count: 15, period: minute}

dbt source freshness computes max(_loaded_at) per table and compares. Run it before dbt build so stale inputs stop the run instead of rebuilding marts on old data.

Note it checks load time. Pair it with an event-time check if replays of old files are possible.

From metric to alert without the noise

Most alert fatigue comes from skipping the baseline and context steps — comparing to yesterday and ignoring the calendar.

Diwali night, 9:00 PM

Rides in Guwahati at 21:00 are 2.4× the usual Friday. The volume monitor fires z = +9. On-call gets paged.

Nothing is broken. It's Diwali.

The fix is not to raise the threshold for everyone forever. It's:

1. A calendar table of festivals and events (Diwali, Bihu, Shad Suk Mynsiem, IPL finals) that the monitor reads, downgrading spikes to an annotation.
2. Asymmetric thresholds: drops are far more often bugs than spikes are. Page on z < −4; only notify on z > +6.
3. Keep freshness paging as normal — Diwali doesn't excuse a late feed.

Gotchas

• Comparing to yesterday. Monday vs Sunday looks like an incident every week. Use same-weekday baselines.
• Only monitoring the total. Tura's feed dying is −3% of the company total and invisible; per-city it's −100%.
• Trusting load time as freshness. A job that reloads an old file on schedule looks perfectly fresh.
• Baselines that learn the incident. A rolling window that includes last week's outage now thinks low is normal. Exclude flagged days from the baseline.
• Z-score on tiny counts. Jowai has 40 rides on a Tuesday; ±10 is noise. Add an absolute floor or use a percentage rule for small segments.
• Alerts with no action. If the responder can only say “hm, interesting”, it should be a dashboard, not a page.

Words to know

In two minutes

• Tests catch rule violations you predicted; monitoring catches drift from normal you didn't predict.
• Freshness compares the newest timestamp with now, against an SLA set by the consumer.
• Distinguish load freshness (when it arrived) from event freshness (how recent the events are) — a job can reload stale data on time.
• Volume compares today's row count with a baseline that respects seasonality: same weekday, same hour.
• Distribution monitors catch silent semantic changes: null rate, mean, share by category.
• Static thresholds are simple but noisy; rolling z-scores or seasonal baselines adapt to growth and weekly patterns.
• Monitor per segment (city, partner), not only in total — a dead city can hide inside a healthy total.
• Every alert needs an owner and a tolerable false-positive rate, or it will be muted within a month.

Test yourself

There are 8 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.