Where freshness sits in the monitor set
The metrics table from session 1 now holds one row per table per night: table_name, load_date, rows, distinct_keys, max_updated_at, late_minutes, null_rate_customer, column_count. That single column late_minutes is enough to build a freshness monitor whose threshold nobody has to defend in a meeting, because the history defends it. analytics.orders_daily at Bellwether is promised by 06:00, carries about 12,000 rows on a weekday and 9 columns, and the eight clean nights already recorded landed 8, 14, 6, 19, 11, 22, 9 and 17 minutes past.
★ What you walk out with tonight. A freshness monitor whose threshold is a trailing p95 plus a 10-minute pad, which on this history fires on night 9 and on nothing else. The two reasons a passing freshness check can be meaningless, both of which you can reproduce in five minutes on a table you own. And the arithmetic that shows what happens when you tighten that same monitor to p75 plus a minute.
LiveWhat a fixed deadline actually encodes3 min▶
Bellwether promises orders_daily by 06:00 and the first monitor fired if the table landed more than 30 minutes past. Where did 30 come from? From looking at the recorded nights and picking a number that none of them exceeded. That is the whole method, and it is worth saying out loud, because once you can see it you can see what it costs: the number encodes a guess that no clean night in your history exceeded, frozen at the moment you guessed it.
Two things then happen. The pipeline gets slower over a quarter as the source grows, the clean nights drift up towards 30, and one ordinary Tuesday tips over it. Or somebody optimises the load, every night now lands in 4 minutes, and the deadline is 26 minutes of slack that would hide a genuinely broken night. A fixed deadline is right on the day you set it and slowly wrong afterwards, in a direction you cannot predict.
Bellwether's night 9 landed 230 minutes after the 06:00 promise because an upstream export job retried twice. The trailing p95 of the fourteen nights before it was 22 minutes. Both the fixed 30-minute deadline and the learned threshold catch that night, which is the point worth noticing: on the incident they agree, and the difference between them shows up entirely on the quiet nights.
Self-studyThe three questions a freshness check can answer4 min▶
These get conflated constantly, and the conflation is what produces a monitor that is green while the dashboard is wrong.
- Did the job run? Answered by the orchestrator's own run history. Useful, and the cheapest of the three, and the one that tells you least about the data.
- Was the table written to? Answered by
max(updated_at)when that column is the row-write time. A full rebuild and an empty load both satisfy it. - Is the data current? Answered only by a timestamp that belongs to the source event, compared against the promise that was made to the reader. This is the question the stakeholder is asking, and the other two are proxies for it.
A monitor is worth what its question is worth. If you can only get the second one, ship the second one and write down which question it answers, so that the first time it is green during an incident nobody is surprised.
A deadline answers a different question
9 min live"Was it late?" is a yes-or-no question about a number that is not yes-or-no. Lateness is a series with a centre, a spread and almost always a tail, and once you plot it the threshold nearly picks itself.
LiveReading the shape before you pick a number3 min▶
Plot 30 nights of late_minutes before you write any threshold. There are three shapes you will see, and each one wants a different monitor.
- A tight cluster with a tail. Most nights sit within a few minutes of each other and one night in fifteen is much later because something retried. This is the common case, and a high trailing percentile handles it: the tail nights are inside the threshold, and the genuinely broken night is an order of magnitude past it.
- Genuinely bimodal. Two clumps, say around 8 minutes and around 40, with nothing in between. That is almost always two different jobs writing the table, or one job with two code paths. A single threshold on the combined series either pages on every slow-path night or is so wide it protects nothing. Split the series by whatever distinguishes the two modes and monitor each, or fix the pipeline so there is one path.
- A drifting centre. The median creeps up week over week. A trailing window follows the drift, which is what you want for a monitor and exactly what you do not want for capacity planning. Alert on the deviation, and report the trend separately to whoever owns the load.
The window length is a real decision. Fourteen nights is long enough to include a weekend and a slow night, and short enough that a deliberate change to the schedule washes through in two weeks. Shorter than about seven and the percentile is unstable enough to fire on its own noise. Much longer than a month and a threshold keeps defending a pipeline that no longer exists.
Self-studyWhy a percentile and not a mean plus standard deviations4 min▶
Mean plus three standard deviations is the reflex, and on lateness it behaves badly for a specific reason: the tail you are trying to tolerate is also in the history, and it inflates the standard deviation that sets your threshold. One 230-minute night in the trailing window and the mean-plus-3sd threshold jumps far enough to swallow the next real incident.
A high percentile is robust in the direction that matters. One extreme value moves a p95 by at most one rank position, so a threshold set from a trailing p95 barely notices an incident that has already happened. That is the property you want in a monitor, because your history will contain the incidents you are trying to detect.
| Threshold | Behaves well when | Fails when |
|---|---|---|
| Fixed clock time | The promise is contractual and the load is stable | The pipeline drifts, in either direction |
| Trailing percentile plus a pad | Lateness is a cluster with a tail | The series is bimodal, or you have under a week of history |
| Trailing mean plus 3 sd | The series is roughly symmetric with no tail | An incident is inside the window and widens the band |
| Median plus a multiple of MAD | You want robustness and have no seasonality | The weekly rhythm is strong, which for volume it usually is |
The pad matters as much as the percentile. A threshold of exactly p95 fires on roughly one night in twenty by construction, which is a page every three weeks for nothing. Adding a fixed pad in the units of the thing you are measuring, 10 minutes here, moves the monitor out of the noise without making it insensitive: night 9 at 230 minutes clears a 32-minute threshold by a factor of seven.
The two traps that make a freshness check lie
8 min liveBoth of these produce a green freshness check on a table that has held no new data for a week. Neither is exotic, both are the default behaviour of common pipeline shapes, and the fix for each is one line of thinking rather than a tool.
LiveTrap one: the full rebuild4 min▶
A table that is dropped and recreated every night, or written with a create or replace, has an updated_at that jumps to the current time on every load. That column now measures one thing: whether the job ran. If the source stopped producing events a week ago and the job faithfully rebuilds the same stale rows every night, the timestamp is perfect and the table is a week out of date.
The fix is to timestamp the source event rather than the row write, and to monitor the gap between the two. Most warehouse tables have a natural candidate already: an order time, a transaction time, an event time from the producing system.
On a healthy incremental table those two are minutes apart. On a full rebuild with a dead source, newest_write is now and newest_event is days ago, and the gap is the incident. Store the event-side value in the metrics table and monitor that one; keep the write-side value too, because the pair together tells you whether the problem is the source or the load.
The reason this trap survives review is that it fails safe on the day you build it. You point the check at max(updated_at), it returns a timestamp from twenty minutes ago, everything looks correct, and it stays correct until the first time the source dies without the pipeline dying with it. That is also the exact failure mode where nothing else alerts, because the pipeline succeeded.
LiveTrap two: the empty partition4 min▶
An empty load is still a load. In several warehouses a write of zero rows still updates the table's last-modified metadata, and in a partitioned table an empty partition can be created and registered with today's date. A freshness check reading table metadata therefore passes on a night when nothing arrived at all.
The defence is to make the freshness check assert on the data rather than on the container: count the rows in the window you expect, and treat zero as late rather than as fresh.
Note what this does to the shape of the monitor: the freshness condition becomes rows exist AND the newest event is recent enough, and the two clauses fail for different reasons. Report which one failed in the alert text, because "no rows at all" and "rows are three hours late" go to different people.
Self-studyFreshness on a table you do not own4 min▶
Plenty of the tables you depend on are landed by somebody else: a vendor extract, another team's pipeline, a replicated source. You cannot add an event timestamp, you cannot change the load, and you frequently cannot get the promise written down. The only lever you actually hold is the comparison window.
- Measure arrival, not content. If you cannot see inside the rows, record the moment the table first satisfies your own check each night and build the distribution out of that. A trailing p95 over your own observations is still a learned threshold, and it needs nothing from the owner.
- Widen the pad, not the percentile. On a table you do not control, more of the variance is genuinely outside anybody's management. A p95 with a generous pad pages rarely and still catches an order-of-magnitude miss, which is the class of failure you can actually escalate.
- Alert on your own consumers first. A page saying "the vendor extract is 4 hours late and the two dashboards that read it are showing yesterday" is actionable by you. A page saying "the vendor is late" is a ticket in somebody else's queue.
- Record the misses even when you cannot act. Thirty nights of measured lateness on a source you do not own is the single most useful artifact in the conversation where somebody asks for a service commitment from that source.
Do not put a hard gate on a source you cannot fix. Blocking your own downstream build when a vendor is late converts their lateness into your outage. Prefer a monitor plus a stale-data banner on the consumer, and keep the gate for sources whose owner is reachable.
The learned freshness monitor
★ 20 min · everyone buildsFour queries against the metrics table, then two runs of the same monitor at two different settings. Use your own table if you have one recording; otherwise the numbers below are Bellwether's, and the arithmetic works out to the same place.
Step 1. Turn the load timestamp into lateness in minutes. The metrics table stores max_updated_at; the monitor needs the gap between that and the promise. Bellwether's promise is 06:00 on the load date, so the promise is a function of the row, not a constant.
Session 1 already stored late_minutes directly, so this is a check rather than a computation. Run it anyway once: a mismatch here means the promise in your monitor and the promise in your metrics table disagree, and that is the kind of drift that makes a threshold quietly meaningless.
Step 2. Compute the trailing 14-night p95. A moving percentile is the one piece of arithmetic here that portable SQL makes awkward, because most warehouses accept percentile_cont as a window function but not with a moving frame. A self-join on the date range is portable, readable, and fast enough on a metrics table of a few thousand rows.
Use percentile_disc rather than percentile_cont if you want the threshold to be a minute value that actually occurred: disc picks a real observation by rank, cont interpolates between two. On the eight clean nights, 8, 14, 6, 19, 11, 22, 9 and 17, the rank-based p95 is 22 minutes.
Step 3. Write the monitor as p95 plus a pad. Save step 2 as a view called freshness_history so the monitor reads three columns and nothing else. Two guards then matter as much as the comparison: refuse to fire before you have enough history, and state the threshold in the output so the alert text carries its own evidence.
The message the monitor sends is part of the monitor. "landed 230 min late, trailing p95 is 22" carries the number, the comparison and the implied severity in eleven words, and it is the sentence the on-call needs before deciding whether to hold the dashboard.
Step 4. Run it against the eight clean nights. Threshold is 22 plus 10, so 32 minutes. The slowest clean night is 22. Nothing fires: zero false pages on eight nights. Compare that with the fixed 30-minute deadline, which also fires on nothing here, and note that the two are only equivalent for as long as the pipeline stays where it is today.
Step 5. Now run night 9. The table landed 230 minutes past the 06:00 promise. The trailing p95 is still 22, the threshold is still 32, and the monitor fires with landed 230 min late, trailing p95 is 22. Time the whole thing: from the check running to a human reading that sentence is the number that goes on the board, not the count of monitors you own.
Step 6. Break it on purpose: p75 and a 1-minute pad. Change two constants and rerun the same query over the same eight clean nights. The rank-based p75 of that set is 17 minutes, so the threshold becomes 18. Night 4 at 19 minutes fires. Night 6 at 22 minutes fires. Two false pages out of eight clean nights, for exactly the same one catch on night 9.
That p75-plus-1 setting is not a straw man; it is the hair-trigger freshness setting on the capstone bench, and it is what the reflex "tighten the thresholds" produces after an incident. Run every monitor at that kind of setting together and the bench scores 2 of 5 caught with 25 false pages in 30 nights, which is worse detection than having almost nothing switched on.
Step 7. Write down the evidence for the setting you kept. One line: the percentile, the window length, the pad, and the count of clean nights it would have paged on in your own history. That line is what makes the threshold reviewable in three months by somebody who was not there tonight.
Sanity check before you ship it. Replay the monitor over the history you already have and count its pages. A threshold you cannot backtest is a threshold you are about to discover in production, and the backtest costs one query on a table with one row per night.
Before session 3
30 min- Plot 30 nights of lateness for your table and name the shape: cluster with a tail, bimodal, or drifting. If it is bimodal, find out which two things write the table. That answer is usually more valuable than the monitor.
- Put the trailing p95 monitor into production on that one table with a pad you can defend, and backtest it over the history first. Record how many pages it would have sent.
- Check the full-rebuild trap on every table you own: is
updated_ata row-write time or a source-event time? Write the answer next to each table name. Most teams find at least one surprise. - Force an empty load on a non-critical table in a development schema and see whether your freshness check passes. Whatever it does, you now know rather than assume.
- For one table you depend on but do not own, start recording arrival time nightly. Thirty rows of measured lateness is the whole argument in the conversation about a service commitment.
Three questions
The eight clean nights landed 8, 14, 6, 19, 11, 22, 9 and 17 minutes past the promise, and the monitor is a rank-based trailing p95 plus a 10-minute pad. What is the threshold, and what happens on those eight nights?
A rank-based p95 of that set is 22, the pad adds 10, and the threshold is 32. Every clean night is inside it, so the monitor costs zero false pages while still catching night 9 at 230 minutes by a factor of seven.
A table is dropped and rebuilt every night. The source stopped producing events eight days ago and the rebuild has run cleanly every night since. What does a freshness check on max(updated_at) report?
The rebuild stamps every row with the current time, so the check confirms that the job ran and says nothing about whether the data is current. Timestamp the source event instead, and monitor the gap between the event time and the write time.
The same monitor is retuned to a rank-based p75 with a 1-minute pad. On those eight clean nights, what does it cost and what does it buy?
The rank-based p75 is 17, so the threshold is 18, and the 19-minute and 22-minute nights both page. Night 9 was already caught at the looser setting, so the tighter one buys nothing and spends two pages. Volume incidents are invisible to a freshness monitor at any setting.