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
| Term | Meaning |
|---|---|
loaded_at_field | Timestamp column used to measure freshness |
warn_after | Time delay after which dbt raises a warning |
error_after | Time delay after which dbt raises an error |
dbt source freshness | Command 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_fieldshould 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_afteranderror_afterrather 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.ordersarrived within 2 hours - Schema test:
customer_idis not null - Data test:
ORDER_STATUSonly containscompleted,pending, orcancelled
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.