Freshness Checks
Expected latest data versus actual latest data — the cheapest check in the toolkit, two clocks that get confused, and the days it fires for no reason.
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.
How old is the newest data in this table, how old should it be, and which of those two questions is the dashboard actually answering?
Anyone acting on a number today: an operations team watching orders, an on-call engineer checking whether a spike is real, a finance analyst who needs to know whether yesterday is final. All of them need to know whether they are looking at current data or at a confident rendering of last Tuesday.
Freshness is a property of one dataset and one clock. "The warehouse is fresh" is not a claim; "fct_orders contains every order up to 04:00 today and was last written at 04:12" is two claims, both checkable, about the same table (The Freshness SLO).
Check that the pipeline ran. If the DAG completed at its scheduled time, the data must be current, so the orchestrator's success timestamp is the freshness signal. This works exactly as long as a successful run always produces new data — which is most days, which is why the assumption survives.
The run succeeded and wrote zero new rows because the upstream extract returned an empty window. The table's load timestamp is minutes old and its newest record is from yesterday (Missing Rows).
- The run succeeded and wrote zero new rows because the upstream extract returned an empty window. The table's load timestamp is minutes old and its newest record is from yesterday (Missing Rows).
- The transformation crashed and nothing was published. The serving table still holds the previous period and renders it with complete confidence — the loud failure produces the silent symptom (Stale Dashboards).
- The pipeline is fine and the *source* stopped. Nothing in your platform failed, freshness by load time is perfect, and the newest event is hours old (CDC Failure Modes and the Retention Deadline).
- A mart derives from a model that derives from raw. Each layer is within its own schedule and the consumer at the end is three intervals behind, which nobody computed because each hop reported itself healthy (Data Marts).
- The check fires every Sunday because the source genuinely produces nothing overnight at the weekend. After three weekends the alert is muted, and the following Wednesday's real outage is muted with it (Alert Fatigue: The Page Nobody Reads).
What is actually happening
- There are two clocks and they answer different questions. Load freshness is now minus the time the table was last written; it tells you whether the pipeline is moving. Data freshness is now minus the maximum event time in the table; it tells you how far the data has caught up with the world.
- They diverge in exactly the interesting cases. Load fresh and data stale means the pipeline ran and had nothing to carry — a source problem. Load stale and data fresh is impossible unless somebody wrote the table by hand. Both stale means the pipeline stopped (Pipeline Observability).
- The expected side of the comparison comes from the schedule plus the pipeline's own latency, not from a wish. A table built hourly from a source with a fifteen-minute capture delay cannot be fresher than that, and an SLO that ignores the arithmetic will be missed permanently (SLOs: A Target, a Window, and a Reason).
- Freshness is the only quality signal that can be computed without reading the data — a maximum over an indexed or partitioned timestamp column, or metadata the table format already maintains. That is why it is the cheapest check available and why there is no excuse for not having it (Open Table Formats).
- It is also the check with the most legitimate false alarms, because "no new data" and "no data arrived" are the same observation. Distinguishing them requires knowing whether the source was supposed to produce anything, which is business knowledge the check does not have (Who Owns Data Quality).
Two clocks, and the failure that lives between them
Every freshness conversation gets easier once the two clocks are separated. The load clock says when the table was last written. The data clock says how recent the newest record in it is. A healthy pipeline keeps them close; the gap between them is a diagnostic.
The important case is load-fresh and data-stale: the pipeline ran, on time, successfully, and carried nothing. Everything the orchestrator can see is green, and the newest order in the table is from yesterday. This is the single most common way a data platform reports health while a source is down (CDC Failure Modes and the Retention Deadline).
The query below returns both, plus the expected threshold, in one row per dataset. It is deliberately trivial — that is the argument. This is the cheapest check in the module and the one most often missing, usually because the orchestrator's success timestamp was assumed to be the same thing.
1SELECT2 'fct_orders' AS dataset,3 -- Data clock: how far the contents have caught up with the world.4 MAX(order_ts) AS newest_event,5 NOW() - MAX(order_ts) AS data_age,6 -- Load clock: when the pipeline last wrote anything here.7 MAX(loaded_at) AS last_written,8 NOW() - MAX(loaded_at) AS load_age,9 -- The expectation, derived from the schedule and known capture lag.10 INTERVAL '90 minutes' AS data_age_slo,11 NOW() - MAX(order_ts) > INTERVAL '90 minutes' AS data_stale,12 NOW() - MAX(loaded_at) > INTERVAL '90 minutes' AS pipeline_stalled13FROM fct_orders14WHERE order_date >= CURRENT_DATE - 2;Read the last two columns together. pipeline_stalled false with data_stale true is the diagnostic pair: your pipeline is healthy and your source is not. The interval is illustrative — the real one is derived from the schedule plus the capture lag, and should be written down where the dataset is declared.
Expected latest versus actual latest
The check is a comparison between two timestamps: the newest record you should have by now, and the newest record you do have. The first is not a measurement — it is derived from the schedule, the capture latency and the source's own behaviour, and deriving it is where the thinking happens.
The timeline below is a teaching clock rather than a measurement. A batch runs at 04:00 and covers everything that arrived before it; the expected newest event at 04:00 is therefore the newest event that had arrived by 04:00, not the newest event that had *happened* by then. Records that happened inside the period and arrived after the run are a completeness problem, not a freshness one, and a freshness check will report them as perfectly fine (Late-Arriving Data).
That distinction is the practical value of separating the clocks. A freshness check answers "has anything recent arrived". It does not and cannot answer "has everything that happened arrived", and treating a green freshness tile as an answer to the second question is one of the most common misreadings on a quality dashboard.
| Event | Happened | Arrived | Lands in |
|---|---|---|---|
| A | 01:12 | 01:14 | Period under test The normal case: a small capture delay, inside the period, present at run time. |
| B | 03:52 | 03:58 | Period under test Close to the boundary and still in time. This record is what the freshness check will see as the newest event. |
| C | 03:55 | 05:40 | Next period Happened inside the period, arrived after the run. Freshness is unaffected and the period is quietly incomplete — a completeness failure wearing no symptom. |
| D | 04:02 | 04:03 | Next period An early record of the next period. If freshness is measured as a bare maximum, this one row makes the table look current while the period it belongs to is nearly empty. |
Events C and D are the two ways a bare maximum misleads. C is missing and invisible to freshness; D is present and makes an empty period look fresh. Measuring freshness against the newest *complete* period removes both.
Three freshness checks, three blind spots
There is no single freshness check, and the differences between the variants are exactly the differences that matter during an incident. Each one is cheap; running all three is still cheap, and their blind spots barely overlap.
The third variant is the one almost nobody implements and the one that catches a genuinely nasty failure: a pipeline that runs, writes, and writes the same thing every time. Load time advances, data time does not move, and if only the load clock is watched everything looks perfect indefinitely.
Note the last column throughout. Every one of these checks is satisfied by a table that is current, complete and entirely wrong, which is why freshness is a gate rather than a verdict (Data Quality).
| Check | Expresses | Catches | Still misses |
|---|---|---|---|
| Load freshness: now minus the last write time | The pipeline is running and producing output on its schedule. | A crashed job, a stuck scheduler, an orchestrator that never triggered, a run blocked by a failed upstream dependency (When a Task Fails Mid-DAG). | A run that succeeded and carried nothing. This is the failure mode the check most looks like it covers and specifically does not. |
| Data freshness: now minus the maximum event time | The contents have caught up with the world to within the stated target. | A source that stopped, a capture connector falling behind, an extract window that closed early (The High-Water Mark). | An empty period certified by one early-arriving record from the next one, and every record that happened in the period and has not arrived yet. |
| Period completeness: newest period with a plausible row count | The most recent period is not merely present but populated the way a period like it usually is. | A partial load, an early close, a source that produced a fraction of its normal volume, and the single-row case the bare maximum misses (Distribution Tests). | A period with the right number of rows and the wrong values — and it needs a history to compare against, so it does not protect a new dataset. |
| Change freshness: time since any row actually changed | The pipeline is producing new information, not rewriting the same period repeatedly. | A stuck high-water mark that keeps re-reading the same window; a source snapshot that is being re-loaded unchanged (Incremental Processing). | A legitimately unchanged period, which makes this a warn-level signal in low-traffic datasets rather than a page. |
The first two are the common pair and the third is the one that catches the single-row certification. All four are metadata-cheap, and all four are silent about whether the values are right.
How to build it
Most important first.
- Publish both clocks per serving dataset. One number hides the failure where the pipeline is healthy and the source stopped, which is the case most likely to be misattributed to the data team.
- Set the expected threshold from the schedule plus known pipeline latency plus a margin, and write down the arithmetic. An SLO nobody can derive is an SLO nobody will defend when it is missed (The Freshness SLO).
- Measure freshness against the newest complete period rather than the newest row. A single early-arriving record from the next hour makes a table look fresh while the hour it belongs to is empty (Late-Arriving Data).
- Encode the expected quiet periods — overnight, weekends, holidays — rather than muting the alert during them. A muted alert is coverage you have lost and cannot see that you have lost.
- Propagate expectations along the lineage: a mart's freshness target cannot be tighter than its parent's, and a platform that lets consumers request otherwise is promising something arithmetically impossible (Data Lineage).
- Emit freshness as a continuously recorded series, not only as an alert. The shape of the series before a breach is what tells you whether the pipeline is degrading or fell over (Pipeline Metrics).
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 passing freshness check guarantees that a record with a recent timestamp exists in the table. It guarantees nothing about whether the records are correct, complete for their period, or unique (Data Quality).
- It guarantees nothing about the period the newest record belongs to. Freshness measured on maximum event time is satisfied by one row, and one row is not a period (Atomic Publish).
- A freshness SLO is a commitment about the pipeline's behaviour, not about the source's. If the source stops, the SLO is missed and no amount of pipeline engineering would have prevented it — which is why the SLO must name its dependency (Error Budgets: Unreliability You Are Allowed to Spend).
- Freshness says nothing about whether the newly loaded rows are *different* from the ones already there. A pipeline re-writing the same period on every run is perfectly fresh and completely stuck.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The check on this check is to stop the pipeline deliberately in a staging environment and confirm the alert fires within the expected window. A freshness alert that has never been observed to fire is a configuration nobody has validated.
- Also assert that the freshness metric is still being emitted. A missing series and a healthy series look identical on most dashboards, and the missing case is the one that matters (The Data Quality Dashboard).
- It misses a table that is fresh, complete and wrong, which is the majority of serious data incidents. Freshness is a necessary signal and a very weak one on its own (Reconciliation).
- This lesson is the measurement of the domain's freshness field itself, so the shape is direct: the check adds essentially no latency, because it reads a maximum rather than the data.
- Where it does cost latency is in the *complete period* form. Waiting for a period to close before calling it fresh trades detection speed for precision, and it is usually the right trade for anything a consumer reports on.
- End-to-end freshness experienced by a consumer is the sum of every hop plus the coarsest schedule in the chain. Publishing per-hop freshness and calling the fastest one "the platform's freshness" misleads every consumer who hears it (The Fundamental Data Journey).
- Changing the schedule changes the SLO, and the two are routinely changed independently. Any schedule change should require the freshness threshold to be re-derived in the same commit (Orchestration).
- Adding a layer between the model and the consumer silently degrades the consumer's freshness by one interval. That is a contract change and deserves the same announcement as a schema change (Data Contracts).
- A source moving from batch to streaming ingestion improves data freshness and usually leaves the downstream schedule untouched, so the consumer sees no improvement and the pipeline owner believes they delivered one (Batch vs Streaming Ingestion).
- A stale table recovers by running the pipeline, which is the easiest recovery in the module — provided the source still has the data. Retention on the intermediate hop is the real constraint (Retention and Replay).
- If staleness was caused by a failed publish, the previous partition is still correct and the recovery is a re-run rather than a repair. This is the argument for failing closed: stale is recoverable, wrong is not (Atomic Publish).
- If the source stopped, there is nothing to recover until it resumes, and the correct action is to tell consumers rather than to keep retrying. A dashboard note is a recovery action (Data Incidents).
What can go wrong
- Measuring only load time, so a healthy pipeline carrying nothing reports as fresh.
- Measuring maximum event time with no completeness qualifier, so a single early row certifies an empty period.
- A threshold derived from a wish rather than from the schedule, producing permanent, ignored breach.
- An alert muted during a recurring quiet period, which silently removes coverage for real failures in that window.
- Freshness averaged across datasets on a dashboard, which hides the one table that stopped (The Average Was Fine and Users Were Not).
- A mart promising freshness its parent cannot supply, so the SLO is unmeetable by construction.
- "The pipeline ran, so the data is fresh." The pipeline ran. Whether it carried anything is a different measurement, and it is the one consumers care about.
- "Fresh data is good data." Freshness is orthogonal to correctness. The fastest way to publish a wrong number is to publish it quickly (Data Quality).
- "The freshness check is green, so the period is complete." One recent row satisfies most freshness checks. Completeness is a count, not a maximum (Reconciliation).
- "We should page on every freshness breach." Page on breaches that affect a decision. A dataset read once a month should not wake anyone at 3 a.m. because it is four hours late (Quality Alerting).
Operating it
- Two series per serving dataset — now minus load time, now minus maximum event time — on the same axis with the threshold drawn on it (Dashboards Built Around Questions).
- The gap between them, which is the single best indicator of a source that has gone quiet while the pipeline continues cheerfully.
- Freshness at each hop of the lineage rather than only at the end, so a breach is localised to an arrow instead of investigated across six systems (Lineage Debugging).
- Time-since-last-*changed* row, distinct from time-since-last-write, which catches a pipeline that rewrites the same period forever (Depth Is Not an Emergency; Age Is).
- Volume changes nothing — this is a maximum, not a scan. Freshness is one of the few checks whose cost does not grow with the data (Scan Cost).
- Dataset count changes everything. At hundreds of datasets, per-table thresholds must be declared as metadata alongside the dataset rather than configured in a monitoring tool, or they will not exist for most tables (The Data Catalog).
- Consumer count turns freshness into a published commitment rather than an internal metric, at which point the arithmetic behind the threshold has to survive being questioned (SLAs: The Promise With Money Attached).
- Effectively free. A maximum over a partitioned or clustered timestamp column reads metadata or one partition, and many table formats expose the value without reading data at all (Partition Pruning).
- The variant that costs is the complete-period form, which needs a count per period as well as a maximum. Still cheap, and still the version worth having.
- The dominant cost is again attention: freshness is the check most likely to fire during a legitimately quiet window, and each of those firings spends on-call time (Alert Fatigue: The Page Nobody Reads).
- Tight thresholds catch real staleness quickly and fire on every quiet period. Loose thresholds are calm and let a stopped source run for hours before anyone hears about it.
- Measuring against complete periods is more truthful and slower to alert. For an operational dashboard that delay may be unacceptable; for a reported metric it is exactly right.
- Publishing per-dataset freshness invites consumers to depend on it, which is the point and also a commitment. Once a number is on a dashboard it becomes an SLA in everyone's mind whether or not you agreed to one (SLOs: A Target, a Window, and a Reason).
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 two-clock distinction and the derivation of the threshold from the schedule hold everywhere. What varies is how cheaply the maximum event time can be read — a partitioned warehouse table answers from metadata, an unpartitioned external table may need a scan.
- FORMAT-SPECIFICOpen table formats maintain per-file column statistics and snapshot commit times, so both clocks are available from metadata without touching data files. Plain directories of Parquet under an external table have file modification times but no committed snapshot time, so the load clock is approximate and can be changed by an unrelated rewrite such as compaction.
- ORG-SPECIFICWhether a freshness breach pages a human depends on which decision the dataset drives, which is a business judgement rather than a technical one. The same four-hour delay is an incident for an operations dashboard and completely irrelevant for a monthly finance model.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns replication lag as a phenomenon — why a replica is behind and what that means for reads. Freshness here is the same shape of question asked of a whole pipeline rather than of one replication link.
- — DevOps / Production Engineering owns the alerting and on-call machinery a freshness SLO plugs into. The threshold is a data decision; the paging policy is a production engineering one.