dbt source freshness warn error thresholds

TL;DR

Set freshness.warn_after and freshness.error_after per source based on the real SLA of that data, and make sure the loaded_at_field actually exists and is populated. Freshness without a trustworthy timestamp column is noise.

dbt source freshness warn error thresholds

Use this when

• Freshness checks never alert or alert constantly
• You are setting warn/error windows per source
• dbt source freshness reports weird results

Not for this skill when

• Seed CSV columns get wrong types
• You are writing unit tests with fixtures
• You are choosing snapshot strategies

Steps

1. Define freshness per source with thresholds that match reality:

sources:
  - name: raw
    tables:
      - name: orders
        loaded_at_field: _etl_loaded_at
        freshness:
          warn_after: {count: 6, period: hour}
          error_after: {count: 24, period: hour}

Expected output: dbt source freshness warns after 6 stale hours and errors after 24. The numbers must come from the source's actual SLA, not a global default.

1. Verify the timestamp column is real and populated:

SELECT max(_etl_loaded_at), count(*) FILTER (WHERE _etl_loaded_at IS NULL)
FROM raw.orders;

Expected output: a recent max timestamp and zero nulls. A null or stale loaded_at_field makes every check lie; this is the most common freshness bug.

1. Run the check and read the output states:

dbt source freshness

Expected output: per-source pass, warn, or error with the age of the data. runtime error usually means the loaded_at_field does not exist or is not a timestamp.

1. Set different thresholds for different source speeds:

# streaming-ish source: tight windows
freshness: {warn_after: {count: 30, period: minute}, error_after: {count: 2, period: hour}}
# daily batch source: loose windows
freshness: {warn_after: {count: 30, period: hour}, error_after: {count: 48, period: hour}}

Expected output: alerts that fire when the specific source is actually late. One global threshold pages the daily-batch owner every morning and never catches the streaming source.

1. Wire the error state into alerting, not just the dbt run log:

In CI or the orchestrator: fail the pipeline (or page) on freshness
error, notify on warn. A freshness check nobody reads is decoration.
dbt Cloud and most orchestrators can gate downstream runs on it.

Expected output: stale sources block or alert before downstream models build on old data.

Variant phrasings

dbt source freshness not working

Check the loaded_at_field exists and is fresh (step 2). Most "not working" reports are a bad timestamp column.

dbt freshness warnafter errorafter

Per-source thresholds in the source YAML (steps 1, 4). Both take count plus period (minute, hour, day).

dbt source freshness runtime error

The query against the source failed: missing column, wrong type, or no read access. The error message names the column; verify it in the database.

Why it happens

Freshness compares now() - max(loaded_at_field) against your thresholds. The check is only as good as the timestamp column and the thresholds. Defaults fit nobody, and a loaded_at_field that the loader stopped populating turns the whole feature into a false-alarm generator.

Edge cases

• Timezones: loaded_at_field in a different zone than the dbt server shifts every reading; store UTC.
• Sources that legitimately pause (weekends, holidays) need thresholds that tolerate the pause or they page every Monday.
• Freshness checks query the source directly, which can be slow on huge tables; an index on the timestamp column helps.
• dbt source freshness does not run models; it is a separate command, often forgotten in CI.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_z9F5EqqYY5NFjpkncMkCow