Skip to main content

03. Source Freshness Tests

️ What Are Source Freshness Tests?​

Source freshness tests in dbt validate that your raw data sources are arriving on time. They compare the latest timestamp in the source table against the current time and report whether the data is fresh enough.

Freshness checks are not the same as schema or data tests. They are specifically designed for data arrival monitoring.


Why Freshness Tests Matter​

  • Detect stale upstream data before downstream models run
  • Prevent reporting on outdated or incomplete data
  • Alert operations teams when ingestion delays occur
  • Build confidence in your ETL/ELT pipeline

Key Freshness Concepts​

TermMeaning
loaded_at_fieldTimestamp column used to measure freshness
warn_afterTime delay after which dbt raises a warning
error_afterTime delay after which dbt raises an error
dbt source freshnessCommand that executes freshness checks

Example Source Definition​

Here is a complete models/schema.yml source definition with freshness configured for customers, orders, and payments.

version: 2

sources:
- name: raw
database: DEMO_DB
schema: RAW
tables:
- name: customers
loaded_at_field: created_at
freshness:
warn_after: {count: 6, period: hour}
error_after: {count: 12, period: hour}
- name: orders
loaded_at_field: updated_at
freshness:
warn_after: {count: 2, period: hour}
error_after: {count: 4, period: hour}
- name: payments
loaded_at_field: payment_date
freshness:
warn_after: {count: 12, period: hour}
error_after: {count: 24, period: hour}

Tip: loaded_at_field should be the timestamp that reflects when the record landed in the source system, not necessarily business event time.


Example Source Table DDL​

Your raw Snowflake source must expose a timestamp column for freshness checks.

CREATE OR REPLACE TABLE DEMO_DB.RAW.ORDERS (
ORDER_ID INT,
CUSTOMER_ID INT,
ORDER_DATE DATE,
STATUS STRING,
AMOUNT NUMBER(10,2),
UPDATED_AT TIMESTAMP
);

If the source table does not already include a timestamp column, add one during ingestion (for example _etl_loaded_at or updated_at).


️ Run Freshness Checks​

Execute freshness for all sources:

dbt source freshness

Run freshness for a specific source:

dbt source freshness --select source:raw

Run freshness for a single table:

dbt source freshness --select source:raw.orders

Example Output​

1 of 1 START source freshness for source raw.orders [RUN]
1 of 1 OK source freshness for source raw.orders [WARN in 1.04s]

Source: raw.orders
Status: WARN
Max loaded_at: 2026-05-06 08:00:00
Age: 3 hours

Run completed with 1 warning.

If the data is too stale, dbt will return ERROR instead of WARN.


Freshness Configuration Patterns​

Set defaults at the source level​

sources:
- name: raw
database: DEMO_DB
schema: RAW
freshness:
warn_after: {count: 24, period: hour}
error_after: {count: 48, period: hour}
tables:
- name: customers
loaded_at_field: created_at
- name: orders
loaded_at_field: updated_at

This config applies the same freshness thresholds to all tables under raw unless a table overrides them.

Override thresholds per table​

      - name: orders
loaded_at_field: updated_at
freshness:
warn_after: {count: 2, period: hour}
error_after: {count: 4, period: hour}

Use this when some sources update more frequently than others.


Best Practices​

  • Use a reliable timestamp column that reflects ingestion or availability time.
  • Set thresholds based on your SLA: hourly sources get hours, daily sources get days.
  • Prefer warn_after and error_after rather than only one of them.
  • Run freshness checks in CI/CD or orchestration tools such as Airflow.
  • Link freshness failures to alerts so teams can investigate quickly.

When to Use Freshness vs. Tests​

  • Use freshness checks when you care about how recently data arrived.
  • Use schema tests to verify table structure and column constraints.
  • Use data tests to validate values and business logic.

Example:

  • Freshness: raw.orders arrived within 2 hours
  • Schema test: customer_id is not null
  • Data test: ORDER_STATUS only contains completed, pending, or cancelled

Summary​

Source freshness tests help you catch upstream delays before they impact downstream analytics. When configured correctly, they are a simple and powerful way to improve data reliability.