Freshness SLAs over Run Metadata
Measure the age of the data, not the health of the job, against a per-pipeline freshness contract, and catch the green run that wrote nothing.
Freshness is a property of the data, not the job
The previous lesson made a promise: this product needs data no older than fifteen minutes. This lesson measures whether the promise is being kept, and the measurement is not the one most people reach for first.
You have met a freshness SLA twice already, and both times through a stand-in. Level 6 graded whether a run finished inside its SLA minutes and flagged in the prose that run duration was only a proxy for freshness. Level 8 measured a task SLA the same way and made the same admission, that a task can succeed and still miss. This lesson retires the proxy and measures the thing itself, because run duration cannot see the one failure it most needs to catch: a job that ran on schedule, exited green, and wrote nothing at all.
A green run tells you the process exited zero. It does not tell you the data is current. A pipeline can succeed every five minutes for an hour while writing nothing, because the upstream topic went quiet, a filter was tightened, or a credential expired in a way the job swallowed. That failure has a name and it is the one production teams miss most often: silent success, pipeline green but zero rows written. Nobody gets paged, because there is nothing to page on. The dashboard just stops moving.
So the measurement is on the data:
data age = now - (newest event time actually landed by the latest SUCCESSFUL run)
Two words in that line are load-bearing.
Event time, not completion time. completed_ms says when the process finished. max_event_time_ms says how new the newest record it committed actually was. A run that finishes at 18:00 having processed events up to 14:00 has landed four-hour-old data, and it is the second number the consumer feels.
Latest successful, not latest. A failed run still writes a row to the run log, and in this seed it also records the watermark it was attempting when it died. Take MAX(max_event_time_ms) across every run and you will report data that was never committed. The pick you want is the newest run whose status is success, which is a per-pipeline top-1 and therefore ROW_NUMBER.
A fair objection: why not MAX(max_event_time_ms) over just the successful runs, grouped by pipeline, and skip the window function? On data like this it returns the same numbers, and it has to, because every pipeline's successful watermarks climb. The two answers come apart the first time a reprocess lands an older window, which backfills do routinely. MAX then answers "the highest watermark this pipeline ever committed", while ROW_NUMBER answers "where the run that committed last actually got to". The second is the question your on-call is asking, so write the query that keeps meaning what you meant when the data stops being tidy.
The SLA turns a number into a verdict
Data age on its own is a number, not a problem. A freshness SLA is the per-pipeline contract that makes it a verdict: max_age_minutes per pipeline, joined onto the age, compared. This is what makes "we stream this one" and "hourly is fine for that one" the same measurable thing, and it is how the previous lesson's decision stops being a slide and starts being enforced.
The corollary catches people out in review: old is not the same as breaching. A dashboard pipeline whose newest data is four hours old is perfectly healthy when its SLA is a day. A fraud pipeline whose newest data is six minutes old is on fire when its SLA is five. Age without the contract beside it is a number nobody can act on.
| run | finished (min ago) | high-water mark (min ago) | rows_written | status |
|---|---|---|---|---|
| mlf-508 | 185 | 190 | 12400 | success |
| mlf-509 | 125 | 190 | 0 | success |
| mlf-510 | 65 | 70 | 12380 | success |
| mlf-511 | 5 | 70 | 0 | success |
Picking the latest successful run
ROW_NUMBER partitioned by pipeline and ordered by completed_ms descending numbers each pipeline's runs from newest to oldest; filtering to rn = 1 keeps exactly one row per pipeline. Filter to successful runs before the numbering, inside the CTE, so a failed newest run drops out instead of winning the partition.
This is the same top-N-per-group shape as picking a customer's latest order, and it is worth recognizing on sight because it appears in almost every SQL round.
Common mistake: computing age from completed_ms instead of max_event_time_ms. It reports every pipeline as fresh the moment a job finishes, which is exactly the blind spot that lets a silent success sit undetected for a day. If your freshness query cannot fail while every job is green, it is measuring the job, not the data.
Interview nuance: "how would you know your pipeline is actually delivering fresh data" is the operations follow-up to every pipeline-design answer, and the complete reply is three parts: age measured on event time, a per-pipeline SLA to compare it against, and a row-count check so a green run that wrote nothing cannot hide behind a healthy-looking job. Freshness is also the first of the five data-observability pillars, ahead of volume, schema, distribution, and lineage, so naming it first is naming the list in the order the field uses.
On a real platform this differs. The run log here is one table you can query. In production the same numbers come from your orchestrator's run history joined to a per-table watermark, from dbt's source freshness check (which compares a loaded-at column against warn and error thresholds you declare in YAML), or from an observability tool such as Monte Carlo that tracks freshness as its first pillar and alerts on the deviation rather than on a fixed number. What does not change is the arithmetic: now minus the newest event time actually committed, compared against a contract somebody signed.
CREATE TABLE stream_pipeline_runs (
pipeline TEXT,
run_id TEXT,
completed_ms INTEGER, -- when the run finished (processing time)
max_event_time_ms INTEGER, -- newest event time in the data this run landed (event time)
status TEXT, -- success | failed
rows_written INTEGER
);
INSERT INTO stream_pipeline_runs (pipeline, run_id, completed_ms, max_event_time_ms, status, rows_written) VALUES
('fraud_scoring', 'fs-2041', 1767289080000, 1767289020000, 'success', 4110),
('fraud_scoring', 'fs-2042', 1767289380000, 1767289020000, 'success', 0),
('fraud_scoring', 'fs-2043', 1767289680000, 1767289620000, 'success', 4260),
('fraud_scoring', 'fs-2044', 1767289980000, 1767289920000, 'success', 3890),
('fraud_scoring', 'fs-2045', 1767290280000, 1767290220000, 'success', 4120),
('inventory_sync', 'inv-770', 1767287700000, 1767287520000, 'success', 610),
('inventory_sync', 'inv-771', 1767288600000, 1767288420000, 'success', 655),
('inventory_sync', 'inv-772', 1767289380000, 1767289200000, 'success', 700),
('inventory_sync', 'inv-773', 1767290220000, 1767290100000, 'failed', 0),
('exec_dashboard', 'exec-311', 1767189900000, 1767189600000, 'success', 51200),
('exec_dashboard', 'exec-312', 1767218700000, 1767218400000, 'success', 49800),
('exec_dashboard', 'exec-313', 1767247500000, 1767247200000, 'success', 50400),
('exec_dashboard', 'exec-314', 1767276300000, 1767276000000, 'success', 48500),
('exec_dashboard', 'exec-315', 1767288600000, 1767288300000, 'failed', 0),
('ml_features', 'mlf-508', 1767279300000, 1767279000000, 'success', 12400),
('ml_features', 'mlf-509', 1767282900000, 1767279000000, 'success', 0),
('ml_features', 'mlf-510', 1767286500000, 1767286200000, 'success', 12380),
('ml_features', 'mlf-511', 1767290100000, 1767286200000, 'success', 0),
('support_routing', 'sup-1289', 1767283200000, 1767282900000, 'failed', 0),
('support_routing', 'sup-1290', 1767285000000, 1767284700000, 'success', 340),
('support_routing', 'sup-1291', 1767286800000, 1767286500000, 'success', 355),
('support_routing', 'sup-1292', 1767288600000, 1767288300000, 'success', 362),
('support_routing', 'sup-1293', 1767290010000, 1767289560000, 'success', 371);
CREATE TABLE freshness_slas (
pipeline TEXT,
max_age_minutes INTEGER -- the oldest this pipeline's data is allowed to be
);
INSERT INTO freshness_slas (pipeline, max_age_minutes) VALUES
('fraud_scoring', 5),
('inventory_sync', 15),
('support_routing', 30),
('ml_features', 60),
('exec_dashboard', 1440);-- The latest SUCCESSFUL run per pipeline. Filtering to success inside the CTE is what
-- keeps a failed newest run from winning its partition.
WITH ranked AS (
SELECT pipeline, run_id, completed_ms, max_event_time_ms, rows_written,
ROW_NUMBER() OVER (PARTITION BY pipeline ORDER BY completed_ms DESC) AS rn
FROM stream_pipeline_runs
WHERE status = 'success'
)
SELECT pipeline, run_id, completed_ms, max_event_time_ms, rows_written
FROM ranked
WHERE rn = 1
ORDER BY pipeline;Apply
Your turn
The task this lesson builds to.
Write a query that returns each pipeline's current data age in minutes and whether it breaches its SLA, as (pipeline, data_age_minutes, max_age_minutes, in_breach), oldest data first, over stream_pipeline_runs(pipeline, run_id, completed_ms, max_event_time_ms, status, rows_written) and freshness_slas(pipeline, max_age_minutes).
Use each pipeline's latest successful run and now = 1767290400000. Data age is (now - max_event_time_ms) / 60000 in whole minutes. Set in_breach to 1 when the age is strictly greater than max_age_minutes and 0 otherwise, ordered by data_age_minutes descending.
3 hints and 1 automated check are waiting in the workspace.
Practice
Make it stick
A second problem on the same idea, plus 2 bonus drills.
Write a query that returns the on-call freshness report, as (pipeline, max_age_minutes, data_age_minutes, in_breach, silent_success_runs), breaching pipelines first and oldest data first within each group, over stream_pipeline_runs and freshness_slas.
Every pipeline in freshness_slas appears exactly once. Data age uses each pipeline's latest successful run and now = 1767290400000, in whole minutes. in_breach is 1 when the age is strictly greater than max_age_minutes. silent_success_runs counts that pipeline's runs with status success and zero rows written, across its whole history, and is 0 when there are none.
1 automated check is waiting in the workspace.