How to Audit Data Freshness Across All Your Tables

Here's the scenario that plays out roughly once a quarter at every company I've worked at: a nightly ETL job fails. No error email, no alert, nothing — the orchestrator marks it as skipped or the cron just stops firing. The dashboard still loads. The numbers still look reasonable. Three days later, someone on the finance team asks why the revenue chart hasn't moved since Tuesday, and that's when you discover you've been reporting stale data to leadership for 72 hours.

The fix isn't better ETL monitoring, though that helps too. The fix is a freshness check that runs independently of the pipeline — something that looks at the data itself and says "this table hasn't been updated in 47 hours, and it should update every hour."

The single-table version

For one table, this is dead simple:

SELECT
  MAX(updated_at)                                          AS last_updated,
  EXTRACT(EPOCH FROM (NOW() - MAX(updated_at))) / 3600    AS hours_since_update
FROM orders;

If hours_since_update comes back as 47 when your pipeline runs hourly, something's broken. But nobody runs this for just one table. The point is to check all of them, automatically, without maintaining a separate query per table.

Scanning every table in Postgres

The trick is using information_schema.columns to find every table that has a timestamp column, then running the check dynamically. This query generates a freshness report across your entire schema in one shot:

DO $$
DECLARE
  rec RECORD;
  query TEXT;
BEGIN
  CREATE TEMP TABLE IF NOT EXISTS freshness_report (
    table_name   TEXT,
    ts_column    TEXT,
    last_updated TIMESTAMPTZ,
    hours_stale  NUMERIC
  );
  TRUNCATE freshness_report;
 
  FOR rec IN
    SELECT table_schema || '.' || table_name AS full_table,
           column_name
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND data_type IN ('timestamp with time zone',
                        'timestamp without time zone')
      AND column_name IN ('updated_at', 'created_at', 'modified_at',
                          'last_modified', 'inserted_at')
  LOOP
    query := format(
      'INSERT INTO freshness_report
       SELECT %L, %L, MAX(%I),
              EXTRACT(EPOCH FROM (NOW() - MAX(%I))) / 3600
       FROM %s',
      rec.full_table, rec.column_name,
      rec.column_name, rec.column_name,
      rec.full_table
    );
    EXECUTE query;
  END LOOP;
END $$;
 
SELECT * FROM freshness_report ORDER BY hours_stale DESC;

Two things to note. First, the column_name IN (...) filter is doing real work — without it, you'd pick up every timestamp column in every table, including things like password_reset_requested_at that have nothing to do with freshness. Add your own conventions to that list. Second, this scans actual rows via MAX(), which means it touches every table. On a 500-table warehouse, that's not free. Run it during off-peak hours or limit it to the tables you actually care about.

The metadata shortcut: pg_stat_user_tables

If you don't want to scan rows at all, Postgres tracks table activity in pg_stat_user_tables. It won't give you the exact timestamp of the last row written, but it tells you when the table was last vacuumed (which correlates with write activity) and how many inserts and updates have happened since the last stats reset:

SELECT
  schemaname || '.' || relname                         AS table_name,
  n_tup_ins                                            AS total_inserts,
  n_tup_upd                                            AS total_updates,
  n_tup_ins + n_tup_upd                                AS total_writes,
  last_autovacuum,
  last_autoanalyze,
  EXTRACT(EPOCH FROM (NOW() - COALESCE(last_autovacuum,
    last_autoanalyze))) / 3600                          AS hours_since_activity
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY hours_since_activity DESC NULLS FIRST;

Rows with NULL for both last_autovacuum and last_autoanalyze are the ones to worry about — they've either never been vacuumed (tiny tables that haven't hit the autovacuum threshold) or haven't seen write activity since the last stats reset. The n_tup_ins and n_tup_upd columns disambiguate: if the counts are high but autovacuum is null, the table is active but small enough that autovacuum hasn't triggered. If the counts are zero, the table is genuinely dormant.

This approach is fast — no table scans, just catalog reads — but it's a proxy. It tells you "this table hasn't had writes recently" rather than "the most recent row is from Tuesday." For most freshness monitoring, the proxy is good enough. When you need exact timestamps, fall back to the MAX() approach above.

DuckDB variant for synced tables

If you're pulling data from multiple sources into a cross-source store — Fastero's DuckDB store, for example — you need freshness checks there too. DuckDB's information_schema works the same way, but the syntax for dynamic SQL is different because DuckDB doesn't have DO $$ ... $$ blocks.

The pragmatic approach: query the metadata, then run the checks from your application layer.

SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'main'
  AND data_type IN ('TIMESTAMP', 'TIMESTAMP WITH TIME ZONE',
                    'TIMESTAMPTZ')
  AND column_name IN ('updated_at', 'created_at', 'synced_at',
                      'last_modified')
ORDER BY table_name;

Then iterate from Python or Node and run SELECT MAX({col}) FROM {table} for each match. In DuckDB this is fast even on large tables because columnar storage means reading a single column doesn't touch the rest of the row.

One DuckDB-specific gotcha: if your sync process drops and recreates tables on each run (a common pattern with full-refresh syncs), the synced_at or _fastero_synced_at column is your real freshness signal, not updated_at from the source. A table with updated_at from three months ago might be perfectly fresh if it was synced 10 minutes ago and the source data just doesn't change often.

Append-only vs. updated tables

Not all tables age the same way. An events or audit_log table is append-only — use MAX(created_at). An orders or subscriptions table gets updated in place — use MAX(updated_at).

But MAX(updated_at) can mislead you on low-activity tables. If your orders table has 500,000 rows and only 50 get status updates per day, MAX(updated_at) reflects the last order that changed status, not the last time the sync ran. The table can be pipeline-fresh while MAX(updated_at) is hours old.

The cleanest fix: add a _loaded_at column that your ETL writes on every run, regardless of whether individual rows changed. If you can't add a column, the pg_stat_user_tables metadata approach sidesteps the problem entirely.

Setting freshness SLAs

The freshness check is useless without a definition of "stale." An alert that fires on every table with the same threshold will either miss slow tables or cry wolf on fast ones. Define SLAs per table:

• Transactional tables (orders, payments, events): < 1 hour
• CRM syncs (contacts, deals): < 4 hours
• Analytics rollups (daily_revenue, cohort_metrics): < 24 hours
• Reference tables (countries, currency_rates): < 7 days

Store these SLAs in a table or config file, then join them against your freshness report to compute status:

WITH slas (table_name, max_hours) AS (
  VALUES
    ('public.orders',          1),
    ('public.events',          1),
    ('public.contacts',        4),
    ('public.daily_revenue',  24),
    ('public.currency_rates', 168)
),
freshness AS (
  SELECT * FROM freshness_report
)
SELECT
  f.table_name,
  f.last_updated,
  ROUND(f.hours_stale, 1)                             AS hours_stale,
  s.max_hours                                          AS sla_hours,
  CASE
    WHEN f.hours_stale <= s.max_hours       THEN 'green'
    WHEN f.hours_stale <= s.max_hours * 1.5 THEN 'yellow'
    ELSE                                         'red'
  END                                                  AS status
FROM freshness f
JOIN slas s ON f.table_name = s.table_name
ORDER BY
  CASE WHEN f.hours_stale > s.max_hours THEN 0 ELSE 1 END,
  f.hours_stale DESC;

Red tables sort to the top. Yellow means "approaching the SLA boundary" — a heads-up before it becomes an incident. Green means nothing to do. This is the query you'd wire into a live dashboard or a scheduled Slack report.

Alerting on stale tables

The freshness query and the SLA check are the detection layer. But detection that runs on a schedule and posts results to a dashboard you check once a day isn't good enough — the whole point is catching problems before someone else does.

Wire the SLA query into an alert. If you're using Fastero, this is a trigger: run the query on a cron, fire a Slack notification when any table hits red. If you're doing it yourself, a Python script on a cron that hits the database, runs the check, and POSTs to a Slack webhook works fine — the approach from setting up SQL alerts without Datadog applies directly here.

The alert should include the table name, how many hours stale it is, what the SLA is, and ideally what downstream dashboards or reports depend on that table. That last part — the blast radius — is where Fastero's data quality monitoring and lineage comes in. A stale orders table isn't just a stale table; it's every revenue dashboard, every MRR calculation, every daily report that reads from it. Knowing the impact scope when the alert fires is the difference between triaging quickly and spending 30 minutes figuring out what's affected.

What this doesn't catch

Freshness checks catch pipelines that stopped running. They don't catch pipelines that ran but loaded garbage. A table that updated 5 minutes ago with 200 rows when it should have gotten 50,000 passes every freshness check with flying colors.

That's why freshness is one layer, not the whole stack. Pair it with row count anomaly checks, null rate monitoring, and schema drift detection. The data quality monitoring guide covers the full set of checks and when each one matters.

But if you're going to start with one check — one query you schedule today before you build the rest — make it the freshness audit. It catches the most common failure mode, it's cheap to run, and it would have saved every team I've been on at least one ugly "we've been reporting stale data for three days" conversation.

Try Fastero free — connect your databases and set up freshness SLAs with automated Slack alerts across all your tables. No credit card required.