How Do You Know It's Broken?

A quality check runs inside the pipeline and asks whether this load is right. A monitor runs beside the pipeline and asks whether the pipeline itself is alive, on time and shaped the way consumers expect. The 3 monitors interviewers look for are freshness, volume and schema, and the answer that lands describes each one as a query with a threshold and an action, checked into the repository next to the pipeline code.

Freshness: how old is the newest row

Define the SLA in consumer terms first. The orders table promises the previous day's partition, published by 07:00 each morning, so on 21 October consumers expect the partition for 20 October to be there. The monitor asks exactly that at the deadline: is the newest loaded dt the expected logical date? Store a loaded_at column at write time as well, so the alert can report when the last load landed without scanning event timestamps that may legitimately lag the load.

SELECT
  MAX(dt) AS newest_dt,
  MAX(loaded_at) AS newest_load,
  COALESCE(MAX(dt) < DATE '2026-10-20', true) AS is_stale
FROM silver.orders
WHERE dt >= DATE '2026-10-19'

The WHERE on dt is the cost control. Without it the monitor scans 3 years of partitions on every run to find a maximum that only ever lives in the newest one. With it the query reads only the newest partitions and finishes in seconds, so it can repeat after the deadline until the partition lands. Comparing dates is also what keeps the page meaningful: a threshold on minutes since MAX(loaded_at) would sit above 60 for most of every day on a once-a-day load and page around the clock.

• 15 min
• 2 partitions

Volume: compare to the right baseline

Row count against yesterday catches a feed that died, but on this orders table it also fires every Monday, because Sunday orders run about 40% below weekdays. The baseline that works is the median count of the same weekday over the previous 4 weeks, with a band around it. A Monday compares to the last 4 Mondays, and a 30% deviation either way fires.

WITH daily AS (
  SELECT
    dt,
    COUNT(*) AS row_count
  FROM silver.orders
  WHERE dt >= DATE '2026-09-22'
  GROUP BY dt
)
SELECT
  today.row_count AS today_rows,
  approx_percentile(hist.row_count, 0.5) AS baseline_rows,
  CAST(today.row_count AS DOUBLE) / approx_percentile(hist.row_count, 0.5) - 1 AS deviation
FROM daily today
CROSS JOIN daily hist
WHERE today.dt = DATE '2026-10-20'
  AND hist.dt < DATE '2026-10-20'
  AND day_of_week(hist.dt) = day_of_week(DATE '2026-10-20')
GROUP BY today.row_count

The median rather than the mean keeps one holiday from dragging the baseline. If a public holiday cut orders by half 2 weeks ago, the mean of 4 weeks moves 12% and the next 2 normal Mondays look high; the median barely moves.

Schema drift: catch the change before the join does

Schema drift means the source changed shape: a column renamed, a type widened from integer to string, a column dropped. The monitor stores the expected column list and types for each source table and compares the live schema against it before the load starts. Added columns are additive and warn. Removed or retyped columns break downstream SELECT lists and casts, so they block the load. Catching a rename at the schema check costs 1 query; catching it when a join returns 0 rows costs the morning.

SELECT
  column_name,
  data_type
FROM information_schema.columns
WHERE table_schema = 'bronze'
  AND table_name = 'orders'
ORDER BY ordinal_position

Monitoring as code

Writing the thresholds in a configuration file next to the pipeline turns them into something reviewed, versioned and testable. A pull request that loosens the volume band from 30% to 50% leaves a record of who did it and why, and a reviewer can push back. The file below is the whole monitor for one table.

table: silver.orders
owner: orders-data-team
monitors:
  - type: freshness
    partition_column: dt
    expected_partition: previous_day
    deadline: "07:00"
    recheck_minutes: 15
    on_breach: page
  - type: volume
    baseline: same_weekday_median
    lookback_weeks: 4
    max_deviation: 0.30
    on_breach: ticket
  - type: schema
    source: bronze.orders
    on_added_column: warn
    on_removed_column: block
    on_type_change: block

The actions differ by signal because the costs differ. Paging on every volume deviation teaches the on-call to ignore pages within a month, which is the failure mode that makes monitoring worthless. Reserve the page for the 2 signals where consumers are already affected or the load will definitely fail, and route the rest to a queue a human clears each morning.

About this lesson

This section is part of the Make It Reliable: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.