Data freshness monitoring with SQL

What a stale dashboard looks like

Nothing errors. The dashboard loads, the chart draws, the number is just last Tuesday’s. A pipeline credential expired, a scheduler got paused during a migration, an upstream export started landing empty files. The job reports success each time because writing zero rows isn’t a failure.

The first sign is usually a person: someone in a Monday meeting says revenue looks flat, or a customer asks why their report hasn’t moved. By then the data has been wrong for days, and the number was already quoted somewhere.

The cheap defense is a query that asks the table itself how old its newest row is. The freshness glossary entry covers the definition, and the Postgres freshness post has the copyable one-table checks. This page is about running it as monitoring: what to return, how to pick limits, and how to cover many tables.

Return a number to alert on

The glossary query returns a row only when the table is stale. That’s fine for reading by eye. For an alert, return a number always. arcodash alerts read one column of the first row, and a query with no rows leaves the alert unknown, not ok.

SELECT (coalesce(extract(epoch FROM now() - max(loaded_at)), 999999999) - 7200)::bigint
       AS overdue_seconds
FROM events;

Read it as “seconds past the 2 hour limit”. Negative means fine, positive means stale, and the alert condition is overdue_seconds > 0. The coalesce handles an empty table: max is NULL, a NULL is not numeric, and the alert would go unknown instead of firing. The large fallback makes an empty table maximally overdue.

Cover many tables with one alert

Give each table its own limit in the query, then return the worst one:

SELECT tbl, overdue_seconds
FROM (
  SELECT 'events' AS tbl,
         (coalesce(extract(epoch FROM now() - max(loaded_at)), 999999999) - 7200)::bigint AS overdue_seconds
  FROM events
  UNION ALL
  SELECT 'orders',
         (coalesce(extract(epoch FROM now() - max(loaded_at)), 999999999) - 1800)::bigint
  FROM orders
  UNION ALL
  SELECT 'refunds',
         (coalesce(extract(epoch FROM now() - max(loaded_at)), 999999999) - 86400)::bigint
  FROM refunds
) t
ORDER BY overdue_seconds DESC
LIMIT 1;

On my test data events was 3 hours old against a 2 hour limit, so the query returned events with 3600. The alert watches overdue_seconds > 0, and because the table name is in the same row, anyone who opens the saved query sees which table tripped it without reading a log.

The limit: the alert is about the single worst table. If events is stale and orders goes stale an hour later, the alert is already triggered, and arcodash doesn’t notify again until it clears. For tables owned by different people, use one alert per owner group, each with its own query and Slack channel.

Choosing thresholds

Ask who reads the table and when, then set the limit to what hurts them. A few rules that hold up:

• Batch loads: cadence plus slack. A nightly job that normally finishes by 03:00 and is read at 09:00 gets a limit around 26 hours, not 24. The extra two hours absorb a slow night. A 24 hour limit pages you every time the job runs a bit late.
• Streams and frequent loads: a few times the interval. A table loaded every 15 minutes gets 45 to 60 minutes. One missed run shouldn’t alert, two should.
• Weekends and holidays. If the pipeline skips weekends, a Monday morning limit based on weekday cadence fires every week. Either widen the limit for those tables or accept a Monday exception rather than muting the alert.
• Start loose. An alert that cries wolf gets muted, and a muted alert catches nothing. Tighten the limit after it has run clean for a few weeks.

Use a load timestamp (loaded_at) where you have one. An event timestamp like created_at looks stale whenever the business is quiet, which is a false alarm on an overnight B2B table.

What to alert on

Freshness is one signal, so choose what goes with it:

• Alert on the tables people read, and on the first table you own in the chain. A stale source feeding a fresh-looking aggregate table is caught late if you only watch the end.
• Alert on dashboard inputs, not every table. A freshness alert for each of 200 tables becomes noise. Start with the ten behind dashboards someone checks daily.
• Add a row count check beside it for the tables that matter. Fresh timestamps over half the usual rows is a different failure; see the row count anomaly query.

Set it up in arcodash

The freshness check is an ordinary saved query with an alert on it. The data observability recipes describe the same pattern.

1. Save and publish the query above against your data source.
2. Schedule it. An alert is only checked when its query refreshes, so a query nobody opens and nobody schedules never alerts. Pick every 5, 15 or 30 minutes, hourly, or daily at a fixed UTC time.
3. Create an alert on overdue_seconds with > and 0.
4. Subscribe a destination. Sending it to Slack takes a webhook URL and a couple of minutes.

If your organization has an AI provider configured, a connector’s Data quality checks card can also suggest checks like these from the schema. Treat suggestions as a first draft and read the thresholds.

Honest limits

• It only proves the timestamp moved. Wrong data with a fresh loaded_at passes.
• The check queries your database on every run. On a huge table without an index on the timestamp column, max(loaded_at) is a scan. Index it, or point the check at a small status table the pipeline writes.
• Schedules run in UTC, and a 5 minute run is the fastest cadence.
• If the check’s own query fails ten times in a row, arcodash pauses the schedule. A broken freshness check should itself be noticed.

If you have a delivery deadline rather than a threshold, see data SLA for measuring how often you meet it.

To get the starting query for your own table, the SQL data check generator fills in the timestamp column and interval, and its One number style returns a value a monitor can read.