Source freshness checks in dbt - Step-by-Step Execution

Concept Flow - Source freshness checks

Define source in dbt project

↓

Configure freshness criteria

↓

Run dbt source freshness command

↓

dbt queries source metadata

↓

Compare source timestamps to criteria

↓

Report freshness status

↓

Take action if stale or warn

↓

END

This flow shows how dbt checks source data freshness by defining sources, setting freshness rules, running checks, and reporting results.

Execution Sample

dbt

sources:
  - name: my_source
    tables:
      - name: users
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}

This YAML config defines a source table with freshness thresholds for warnings and errors.

Execution Table

Step	Action	Source Timestamp	Current Time	Age (hours)	Check Result	Message
1	Start freshness check	-	2024-06-01 12:00	-	Pending	Starting check for source 'users'
2	Query source metadata	2024-06-01 01:30	2024-06-01 12:00	10.5	Within threshold	Source data is fresh
3	Compare to warn_after (12h)	10.5 < 12	N/A	True	No warning	No freshness warning needed
4	Compare to error_after (24h)	10.5 < 24	N/A	True	No error	No freshness error needed
5	Finish check	-	-	-	Success	Source freshness check passed

💡 Source timestamp age 10.5 hours is less than warn_after 12 hours, so freshness is good.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
source_timestamp	-	2024-06-01 01:30	2024-06-01 01:30	2024-06-01 01:30	2024-06-01 01:30
current_time	2024-06-01 12:00	2024-06-01 12:00	2024-06-01 12:00	2024-06-01 12:00	2024-06-01 12:00
age_hours	-	10.5	10.5	10.5	10.5
check_result	Pending	Within threshold	No warning	No error	Success

Key Moments - 2 Insights

Why does dbt compare the source timestamp age to both warn_after and error_after?

dbt uses warn_after to trigger a warning if data is getting old, and error_after to fail the check if data is too stale. This two-level check helps catch issues early (see execution_table rows 3 and 4).

What happens if the source timestamp is missing or null?

If the timestamp is missing, dbt cannot calculate freshness age, so the check usually fails or warns. This is important because freshness depends on having a valid timestamp (not shown in this trace but common in practice).

Visual Quiz - 3 Questions

Look at the execution table, what is the age in hours of the source data at step 2?

A 12

B 24

C 10.5

D 1.5

💡 Hint

Check the 'Age (hours)' column at step 2 in the execution_table.

At which step does dbt decide that no freshness warning is needed?

A Step 3

B Step 2

C Step 4

D Step 5

💡 Hint

Look for the 'Check Result' and 'Message' columns mentioning warnings in the execution_table.

If the source timestamp was 13 hours old instead of 10.5, what would change in the execution table?

A Step 5 would say freshness check failed

B Step 3 would show a warning triggered

C Step 4 would show an error triggered

D No changes, still passes

💡 Hint

Compare age_hours to warn_after threshold in execution_table rows 3 and 4.

Concept Snapshot

Source freshness checks in dbt:
- Define sources and tables in YAML
- Set freshness thresholds: warn_after and error_after
- Run 'dbt source freshness' to check timestamps
- Compare source data age to thresholds
- Report status: fresh, warning, or error
- Helps monitor data timeliness automatically

Full Transcript

Source freshness checks in dbt help ensure your data is up-to-date. You define your source tables and set freshness rules like warn_after and error_after in your dbt project YAML. When you run the freshness check, dbt queries the source metadata to get the latest timestamp. It calculates how old the data is by comparing the source timestamp to the current time. Then it compares this age to your thresholds. If the data is younger than warn_after, it passes with no warnings. If it is older than warn_after but younger than error_after, dbt issues a warning. If it is older than error_after, it triggers an error. This process helps catch stale data early and keeps your analytics reliable.