Staleness Checks

A staleness check is a small scheduled automation that watches the tables you care about and tells you when data stops updating.

It exists because of a failure mode that per-run alerts can't see: every upstream feed keeps succeeding, but the transform that builds your analytics tables stops running — its schedule was removed during an edit, its spill disk filled up, someone paused it — and every downstream table silently freezes. Nothing "failed", so nothing alerted. A staleness check watches the data instead of the runs: if max(updated_at) on a table you care about stops advancing, you hear about it.

Because it's an ordinary automation you own, everything is yours to tune: which tables, which timestamp column, how stale is too stale, how often to check, and whether an alert means a failed run in your Inbox, a Slack message, or an email.

The simple case has a built-in. If all you want is "warn when this table hasn't loaded in N hours", set an SLA on the table in the Catalog (the SLA editor sits under each table's freshness pills) — breaches surface in the Inbox as sla_breach alerts with no automation needed. Reach for the recipes below when you want a custom timestamp column, a cross-table comparison, your own alert text, Slack/email delivery per check, or a different schedule per table.

Variation 1 — raise to alert (recommended default)

One python step. It checks each table with a single SQL comparison and fails the run when anything is stale. A failed run is already a first-class event: it shows up as a pipeline-failure alert in the Inbox, and — if you've configured Settings → Notifications — goes out by webhook and/or email. You write no alerting code at all.

from definite_lakehouse import query

CHECKS = {
    # "schema.table": ("timestamp column", max age in hours)
    "analytics.orders": ("updated_at", 2),
    "analytics.revenue_daily": ("day", 26),
}

stale = []
for table, (col, hours) in CHECKS.items():
    fresh = query(
        f"SELECT max({col}) >= now() - INTERVAL {hours} HOUR AS fresh FROM {table}"
    ).to_pylist()[0]["fresh"]
    if not fresh:
        stale.append(f"{table}: no new {col} in {hours}h")

if stale:
    raise SystemExit("STALE DATA: " + "; ".join(stale))
print("all fresh")

The freshness comparison runs inside SQL (max(col) >= now() - INTERVAL n HOUR), which sidesteps timezone handling in Python entirely. An empty table yields NULL, which is not true — so a table with no rows counts as stale, which is what you want.

As a full pipeline definition (hourly):

{
  "steps": [
    {
      "id": "check",
      "type": "python",
      "config": {
        "attach_lake": true,
        "script": "from definite_lakehouse import query\n\nCHECKS = {\n    \"analytics.orders\": (\"updated_at\", 2),\n    \"analytics.revenue_daily\": (\"day\", 26),\n}\n\nstale = []\nfor table, (col, hours) in CHECKS.items():\n    fresh = query(\n        f\"SELECT max({col}) >= now() - INTERVAL {hours} HOUR AS fresh FROM {table}\"\n    ).to_pylist()[0][\"fresh\"]\n    if not fresh:\n        stale.append(f\"{table}: no new {col} in {hours}h\")\n\nif stale:\n    raise SystemExit(\"STALE DATA: \" + \"; \".join(stale))\nprint(\"all fresh\")\n"
      }
    }
  ]
}

definite run automation create \
  --name staleness-check \
  --definition "$(cat staleness-check.json)" \
  --cron "0 * * * *" \
  --format json

# run it once right now to verify (id from the create output)
definite run automation run <pipeline_id>

The SystemExit message becomes the run's error, so the Inbox alert (and any notification email) reads exactly what you wrote: STALE DATA: analytics.orders: no new updated_at in 2h. For longer scripts, prefer the stored-script workflow.

Variation 2 — a report to Slack or email, only when stale

Sometimes a failed run is the wrong shape — you want the check itself to stay green and instead receive a report, and only when there is something to say. Split it into a probe step that prints a report only when something is stale, plus a delivery step guarded by when: if the probe printed nothing, output_truthy:stdout is false and the delivery step is skipped.

The probe:

from definite_lakehouse import query

CHECKS = {
    "analytics.orders": ("updated_at", 2),
    "analytics.revenue_daily": ("day", 26),
}

lines = []
for table, (col, hours) in CHECKS.items():
    row = query(
        f"SELECT max({col}) AS last, "
        f"max({col}) >= now() - INTERVAL {hours} HOUR AS fresh FROM {table}"
    ).to_pylist()[0]
    if not row["fresh"]:
        lines.append(f"- {table}: last {col} = {row['last']} (threshold {hours}h)")

if lines:
    print("Stale tables:\n" + "\n".join(lines))

Then deliver to Slack (via a stored slack_webhook integration), to email, or both — each step templates the probe's stdout straight into the message:

{
  "steps": [
    { "id": "check", "type": "python", "config": { "attach_lake": true, "script": "<probe above>" } },
    {
      "id": "slack",
      "type": "slack_webhook",
      "when": { "step": "check", "expr": "output_truthy:stdout" },
      "config": {
        "integration": "slack-alerts",
        "text": ":warning: {{ steps.check.stdout }}"
      }
    },
    {
      "id": "email",
      "type": "send_email",
      "when": { "step": "check", "expr": "output_truthy:stdout" },
      "config": {
        "to": ["data-team@example.com"],
        "subject": "Stale data detected",
        "text_body": "{{ steps.check.stdout }}\nSent by the staleness-check automation."
      }
    }
  ]
}

When everything is fresh the run succeeds with both delivery steps skipped — no noise anywhere. Keep whichever delivery step you want, or both. send_email requires the deployment's email delivery to be configured; slack_webhook needs a slack_webhook integration (see Slack setup) or a webhook_url.

Which variation to pick: Variation 1 if you already route pipeline-failure notifications (one step, zero config, the Inbox is the record). Variation 2 if stale data should page a Slack channel or a distribution list directly, or you don't want red runs for a data-quality condition.

Configuration

How often to check — the pipeline's cron_schedule (standard 5-field cron, minimum every minute) and optional cron_timezone:

Set them at create time (--cron, --cron-timezone) or later with definite run automation update <id> --cron "...".

Which tables, and how stale is too stale — rows in the CHECKS dict: the timestamp column and a per-table max age. Set each threshold to 2–3× the feed's expected cadence (hourly loads → 2–3h threshold; a daily table → 26h) so one slow run doesn't produce a false alarm. A date-grain column like day needs the daily-scale threshold even if the job runs hourly.

Custom timestamp columns — anything SQL can evaluate works as the freshness probe, not just a TIMESTAMP column:

-- epoch milliseconds
SELECT to_timestamp(max(updated_ms) / 1000) >= now() - INTERVAL 2 HOUR AS fresh FROM ...

-- ingestion timestamp written by your loader
SELECT max(_loaded_at) >= now() - INTERVAL 2 HOUR AS fresh FROM ...

Mart vs. raw feed — the sharpest check when raw ingestion and transforms are separate pipelines: raw feeds can keep landing while the transform that builds the mart has stopped. Compare the two directly instead of using a fixed age:

SELECT (SELECT max(updated_at) FROM analytics.orders)
    >= (SELECT max(updated_at) FROM raw.orders) - INTERVAL 1 HOUR AS fresh

This alerts precisely when the mart stops keeping up with its source, regardless of how often either one loads.

Setting it up with Fi

You don't have to write any of this by hand — ask Fi:

Set up a staleness check: alert if analytics.orders has no new updated_at for 2 hours or revenue_daily hasn't updated in 26 hours. Check hourly and post to our alerts Slack channel.

Fi will build the pipeline, create the Slack integration through a secure form if one doesn't exist, and run the check once to verify it.

Notes

• The check itself is a scheduled pipeline — if someone disables it, it goes silent too. For belt-and-braces, add a daily heartbeat variant (an unconditional slack_webhook step reporting "checked N tables, all fresh") so silence itself becomes a signal.
• Alert emails for Variation 1 require email notifications to be enabled in Settings → Notifications (pipeline failures are error severity, so the default severity threshold delivers them once the rule is on). In-app Inbox alerts need no setup.
• The python step runs with attach_lake: true and the preinstalled definite_lakehouse client — see the environment contract for exactly what the script can reach.