A dashboard can look correct while displaying outdated information. Sales figures may stop updating, customer records may arrive late, or an upstream data pipeline may fail without immediately breaking downstream queries.
These problems are difficult to detect when a data warehouse still contains the last successfully loaded records. A dashboard can continue displaying numbers even though the source data has not changed for hours or days.
dbt provides source freshness checks that help data teams detect these situations. By defining freshness rules for data sources, teams can check when data was last updated and identify sources that have fallen behind their expected schedule.
In this article, you will learn how dbt source freshness works, how to configure freshness rules, how to run freshness checks, and how to use the results in a data pipeline.
What Is dbt Source Freshness?
dbt is a tool used to transform data inside a data warehouse or supported data platform. It allows teams to define transformations using SQL, organize dependencies between models, and test data assumptions.
Source freshness checks help determine whether data in a source table is recent enough for downstream use.
For example, suppose an application loads orders into a warehouse every 15 minutes. A reporting dashboard depends on that table to show current sales.
If the last order record was updated three hours ago, the dashboard might be showing stale information.
A freshness check can compare the latest relevant timestamp in the source table with a configured threshold. Based on the result, dbt reports whether the source meets the expected freshness requirement.
Source freshness does not automatically prove that every row is correct or that the entire upstream pipeline is healthy. It checks the recency of data according to the configured rule.
Why Source Freshness Matters
Data pipelines often involve several systems:
An application generates business data.
An ingestion process transfers that data to a warehouse.
dbt transforms the raw data into reporting models.
A dashboard reads the transformed data.
A failure at any upstream stage can leave downstream models working with older records.
For example, an ingestion job might fail because of expired credentials. The raw table remains available, so downstream transformations may continue to run successfully. However, the resulting dashboard no longer reflects recent business activity.
Source freshness checks help identify this problem before stale data goes unnoticed.
Common situations where freshness monitoring is useful include:
Sales dashboards that depend on frequently updated order data.
Financial reports that require regular transaction updates.
Customer analytics that depend on application event streams.
Inventory reports that rely on warehouse ingestion.
Marketing dashboards that use advertising or campaign data.
The acceptable delay depends on the business requirement. A dashboard used for daily reporting may tolerate data that is several hours old, while an operational dashboard may require much more frequent updates.
Step 1: Define a Source in dbt
Before checking freshness, define the source table in your dbt project.
Suppose your warehouse contains a table named raw_orders in the raw schema.
Create or update a source YAML file, such as models/sources.yml:
version: 2
sources:
- name: ecommerce
schema: raw
tables:
- name: raw_orders
This configuration tells dbt that raw_orders is an external source table belonging to the ecommerce source definition.
The schema value identifies the warehouse schema containing the table. Depending on your adapter and project configuration, you may also need to specify a database or other source properties.
You can reference this source in a model using:
SELECT *
FROM {{ source('ecommerce', 'raw_orders') }}
The source() function establishes a dependency on the declared source and allows dbt to resolve the appropriate relation.
At this stage, the source is defined, but no freshness rule has been configured.
Step 2: Add a Freshness Rule
To check source freshness, configure a timestamp column and one or more freshness thresholds.
Suppose raw_orders has a column named loaded_at that records when each record was loaded into the warehouse.
Update the source configuration:
version: 2
sources:
- name: ecommerce
schema: raw
tables:
- name: raw_orders
loaded_at_field: loaded_at
freshness:
warn_after:
count: 30
period: minute
error_after:
count: 60
period: minute
This configuration defines two thresholds:
Threshold | Value | Meaning |
|---|
Warning | 30 minutes | Data older than this threshold should trigger a warning |
Error | 60 minutes | Data older than this threshold should trigger an error |
The loaded_at_field identifies the timestamp dbt uses to evaluate freshness. dbt generally evaluates the most recent value in that field against the configured thresholds.
If the latest timestamp is 20 minutes old, the source is within the warning threshold.
If it is 45 minutes old, the source exceeds the warning threshold but remains within the error threshold.
If it is 90 minutes old, it exceeds the error threshold.
The exact reporting behavior can depend on the dbt version and command used, so verify how your installed version represents warning and error states.
Choose the Correct Timestamp Column
The timestamp field is one of the most important parts of a freshness configuration.
Consider a table with these columns:
Column | Meaning |
|---|
created_at
| When the business event occurred |
updated_at
| When the record was last modified |
loaded_at
| When the record was loaded into the warehouse |
These fields measure different things.
If your goal is to detect ingestion delays, loaded_at may be the most appropriate field.
If your goal is to detect whether new business events are arriving, created_at may be more useful, provided its values reflect the relevant event stream.
Using the wrong timestamp can produce misleading results. For example, an old record might be updated today even though new transactions have stopped arriving.
Choose a field that represents the freshness requirement you want to monitor.
Step 3: Run the Freshness Check
Once the source and freshness rules are configured, run:
dbt source freshness
This command evaluates freshness for eligible sources in the project.
The output reports the status of the configured sources, including whether their latest timestamps meet the specified thresholds.
For example, the results might indicate that:
These are illustrative results. Actual statuses depend on the timestamp values, configured thresholds, adapter behavior, and dbt version.
Check a Specific Source
For a large project, you may want to check only the source used by a particular pipeline.
You can select a source by its name:
dbt source freshness --select source:ecommerce
This targets the ecommerce source and its eligible tables.
If your project uses different selection syntax or a version with changed CLI behavior, consult the documentation for that version and verify the selected resources before using the command in production.
Targeted freshness checks can reduce unnecessary work when a pipeline depends on only a subset of the project's sources.
Step 4: Understand Freshness Results
A freshness check tells you whether the configured timestamp is recent enough. It does not automatically explain why the data is late.
For example, consider the following situations:
Observed result | Possible explanation | Recommended investigation |
|---|
Freshness check passes | Recent data is available | Continue with downstream processing |
Warning threshold exceeded | Ingestion may be delayed | Check ingestion job status and expected schedule |
Error threshold exceeded | Source may be seriously delayed | Investigate the upstream pipeline before publishing data |
No usable timestamp is available | Timestamp configuration or source data may be invalid | Inspect the configured field and source table |
Freshness check fails unexpectedly | Query, permissions, or adapter issue may exist | Review logs and warehouse permissions |
The correct response depends on the business impact.
A delayed marketing report may be acceptable for a short period. A delayed financial reconciliation process may require immediate investigation.
Freshness thresholds should reflect the expected update schedule and the consequences of using outdated data.
Step 5: Integrate Freshness Checks Into Your Pipeline
Running freshness checks manually is useful during development, but production pipelines should execute them automatically.
A common approach is to run freshness checks before building downstream models that depend on the source.
For example:
dbt source freshness
dbt build --select source:ecommerce+
The first command checks the configured sources. The second command selects the source and its downstream resources according to dbt's selection syntax.
However, running these commands in sequence does not automatically guarantee that every downstream model will be blocked whenever a freshness warning or error occurs.
Your orchestration system must interpret the freshness result and apply the intended policy. If a source is critically stale, the pipeline should stop before publishing data that must meet a strict freshness requirement.
The appropriate implementation depends on your dbt version, orchestration platform, and how freshness results are exposed to the pipeline.
Example CI or Orchestration Policy
A typical policy might look like this:
Start the ingestion process.
Wait for ingestion to finish or reach its defined completion state.
Run dbt source freshness checks.
Stop the pipeline if a required source exceeds its error threshold.
Continue to transformations if the required sources pass the policy.
Publish or refresh the dashboard after the downstream models succeed.
This separates the freshness check from the decision about whether the business process should continue.
For example, a warning might allow a non-critical report to continue while generating an alert. An error might block a high-priority dashboard refresh until the source is healthy again.
Step 6: Use Freshness Checks With Data Tests
Freshness and data quality are related, but they solve different problems.
A freshness check evaluates how recent the relevant source data is. A data test evaluates whether data satisfies a particular condition.
For example, a source may have been updated recently but contain invalid order identifiers.
You can define a source-level test in YAML:
version: 2
sources:
- name: ecommerce
schema: raw
tables:
- name: raw_orders
columns:
- name: order_id
data_tests:
- not_null
This example uses the current data_tests property supported by recent dbt versions. Projects using older versions may use the legacy tests property instead.
The test checks that order_id is not null in the source data.
You can run the source test with a command such as:
dbt test --select source:ecommerce
Review the selection behavior and test syntax supported by your installed dbt version.
Combining freshness checks with data tests provides a more complete view of source health:
Freshness checks help detect delayed data.
Data tests help detect invalid or unexpected data.
Pipeline monitoring helps detect failed jobs and infrastructure problems.
No single check replaces the others.
Step 7: Monitor Freshness Over Time
A source that repeatedly approaches its freshness threshold may indicate a reliability problem even if it rarely crosses the error limit.
For example, suppose a source is expected to update every 15 minutes but frequently takes 28 minutes to refresh. A warning threshold of 30 minutes might allow the source to pass most checks while leaving little room for additional delays.
Review freshness results over time and adjust the thresholds based on actual operating conditions.
Useful monitoring information includes:
The expected ingestion frequency.
The latest observed source timestamp.
The freshness status for each run.
The frequency of warnings and errors.
The duration of upstream ingestion jobs.
The time between ingestion completion and dashboard refresh.
Where possible, send alerts to the team responsible for the source pipeline. An alert should identify the affected source, the observed delay, and the expected update schedule.
Avoid setting thresholds so tightly that ordinary variations create constant alerts. Equally, thresholds should not be so generous that meaningful delays go unnoticed.
Best Practices for dbt Source Freshness
Use a Timestamp That Matches the Business Requirement
Select a field that measures the kind of freshness you need to monitor. Ingestion timestamps, event timestamps, and modification timestamps are not interchangeable.
Set Realistic Thresholds
Base warning and error thresholds on the source's expected update schedule, normal ingestion delays, and business requirements.
Check Freshness Before Publishing Critical Data
For dashboards that require current data, make freshness a defined condition in the pipeline rather than relying on someone to inspect a report manually.
Combine Freshness With Data Quality Tests
A recent timestamp does not prove that the data is correct. Validate important identifiers, required fields, and business rules separately.
Investigate Repeated Warnings
Frequent warnings may reveal a source that is approaching its operational limit. Investigate the pattern rather than waiting for a complete failure.
Keep Monitoring Ownership Clear
Each critical source should have an identified owner responsible for investigating delays and restoring the expected update schedule.
Common Mistakes to Avoid
Using the Wrong Timestamp
A timestamp that records when a business event occurred may not accurately measure when data was loaded into the warehouse.
Choose the field based on the purpose of the check.
Assuming Freshness Means Data Quality
A source can be fresh and still contain duplicate records, null identifiers, or invalid values. Use separate tests for these conditions.
Assuming Every Warning Stops the Pipeline
A warning and an error may have different meanings in your orchestration policy. Explicitly define which statuses allow processing to continue and which statuses block publication.
Ignoring Sources That Update Infrequently
Not every source should be expected to update every few minutes. A monthly reference table may be healthy even if its timestamp is several days old.
Configure freshness expectations according to the source's actual update pattern.
Running Freshness Checks Only After Transformations
If the purpose is to prevent stale data from reaching a dashboard, checking only after all transformations have completed may detect the problem too late.
Run the check at a point where its result can still influence the pipeline.
Advantages and Disadvantages
Advantages
Detects stale source data before it silently affects downstream reporting.
Allows teams to define different freshness thresholds for different sources.
Integrates with dbt source definitions and automated data pipelines.
Helps identify ingestion delays and recurring reliability problems.
Complements data quality tests and pipeline monitoring.
Disadvantages
Requires a suitable timestamp field.
Thresholds must be maintained as source schedules change.
Freshness checks do not identify every cause of a delay.
A passing check does not guarantee that the data is complete or correct.
Pipeline orchestration must define how warnings and errors affect downstream processing.
Summary
dbt source freshness helps data teams detect when source tables have not been updated within an expected time window. By configuring a suitable timestamp field and warning and error thresholds, you can identify delayed data before it silently affects reports and dashboards.
The practical workflow is to define the source, configure freshness rules, run dbt source freshness, and integrate the results into the orchestration process.
For stronger data reliability, combine freshness checks with data quality tests, ingestion monitoring, and clear rules about when downstream processing should continue.
The goal is not simply to confirm that a table contains recent timestamps. It is to ensure that the data used for business decisions meets the freshness requirements the organization has defined.
Join the conversation! Your thoughts help the community grow.