Data freshness: what it is and how to check it

Freshness is a lag, not a status

A pipeline has no “up” or “down” light you can trust. Jobs succeed while writing zero rows, schedulers get paused, a credential expires and the error lands in a log nobody reads. The table itself is the only honest witness: if the newest row is old, the data is stale, whatever the pipeline claims.

So define freshness as a number, the lag, and attach a threshold to it. The threshold comes from the consumer, not the pipeline. A finance dashboard read once a day tolerates 24 hours. An ops view people refresh during an incident does not.

Check it with one query

This returns a row only when the table is stale. A healthy table returns nothing, which suits any scheduler that alerts on a non-empty result. An arcodash monitor needs one numeric column in the first row instead, so use the always-returns-a-row variant in data freshness monitoring.

SELECT
  now() - max(loaded_at) AS lag,
  max(loaded_at)         AS last_row
FROM events
HAVING coalesce(max(loaded_at), '-infinity')
       < now() - interval '2 hours';

The coalesce matters. An empty table has a max of NULL, and a comparison with NULL is never true, so without it a table that lost all its rows would look fine.

Check several tables at once

Add one UNION ALL branch per table and keep the same threshold shape. The result lists only the tables that are stale.

SELECT 'events' AS tbl, now() - max(loaded_at) AS lag
FROM events
HAVING coalesce(max(loaded_at), '-infinity') < now() - interval '2 hours'
UNION ALL
SELECT 'orders', now() - max(loaded_at)
FROM orders
HAVING coalesce(max(loaded_at), '-infinity') < now() - interval '2 hours';

Where freshness checks go wrong

• Event time instead of load time. A quiet weekend makes created_at old even though the pipeline is healthy. Use a load timestamp, or widen the threshold to cover the quiet period.
• Timezones. A timestamp without a time zone compared with now() can be off by hours. Store timestamptz, or convert explicitly.
• Thresholds nobody chose. Ask who reads the table and when, then set the lag to what hurts them.
• Only checking the final table. If the last table in the chain is fed by a stale one, the alert fires late. Check the first table you own as well.

Freshness covers one failure: data that stopped arriving. Data that arrives wrong, such as half the usual rows or a column full of nulls, needs volume and validity checks. That wider set is what people usually mean by data observability.

If you would rather not hand-write the query, the SQL data check generator builds a freshness check for your table and dialect.