The Freshness SLO
Now minus the event time of the newest complete data. The word "complete" does all the work: a table holding a few hours of today is extremely fresh and completely wrong to aggregate.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
A consumer asks how fresh this table is. What number do you give them, and what has to be true for that number to mean anything?
The operational dashboard refreshed every few minutes, the alerting rule that fires on today's volume, and the analyst who filters to "last 7 days" without checking whether today is finished. The third one is the dangerous case: they are not asking about freshness at all, and the freshness answer is what protects them (Stale Dashboards).
The unit is one dataset and one time semantic. Freshness of fct_orders measured on order_placed_at is a different number from freshness of the same table measured on loaded_at, and both are legitimate answers to different questions. A freshness figure with no stated time column is not a figure (Event Time).
Report now() - max(loaded_at) on the table. One query, no state, no configuration, and it correctly detects the case everyone worries about: a pipeline that stopped running.
The hourly load for 09:00 is still in progress and has written a third of its rows. max(loaded_at) is seconds old, freshness reads as excellent, and any aggregate over today is a third of the truth (Atomic Publish).
- The hourly load for 09:00 is still in progress and has written a third of its rows.
max(loaded_at)is seconds old, freshness reads as excellent, and any aggregate over today is a third of the truth (Atomic Publish). - A backfill re-loads January. Every row in the table now has a
loaded_atof this morning, so a dataset whose newest business event is eight months old reports as perfectly fresh (Backfills). - The source stopped producing at 02:00 and the pipeline kept running, loading zero rows every hour and updating nothing.
max(loaded_at)freezes at 02:00 and reads correctly as stale — but only by accident, and the same query would have read fresh if the pipeline had written an empty marker row. - The pipeline writes a row per run to a metadata table and the freshness check reads that instead of the data. The metadata is written first and the load fails afterwards, so the check reports fresh data that does not exist (The Pipeline Succeeded. The Data Is Wrong.).
- A late-arriving event for yesterday lands today.
max(event_time)is unchanged,max(loaded_at)jumps, and neither answers the question a consumer asked, which was whether yesterday is finished (Late-Arriving Data). - The platform reports a single average freshness across two hundred datasets. The one that matters is nine hours behind and the average is comfortable, because a hundred and ninety-nine streaming tables are seconds behind (Pipeline SLOs).
What is actually happening
- Freshness is a subtraction with three variables, and every argument about it is really an argument about one of them: now, the time semantic, and the completeness rule that decides which data counts as data (Processing Time).
- The time semantic decides what is being measured. Event time answers "how far behind the world are we"; ingestion time answers "how far behind the source are we"; load time answers "when did we last write", which is a statement about the pipeline rather than about the data (Ingestion Time).
- The completeness rule is what turns a number into a promise. Without it, freshness measures the newest row present, and the newest row present is a partially-loaded period whose aggregates are wrong by an amount that shrinks as the load finishes (Atomic Publish).
- The three usable completeness rules are: a completion marker written by the publish step for a period, which is exact and requires the pipeline to write it; a high-water mark the pipeline advances only after a unit is fully published (The High-Water Mark); and in streaming, a watermark — an estimate rather than a fact, and one that stalls when the source goes quiet (Watermarks).
- Freshness composes along the chain by taking the minimum completeness across inputs, not the maximum and not the average. A mart built from a fresh table and a stale one is exactly as fresh as the stale one, and reporting its own load time hides that entirely (Model Layering).
- A freshness objective is therefore an objective on the whole upstream chain, whether or not anybody upstream agreed to it. That is the property that makes freshness the most frequently breached objective in a data platform (Data Lineage).
- Measuring freshness during a quiet period is genuinely ambiguous: no new data and no missing data look identical from the served side. The only resolution is a source-side expectation — "this source produces something every hour" — which is a statement about the business, not about the pipeline (Volume Anomalies).
The word doing all the work is "complete"
Three queries, three numbers, three completely different claims — and only one of them is safe to publish as a freshness objective. The difference between them is not precision. It is which question they answer, and two of the three answer a question nobody asked.
max(loaded_at) answers "when did we last write to this table". It is a statement about the pipeline. It is the figure most platforms publish, and a backfill turns it into a lie the size of the backfill range: reload January today and a dataset whose newest business event is eight months old will report itself as seconds fresh.
max(event_time) answers "what is the newest thing we have". Better, and still wrong for aggregation, because the newest thing you have is inside a period that is still loading. A consumer who sums today from a table reporting two minutes of freshness gets whatever fraction happened to have landed, and there is no error, no warning and no way for them to tell.
The third query answers the question the consumer actually meant: how far back do I have to go before the data is finished? It requires the pipeline to have said so, which is why the completeness boundary must be written by the publish step rather than inferred from the rows (Atomic Publish).
Report `now() - max(event_time)`. During the 09:00 load the newest row is seconds old, so freshness reads as excellent from the moment the first record lands until the load finishes.
Report `now() - complete_through`, where `complete_through` is advanced to the end of the 08:00 hour and stays there until the 09:00 load has published and validated. Freshness reads as up to an hour, which is the truth.
A consumer aggregates over what the table contains, not over what the table has most recently been sent. During a partial load the table contains an arbitrary fraction of the current period, so any figure derived from the newest row present describes the arrival of data rather than the availability of an answer — and it is optimistic in exactly the window where being wrong costs the most.
1-- 1. Pipeline liveness, not freshness. A backfill makes this seconds old2-- while the newest business event in the table is eight months old.3SELECT now() - max(loaded_at) AS since_last_write4FROM analytics.fct_orders;5 6-- 2. Newest data present. Ignores whether the period it sits in is finished,7-- so it reads "fresh" throughout a partial load of the current hour.8SELECT now() - max(order_placed_at) AS since_newest_row9FROM analytics.fct_orders;10 11-- 3. Freshness as a promise: now minus the newest COMPLETE unit.12-- complete_through is advanced by the publish step, in the same13-- transaction as the swap, only after validation passed.14SELECT now() - complete_through AS freshness15FROM analytics.dataset_boundary16WHERE dataset = 'analytics.fct_orders';17 18-- ...and the affordance that prevents the wrong number in the first place:19-- a consumer can exclude the unfinished period without knowing any of this.20SELECT order_date, sum(amount_minor)21FROM analytics.fct_orders22WHERE order_placed_at < (23 SELECT complete_through FROM analytics.dataset_boundary24 WHERE dataset = 'analytics.fct_orders'25)26GROUP BY order_date;Query 3 depends on something query 1 and query 2 do not: the pipeline having declared completeness. That declaration is the entire difference between a freshness metric and a freshness objective, and it cannot be added by the monitoring layer after the fact.
Freshness is inherited, and only the bad half
A derived dataset cannot be fresher than its inputs. That sounds obvious until you notice that a mart computing its own freshness from its own load time reports a number that has no relationship to its inputs at all — it will happily claim two minutes while one of its five sources has not updated since Tuesday.
Composition takes the minimum completeness across inputs, then adds the derived dataset's own run interval. There is no averaging, and the arithmetic runs in the unhelpful direction: staleness is inherited, freshness is not. One neglected upstream source sets the ceiling for everything downstream of it, however much attention the downstream models get.
This is why a freshness objective on a leaf dataset is implicitly an objective on every node above it, and why the most common cause of a breached freshness objective is a dataset owned by someone who never agreed to one. The chain below is the diagnosis you would otherwise perform by hand at three in the morning (Lineage Debugging).
- Source: orders service database
holds The committed truth, available at commit. This is the only point in the chain with no freshness gap at all.
could corrupt A transaction committing after its
updated_atwas assigned, so an incremental extract keyed on that column misses it permanently — a completeness failure that no freshness figure detects (Incremental Extraction).↑ reads from - CDC / extract
holds Changes up to the position it has read. Its own lag is the first term in every downstream freshness figure.
could corrupt Falling behind the source log's retention, at which point the gap is not late data but missing data, and the boundary advances over a hole (CDC Failure Modes and the Retention Deadline).
↑ reads from - Raw landing zone
holds What arrived, untouched, partitioned by arrival. Complete only for periods whose landing has been closed.
could corrupt A partition declared closed while a producer is still writing to it, which freezes an incomplete period as permanently complete (The Raw Landing Zone).
↑ reads from - Staging model
holds Typed, deduplicated rows for periods it has processed. Its boundary is the raw boundary minus its own lag.
could corrupt Advancing its boundary on run completion rather than on the raw boundary it actually consumed, inventing completeness it did not inherit (The High-Water Mark).
↑ reads from - fct_orders
holds Modelled facts at order grain, complete through the minimum of its inputs' boundaries.
could corrupt Joining to a dimension whose own boundary is behind, producing rows that are complete on the fact side and unmatched on the dimension side (Dimension Tables).
↑ reads from - Revenue mart
holds Pre-aggregated revenue by day and country, complete through the minimum across every model it reads.
could corrupt Reporting its own build time as freshness, hiding a stale input entirely — the single most common freshness lie in a warehouse (Data Marts).
↑ reads from - Dashboard
holds A number and a "last refreshed" caption that describes the BI tool's cache, not the data.
could corrupt Displaying a cache timestamp next to a figure computed from data hours older, which is worse than showing nothing because it manufactures confidence (Stale Dashboards).
Read the chain to find the minimum: the dashboard is exactly as complete as the least complete node above it, plus its own refresh interval. Every node that reports its own run time instead of its inherited boundary breaks the chain at that point and makes everything below it unanalysable.
Deciding what "complete" means for this dataset
Completeness is a definition, not a measurement, and it is the definition that has to be argued about. For a batch dataset it is usually "the period has published and validated". For a streaming dataset it is "the watermark has passed", which is a belief. For a dataset fed by a source with a long settlement tail, neither is satisfactory and the honest answer involves a stated grace period and an acknowledgement that late corrections will follow.
The choice interacts with the objective. A conservative completeness rule makes the published freshness figure worse and makes it true. A liberal rule makes it look better and transfers the risk to consumers, who will discover it by aggregating a partial period and reporting a number to somebody senior.
The one option that is never acceptable is leaving it undefined. An undefined completeness rule means every consumer picks their own, and they will pick "whatever is in the table", which is the failure this whole lesson exists to prevent (Data Contracts).
A consumer needs to know how far back the data is finished. What is allowed to move that line forward?
when Batch or micro-batch datasets with a clear period. The default answer for anything in a warehouse.
cost The pipeline must write it, in the same transaction as the publish, and every alternative write path — a backfill, a manual fix, a second job — must write it too or the boundary drifts from reality (Atomic Publish).
when Incremental pipelines where the unit is a range rather than a calendar period.
cost Advancing it on run success rather than on validated publish is a one-line mistake that makes the boundary meaningless, and nothing about the code makes the difference visible (The High-Water Mark).
when Continuous processing where there is no period to close and progress is estimated from event times.
cost It is a belief, not a fact: late data past it is excluded, and it freezes entirely when the source goes idle, so a flat freshness line can mean either perfect health or a total stall (Watermarks).
when A source with a known settlement tail and no way to signal completeness — a partner feed, a payment processor, a device fleet.
cost A guess dressed as a rule. It excludes the tail beyond the grace period silently unless a late-arrival counter is kept, and the tail changes as the source population changes (Late-Arriving Data).
when Financial or regulatory datasets where a wrong aggregate is unacceptable and the source can be counted.
cost The strongest rule and the slowest: the boundary cannot advance until the source is queried and agrees, which adds the reconciliation interval to every consumer's freshness (Reconciliation).
Table formats and warehouses increasingly expose snapshot or commit timestamps and per-partition metadata that make a cheap boundary read possible without a scan, and what is available has changed substantially across recent versions of each. Check what your table format exposes before building a boundary table by hand — and confirm what its timestamp actually means, since several of them are write times rather than data times.
How to build it
Most important first.
- Define freshness as now minus the event time of the newest complete unit, and write down what "complete" means for that dataset before writing the query. Everything else in this lesson is a consequence of that one sentence (Freshness Checks).
- Have the publish step write completeness explicitly — a marker row, a manifest entry, a high-water mark advanced in the same transaction as the swap. Inferring completeness from the data is guesswork that fails during exactly the incident you built the check for (Atomic Publish).
- Measure from the served table, with a query a consumer could run themselves. A freshness figure computed from orchestrator metadata reports your intentions (Freshness Monitoring).
- Publish freshness per dataset, next to the dataset, in the catalog entry. A platform average is arithmetic performed on incomparable things and no consumer can act on it (The Data Catalog).
- Expose the completeness boundary to consumers as a queryable value, not only as an alert. An analyst who can filter
WHERE event_date <= (SELECT complete_through FROM ...)never accidentally aggregates a partial today, and that single affordance prevents more wrong numbers than the alert does. - Set the objective from the decision, then check that the architecture can meet it before promising. A fifteen-minute bound on a dataset produced by an hourly batch is not an objective, it is a re-architecture request in disguise (Batch vs Streaming Ingestion).
- Handle the quiet-period case deliberately: either declare an expected minimum arrival rate per source and alert on its absence, or state that freshness is undefined during a stated window and stop paging on Sunday mornings (Alert Fatigue: The Page Nobody Reads).
- Alert on the *chain*, not only the leaf. When a mart breaches, the useful page names the upstream dataset that caused it, which requires the lineage edge to exist (Lineage Debugging).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- A freshness figure guarantees that data older than the boundary is present and complete under the stated rule. It guarantees nothing about data newer than the boundary, which may be partially present — and usually is (Incremental Processing).
- It guarantees nothing about correctness. The most reliably fresh dataset in a platform can be systematically wrong, and freshness monitoring will report it as healthy every minute of it (Data Quality).
- Composed freshness for a derived dataset is bounded by its slowest input. That is a genuine guarantee and it runs in the unhelpful direction: you inherit staleness and you do not inherit freshness (Model Layering).
- In streaming, the boundary is a watermark and therefore a belief rather than a fact. Late data past it exists and is excluded, and no freshness figure reveals how much (Late Events).
- What is explicitly not guaranteed: that a stale reading means a broken pipeline. A quiet source and a stopped pipeline are indistinguishable from the served side (Volume Anomalies).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- Assert that the completeness boundary never moves backwards, and that it never moves forward past a period whose validation has not passed. Both are cheap and both catch a class of bug that otherwise publishes silently (Data Tests).
- Cross-check the freshness figure against a row count for the newest complete period. Freshness plus volume together distinguish "the load ran and was empty" from "the load has not run", which freshness alone cannot (Volume Anomalies).
- Both miss the case where the boundary is advanced correctly over data that is complete and wrong — the aggregate is finished, present, timely, and computed from a broken join (The Pipeline Succeeded. The Data Is Wrong.).
- They also miss the measurement failing open. A freshness check that errors and is treated as passing is worse than no check, because it produces confidence rather than absence (Quality Alerting).
- This lesson is the measurement of freshness rather than a contributor to it, but the measurement has a latency shape of its own: an evaluation every N minutes means a breach is detected up to N minutes after it starts, and that interval is part of the promise whether or not it is written down.
- A completion marker makes freshness a step function — it jumps by a whole period when a load publishes, and is flat in between. Consumers who expect a smooth number find this alarming until it is explained, and it is the correct shape for a batch dataset (Atomic Publish).
- A streaming freshness figure is continuous and jitters with the watermark, which stalls when the source goes quiet. A flat streaming freshness line is a stronger signal of a stall than a rising one is of lag (Watermarks).
- End-to-end freshness for a consumer is the sum of every hop plus the schedule interval of the slowest one, so shaving the last stage of a chain moves the number very little (The Data Loop).
- Changing the time semantic — event time to ingestion time, or a switch of source column — silently redefines the series. Attainment before and after are not comparable, and nothing in the schema records that they are not (Semantic Changes).
- Tightening the completeness rule usually reveals the dataset was never as fresh as reported. This is a good outcome and it looks exactly like a regression on the dashboard, so it needs announcing before it is deployed (Data Contracts).
- Adding an upstream input to a derived dataset changes its composed freshness immediately, because the minimum now includes the new one. A new join is a freshness change with no schema change to notice it by (Impact Analysis).
- Moving a dataset from batch to streaming changes freshness from a step function to a continuous estimate, which changes what a breach means and usually invalidates the existing alert threshold (Batch and Streaming Unification).
- Recovery from a freshness breach is a catch-up run, and catch-up must respect the completeness rule: publish whole units and advance the boundary once each is complete, rather than advancing it to the target and filling in behind (Idempotent Data Pipelines).
- After a long stall, resist the temptation to advance the boundary to now and backfill quietly. Consumers who read during the gap made decisions on stale data and are entitled to know the gap existed (Data Incidents).
- A backfill must not advance the freshness boundary at all — it repairs history behind the boundary. If your freshness query is written on load time rather than event time, every backfill lies about freshness for as long as it runs (Backfills).
- When the completeness marker itself is lost, recompute it from the data with an explicitly conservative rule and record that it was recomputed. A boundary of unknown provenance is worse than a boundary that is known to be pessimistic (Metadata: Technical, Operational and Business).
What can go wrong
- Freshness measured on load time, so a backfill reports an eight-month-old dataset as seconds fresh (Backfills).
- Freshness measured on the newest row rather than the newest complete unit, so a partially-loaded period reads as fresh and every aggregate over it is short (Atomic Publish).
- The check reading pipeline metadata rather than the served table, reporting on a load that did not happen.
- A platform average that is comfortable while the one dataset the business runs on is nine hours behind (Pipeline SLOs).
- Alerting through a quiet period — a weekend, a holiday, a seasonal lull — until the team mutes the alert and does not unmute it (Alert Fatigue: The Page Nobody Reads).
- The mitigation failing: a completion marker written before the load rather than after, converting a good mechanism into a confident lie (Atomic Publish).
- A composed dataset reporting its own load time, hiding the fact that one of its five inputs has not updated since Tuesday (Lineage Debugging).
- "The table was updated a minute ago, so it is fresh." It was *written* a minute ago. If the write was a backfill of January, or a partial load of the current hour, the newest complete business data may be months or hours old (Backfills).
- "Freshness is a platform metric." It is a per-dataset property and averaging it across datasets produces a number with no consumer and no meaning. Publish it per dataset or do not publish it (Pipeline SLOs).
- "Fresher is better." Fresher costs compute continuously and buys nothing unless a decision changes as a result. The right freshness is set by the decision the data drives, not by what the stack can achieve (Cost vs Freshness).
- "Freshness monitoring means we would notice a data problem." It notices absence and staleness. Every wrong-value failure — a broken join, a filter that dropped a category, a redefined metric — arrives exactly on time (Data Observability).
- "The freshness alert is quiet, so the pipeline is running." A quiet source and a stopped pipeline look the same from the served side. Without an expected arrival rate, silence is not evidence (Volume Anomalies).
Operating it
- Per dataset: current freshness, the completeness boundary as a timestamp, and the objective. Three values on one row, published where consumers read the dataset rather than only in the platform's own dashboard (The Data Catalog).
- The freshness series over time, not just the current value. The shape distinguishes a slow degradation from a hard stop, and only the series shows a boundary that has stopped advancing while the value keeps looking plausible (Freshness Monitoring).
- Freshness of each upstream input on the same axis as the derived dataset, which turns "the mart is stale" into "the mart is stale because this input is" without a manual walk (Data Lineage).
- Row count for the newest complete unit alongside freshness, because the two together identify the empty-load case that neither identifies alone (Volume Anomalies).
- For streaming datasets, watermark lag as well as consumer lag. A caught-up consumer with a frozen watermark is a stalled dataset reporting healthy on every conventional signal (Watermarks).
- At 10x datasets, per-dataset freshness must be generated from a declared property rather than hand-written, and the catalog becomes the place it is published (The Data Catalog).
- At 100x, the check queries themselves become a workload. Reading from table metadata or from a maintained boundary table rather than scanning becomes a requirement rather than an optimisation (Metadata: Technical, Operational and Business).
- At 10x chain depth, composed freshness is dominated by whichever input is slowest, and the leaf's objective becomes unachievable without an objective on the root. This is the point at which freshness stops being a monitoring topic and becomes a contract topic (Data Contracts).
- Nothing changes with row volume. Freshness is a property of timestamps and schedules, and a table with a thousand rows and a table with a trillion have the same freshness mechanics (Physical Data Layout).
- The check itself should be a metadata read or a small predicate-pruned query. Written naively as a scan for the maximum of an unpartitioned timestamp column, it becomes a full table scan running every few minutes, which is a genuinely common and entirely avoidable line item (Scan Cost).
- Meeting a tighter freshness objective costs run frequency, and run frequency costs fixed overhead per run — planning, container start, small output files — that does not shrink as the interval does (Compute Waste).
- Beyond a point, tightening moves the architecture from scheduled batch to a continuously running consumer, which is a step change in cost rather than an increment (Cost vs Freshness).
- The freshness series is tiny and worth retaining indefinitely; it is the only record of what the platform used to promise (Pipeline Metrics).
- A strict completeness rule buys a number consumers can aggregate against safely and costs apparent freshness — the honest figure is always worse than the naive one, and it will be questioned.
- Publishing the boundary as a queryable value buys consumers a way to exclude partial periods themselves and costs an extra object to maintain and document, plus consumers who will use it inconsistently.
- A tight objective buys timely decisions and costs run frequency, per-run overhead and, past a point, a different architecture. The freshness a platform can technically achieve is almost never the freshness it should promise (Cost vs Freshness).
- Per-dataset publication buys actionability and costs a hundred small obligations. The mitigation is a tiered default — a real objective for the datasets that drive decisions, an explicit best-effort statement for the rest (Data Products).
Dataset review questions
This lesson uses the shared review exercise.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe definition — now minus the event time of the newest complete unit — is independent of stack, because it follows from what a consumer means when they ask whether they can aggregate today; what differs between platforms is only how cheaply the completeness boundary can be read.
- ENGINE-SPECIFICIn a batch platform the boundary is a marker or high-water mark the pipeline writes, so it is exact and only as trustworthy as the publish step; in a streaming platform it is a watermark, which is an estimate derived from observed event times and freezes when the source goes idle, so the same figure means "known complete" in one and "believed complete" in the other.
- WAREHOUSE-SPECIFICSeveral warehouses and table formats expose a table-level last-modified or snapshot timestamp in metadata, which is cheap to read and answers the load-time question rather than the event-time one; using it as the freshness figure is exactly the substitution this lesson argues against, but it remains the right source for detecting a pipeline that has stopped writing at all.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns the alerting policy this feeds — burn-rate windows, page-versus-ticket thresholds, and the rotation that receives a freshness page at three in the morning for a dataset nobody reads until nine.
- — Distributed Systems owns why the quiet-source case is undecidable from the served side: a receiver cannot distinguish a silent sender from a slow one, so "no new data" and "no data arriving" require an out-of-band expectation to tell apart.