The Five Pillars: Freshness and Volume Monitors in SQL

Five pillars, and why they are five

Data observability is usually framed as five pillars, and the framing is worth memorizing because interviewers use it as a checklist:

1. Freshness. Is the data recent enough? A table that stopped loading at 2 a.m. looks perfectly correct and is completely wrong.
2. Volume. Did the expected amount of data arrive? Half a load is the failure people notice last.
3. Schema. Did the shape change? A dropped or retyped column breaks consumers that never touched the pipeline.
4. Distribution. Are the values still shaped the way they were? A null rate that jumps from 1% to 6% is a broken join upstream.
5. Lineage. What is upstream and downstream of this table? Without it you cannot answer "what else is affected".

The metric all five exist to shrink is data downtime: the total time your data was wrong, late, or missing. Framing your monitoring answer around reducing data downtime rather than around "adding alerts" is what separates an engineer from someone who has installed a tool.

Monitors read metadata, not data

Here is the design decision that makes always-on monitoring affordable. A monitor does not scan the table it watches. It reads the metadata a warehouse already keeps about that table: when it was last written, how many rows it holds, what its columns are. Scanning a billion-row fact table every fifteen minutes to ask "is it fresh" would cost more than the pipeline that built it.

So the monitors below run over two metadata tables. warehouse_tables is the catalog's own record of each table (owner, last write, agreed SLA). table_snapshots is the daily profile a monitoring job appends: row count and null rate per table per day. Both are tiny, and both can be queried every few minutes forever.

Table

Each pillar is one query shape over metadata. Schema and lineage are the next lesson; freshness, volume, and distribution are this one.

pillar	what breaks when it fails	the SQL shape
freshness	the dashboard shows yesterday and nobody notices	age from last_loaded_at compared to an SLA in minutes
volume	half the rows arrived and the totals quietly drop	latest row_count against a trailing-window AVG, with a deviation threshold
schema	a dropped or retyped column breaks every consumer	two catalog snapshots diffed with a self-join
distribution	a broken join fills a column with NULLs	latest null rate or category share against its own trailing average
lineage	you cannot answer what else is affected	an edges table walked with a recursive CTE

Freshness: age against an SLA

Freshness is subtraction. Take the evaluation time, subtract last_loaded_at, convert to minutes, and compare to the table's agreed freshness_sla_minutes. In SQLite that is strftime('%s', ...) on both sides, which gives epoch seconds:

(strftime('%s', '2026-03-02 09:00:00') - strftime('%s', last_loaded_at)) / 60

Two details decide whether the monitor is trustworthy. First, an SLA is per table, not global: a streaming staging table is late after 30 minutes, a slowly changing dimension is fine for a day. Second, a table sitting at exactly its SLA is not yet breaching, so the comparison is strictly greater than. Getting that wrong is how a monitor starts paging on healthy tables and gets muted.

This is not the same freshness Level 9 measured. de-l9-freshness-slas measures the age of the DATA: how far behind real time the newest event in the table is, derived from event timestamps. That is the pipeline-side question and it needs the table's contents. This lesson measures the age of the WRITE: how long since anything landed, read from catalog metadata. A table can be freshly written and still full of stale events (the source stopped producing), and it can hold very recent events while the write itself is hours late. Interviewers ask which one you mean, so say it: catalog freshness is the cheap always-on watcher, event-time freshness is the accurate one that costs a scan.

Volume: today against its own history

A row count means nothing on its own. It means something against a baseline, and the standard baseline is the same table's trailing window. A window function gives you it directly:

AVG(row_count) OVER (
  PARTITION BY table_name ORDER BY snapshot_date
  ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
)

1 PRECEDING as the upper bound is the part people get wrong. Including today in its own baseline drags the average toward the anomaly and hides small breaks. Then compare with a percentage deviation and a threshold, and make the comparison two-sided: a load that doubles is as suspicious as a load that halves, and duplicate-producing bugs are at least as common as missing-data bugs.

Distribution: the same query on a different column

Once you have the trailing-window shape you get the distribution pillar almost free. Swap row_count for null_user_id_rate and you are monitoring whether a column's null rate suddenly doubled, which is what a silently broken join looks like from the outside. The same shape monitors a category's share of rows, an average order value, or a percentage of negative amounts.

Common mistake: including the current day inside its own trailing baseline. With a 7-day window that pulls the average about 12% toward whatever today did, which is exactly the direction that hides a real anomaly. Bound the window at 1 PRECEDING.

Interview nuance: "how would you monitor this pipeline in production" is scored on two things: naming the five pillars without hesitating, and being able to sketch the freshness and volume queries on a whiteboard. Freshness and volume are the pair an intern is expected to implement, and they catch most real incidents. Mentioning that the monitors read metadata rather than the tables themselves is the detail that shows you have thought about the cost.

On a real platform this differs. Monte Carlo, Soda, and Bigeye ship these monitors as configuration rather than SQL, and they learn the thresholds from history instead of taking a hardcoded 50%. dbt has dbt source freshness for the freshness pillar specifically, with warn_after and error_after in the source config. The metadata is also already there in real warehouses: Snowflake's INFORMATION_SCHEMA.TABLES carries LAST_ALTERED and ROW_COUNT, BigQuery's __TABLES__ carries last_modified_time and row_count, and Glue keeps partition-level metadata. The queries you are about to write are the ones those products run for you.