dbt source freshness: alert when a source stops loading

The goal

An upstream load stopped overnight. dbt ran on schedule, every model and test passed, and the dashboard shows yesterday's numbers.

Source freshness closes that gap. It checks how old the newest row in each raw table is, and warns or fails when it passes a limit you set.

Step 1: check the timestamp column

Freshness is only as good as the column you point it at. You want the time the row landed in the warehouse, not when the business event happened. A backfill of old orders has old event times but a current load time.

Look at the column directly:

SELECT MAX(_loaded_at)                         AS latest_load,
       CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP()) AS now_utc,
       COUNT(*)                                AS row_count
FROM raw.erp.orders;

Check that latest_load is close to the time you expect, and in the same time zone as now_utc. If your loader doesn't write a load timestamp, adding one is the best fix.

Step 2: add freshness to the source

# models/staging/erp/_erp__sources.yml
version: 2

sources:
  - name: erp
    database: raw
    schema: erp
    config:
      loaded_at_field: _loaded_at
      freshness:                 # default for every table below
        warn_after: {count: 12, period: hour}
        error_after: {count: 26, period: hour}
    tables:
      - name: orders
        config:
          freshness:             # stricter for this table
            warn_after: {count: 1, period: hour}
            error_after: {count: 4, period: hour}
            filter: _loaded_at >= DATEADD('day', -2, CURRENT_TIMESTAMP())
      - name: customers
      - name: country_codes
        config:
          freshness: null        # static reference data, don't check

How to read that file:

Inheritance. Settings on the source apply to all its tables. Settings on a table override them.
Thresholds. Each threshold needs both count and period (minute, hour or day). You can set one threshold or both. With neither, dbt doesn't check that table.
Choosing values. Base them on the load schedule. A table that loads daily can't use error_after: 24 hours, because normal variation in load time would trip it. 26 hours gives some margin.

Note
Placement varies by version. freshness moved under config: in v1.9 and loaded_at_field in v1.10. On an older project, these keys sit directly on the source or table. Check the docs for your version.

If the timestamp isn't a plain UTC column, cast it. These forms come from the dbt docs:

loaded_at_field: "completed_date::timestamp"
loaded_at_field: "convert_timezone('Australia/Sydney', 'UTC', created_at_local)"

From v1.10 you can use loaded_at_query instead, for a full SQL expression. Set one or the other. Having both on the same resource is a parse error, and filter doesn't apply to loaded_at_query.

If you leave out loaded_at_field, dbt can use warehouse metadata where that's possible. Not every source supports this, so check your adapter's documentation. Confirm it by checking that the table appears in the results of Step 3.

Step 3: run it

# All sources
dbt source freshness

# One source, or specific tables
dbt source freshness --select source:erp
dbt source freshness --select source:erp.orders source:erp.customers

The results are also written to target/sources.json. Each entry has a status:

Status	Meaning
`pass`	Newest row is inside `warn_after`
`warn`	Older than `warn_after`, inside `error_after`
`error`	Older than `error_after`
`runtime error`	The freshness query itself failed

Note
In dbt v2 the docs list dbt source freshness as a legacy command. dbt freshness --resource-type source replaces it and writes target/freshness.json. target/sources.json is still written for backward compatibility when sources are included.

Step 4: schedule it and alert

Don't assume dbt build covers this. Run freshness as its own step, ahead of your build, or on a more frequent schedule of its own.

The command exits non-zero when a table is stale. Check how warn behaves on your version before you rely on the exit code alone. For a clear alert, read the results file instead:

# scripts/check_freshness.py
import json
import sys

with open("target/sources.json") as f:
    results = json.load(f)["results"]

problems = [r for r in results if r["status"] != "pass"]

for r in problems:
    age = r.get("max_loaded_at_time_ago_in_s")
    age_text = f"{age / 3600:.1f}h old" if age is not None else "age unknown"
    print(f'{r["status"]:14} {r["unique_id"]}  {age_text}')

# Warnings print but don't fail the job. Anything else does.
sys.exit(1 if any(r["status"] != "warn" for r in problems) else 0)

#!/usr/bin/env bash
# Clear the old file so a failed run can't report yesterday's results
rm -f target/sources.json

dbt source freshness || true   # the script below decides pass or fail
python scripts/check_freshness.py

If dbt source freshness can't parse the project or connect, sources.json won't exist. The script then fails with a file-not-found error, which is what you want: no results is not a pass.

Warning
A failed freshness step shouldn't always block your build. If a low-priority source going stale stops every model, you've turned one late feed into a full outage. Run critical sources as a gate, and alert only on the rest.

Fixing false alarms and failures

Symptom	Likely cause	Fix
Table is missing from the results	No thresholds, or `freshness: null` inherited	Add `warn_after` or `error_after` at source or table level
Table is missing and thresholds are set	No `loaded_at_field`, and metadata isn't available for it	Set `loaded_at_field`
Always stale by the same number of hours	Column is local time, not UTC	Cast it with `convert_timezone`
Always `error` on Monday morning	Source doesn't load at weekends	Raise the threshold, or don't alert on that table on those days
`runtime error`	Wrong column name, no `SELECT` grant or bad cast	Run the Step 1 query as the dbt role
`runtime error` after adding `filter`, or on an empty table	No rows match, so there's no maximum timestamp	Widen the filter, and check the table has data
Check is slow or expensive	Full scan to get `MAX()` on a big table	Add `filter` on a partitioned or clustered column

Tip
Keep the filter window wider than error_after. In the example, the window is 2 days and the limit is 4 hours. If the window is too narrow, a stale table has no matching rows at all, and you get a runtime error instead of a clean "stale" result.

Build only what has new data

Freshness results can also save warehouse cost. source_status:fresher+ selects models downstream of sources with newer data than the previous run. It compares two sources.json files, so both the previous run and this one need a freshness step.

# prod-artifacts/ holds sources.json and manifest.json from the last good run
dbt source freshness
dbt ls --select "source_status:fresher+" --state prod-artifacts   # preview
dbt build --select "source_status:fresher+" --state prod-artifacts

# Only after a successful build
cp target/sources.json target/manifest.json prod-artifacts/

Copy the artifacts only after the build succeeds. If you copy them after a failure, the next run sees no newer data and never retries the models that failed. The artifacts in --state must also use schema versions that your running dbt version can read, so refresh them after a dbt upgrade.

Prevent it next time

Every source table has thresholds, or an explicit freshness: null with a comment explaining why.
Thresholds are based on the real load schedule, and reviewed when the schedule changes.
loaded_at_field is a load time, not a business event time.
Freshness runs on a schedule and alerts on-call for critical sources.
Each alert names the owner of the source system, so the page goes to the team that can fix the load.
Row-count and null tests run alongside it. Freshness shows that data arrived, not that all of it arrived.

dbt source freshness: alert when a source stops loading

#The goal

#Step 1: check the timestamp column

#Step 2: add freshness to the source

#Step 3: run it

#Step 4: schedule it and alert

#Fixing false alarms and failures

#Build only what has new data

#Prevent it next time

dbt test failed: see the failing rows, set severity and thresholds, and stop noisy tests blocking builds

Data quality checks that catch customer-visible problems before the customer does

dbt incremental model drifted from source: repair duplicates, missed rows and new columns

The goal

Step 1: check the timestamp column

Step 2: add freshness to the source

Step 3: run it

Step 4: schedule it and alert

Fixing false alarms and failures

Build only what has new data

Prevent it next time