Как посчитать data staleness в SQL

Зачем staleness

Staleness — время с момента последнего обновления данных. Если пайплайн «забыл» обновить таблицу, метрики тихо показывают вчерашние числа — без алерта. Stuck pipeline ставит критические дашборды на «pause», пока его не починят. Базовый чек DQ перед любым SLA.

Базовая формула

SELECT
    MAX(updated_at) AS last_updated,
    NOW() - MAX(updated_at) AS staleness
FROM orders;

Или конкретно в часах:

SELECT
    EXTRACT(EPOCH FROM (NOW() - MAX(updated_at))) / 3600 AS hours_stale
FROM orders;

Если у таблицы нет updated_at — используйте MAX(created_at). Худший вариант — partition_date или created_at без updated_at вообще, тогда staleness неточный (только append-staleness).

Staleness в разрезе

Часто несколько источников пишут в одну таблицу:

SELECT
    source_id,
    MAX(updated_at) AS last_updated,
    NOW() - MAX(updated_at) AS staleness,
    COUNT(*) FILTER (WHERE updated_at >= CURRENT_DATE - INTERVAL '1 day') AS rows_today
FROM events
GROUP BY source_id
ORDER BY staleness DESC;

Если один источник отстаёт на 4 часа, остальные пишут как обычно — у вас «частичная» staleness. Чисто MAX(updated_at) на всей таблице это скроет.

SLA-чек

Бинарный verdict против SLA:

WITH staleness AS (
    SELECT
        EXTRACT(EPOCH FROM (NOW() - MAX(updated_at))) / 3600 AS hours_stale
    FROM orders
)
SELECT
    hours_stale,
    CASE
        WHEN hours_stale > 24 THEN 'CRITICAL: > 24h stale'
        WHEN hours_stale > 6  THEN 'WARNING: 6-24h stale'
        WHEN hours_stale > 2  THEN 'INFO: 2-6h stale'
        ELSE 'fresh'
    END AS verdict
FROM staleness;

SLA по таблице зависит от cadence обновления. Hourly job → SLA 2 часа. Daily → 25 часов.

Дифф staleness

Сравнение «как давно мы видели свежую запись» vs «когда таблица последний раз обновлялась физически». Различия указывают на проблему трекинга:

SELECT
    MAX(updated_at) AS last_updated_logical,
    pg_last_committed_xact() AS last_commit_info,  -- Postgres
    EXTRACT(EPOCH FROM (NOW() - MAX(updated_at))) / 3600 AS hours_stale_logical
FROM orders;

Если last_commit_info свежий, а last_updated_logical старый — ETL пишет, но не обновляет updated_at (баг кода).

Частые ошибки

Ошибка 1. Использовать created_at вместо updated_at. created_at показывает только последнюю вставку. Update существующих строк остаётся скрытым.

Ошибка 2. Игнорировать timezone. NOW() - MAX(updated_at) сломается, если в updated_at хранится timestamp without time zone в UTC, а NOW() в local. Приводите оба к одной зоне.

Ошибка 3. Один SLA для всей БД. Real-time ивенты — 5 мин. Reporting tables — 24h. Уникальный SLA на таблицу.

Ошибка 4. Не сегментировать. Если один источник из 10 отстал, общая MAX(updated_at) всё ещё свежая (потому что другие пишут). Сегментируйте.

Ошибка 5. Считать staleness как алерт без SLA. «5 часов» — это плохо или нормально? Без SLA — непонятно. Сначала фиксируйте SLA, потом меряйте.

Связанные темы

• Как посчитать data freshness в SQL
• Как посчитать data completeness в SQL
• Как посчитать data quality score в SQL
• Как посчитать data volume anomaly в SQL

FAQ

Staleness vs freshness?

Freshness — общее «насколько данные свежи». Staleness — конкретный numerical age с последнего апдейта.

Какой SLA нормальный?

Hourly tables — 1-2 часа. Daily — 25-30 часов. Real-time event streams — секунды-минуты.

Что если updated_at нет?

created_at или partition_date. Для append-only таблиц этого достаточно.

Staleness на пустой таблице?

NULL. Алерт separate (или фиктивный «∞»).

Staleness как мониторить?

Через Grafana / Datadog / Airflow sensor — запрос staleness каждые 5 мин, алерт через PagerDuty.