Source Freshness

Concepts covered: Sources and seeds

Tests check the rows a model holds. They say nothing about whether new rows are still arriving: a raw table that stopped loading 3 days ago passes unique and not_null as well as it ever did, and every model built from it serves data 3 days old without a single FAIL. Source freshness is the check for that, and it runs against the raw table dbt reads, never against a model.

sources:
  - name: raw
    schema: raw
    config:
      loaded_at_field: _loaded_at
      freshness:
        warn_after:
          count: 12
          period: hour
        error_after:
          count: 24
          period: hour
    tables:
      - name: orders
      - name: customers
        config:
          freshness:
            warn_after:
              count: 2
              period: day
            error_after:
              count: 4
              period: day

loaded_at_field names the column the loader stamps with the time it wrote the row. The freshness block sets 2 windows: past warn_after dbt prints WARN, past error_after it prints ERROR. Set in the source's config: block, both apply to every table under it; a table can carry its own config: to override them, which is why customers, loaded once a night, gets windows measured in days while orders keeps the hourly ones.

select
    max(_loaded_at) as max_loaded_at,
    current_timestamp as snapshotted_at
from raw.orders

dbt source freshness sends that query for each source table with a freshness config, subtracts the newest load time from the time of the query, and compares the age with the 2 windows. It touches no model and writes no table. dbt build never runs it either, so a project that wants both checks runs 2 commands, and freshness usually goes first because a stale source makes building from it pointless.

Reading the output

The command prints 1 line per table showing the status and how long the query took, laid out like a test line. A WARN leaves the exit code at 0, so a calling script carries on; an ERROR sets it non-zero, the same signal a failed test gives. The same age that prints WARN for orders would print PASS for customers, because the table's own windows replace the source's.

A morning the loader stops

Suppose the orders loader dies at 22:00 on 2026-11-01 and the morning script runs at 08:00 on 2026-11-03, freshness first. raw.orders is 34 hours old, past its 24-hour error_after, so it prints ERROR and the command exits non-zero, which stops the script before the build. raw.customers prints PASS because it loaded at 01:00, 7 hours earlier. Had the build run anyway, it would have found nothing to object to: the raw rows are unchanged, every test returns the same 0 rows as the day before, and order_totals is rebuilt to the same totals it held 34 hours ago. The freshness ERROR is the only line in either log that reports the orders stopped arriving.

Choosing the column

The column must say when the row was loaded, not what the row is about. order_date records when a customer bought, so a loader that died on Sunday night leaves Saturday's orders as the newest rows and the check reports stale, which is right, while a quiet Sunday with no sales reports stale too, which is wrong. A column the loader stamps at write time, named something like _loaded_at, separates those 2 cases: it advances with every batch whether the batch held 1 order or 1 million.

A table with years of history makes the max scan everything. A filter: under freshness adds a where clause to the query so the scan stays within recent rows, and the comparison with the windows works as before.

sources:
  - name: raw
    schema: raw
    config:
      loaded_at_field: _loaded_at
      freshness:
        warn_after:
          count: 12
          period: hour
        filter: _loaded_at > current_timestamp - interval '2' day
    tables:
      - name: orders

About this lesson

This section is part of the Testing: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.