What you will be able to do
- Declare warn_after and error_after thresholds on a source and override or remove them per table
- Choose between loaded_at_field, loaded_at_query, a filter and warehouse metadata for measuring the latest load
- Run freshness checks with dbt freshness, select sources, and read freshness.json and sources.json
- Set up a dbt platform job so freshness either blocks or doesn't block the models that follow, and run it often enough for your SLA
Key concept
Freshness threshold — A freshness config tells dbt how old the newest loaded row in a source table may get before a check reports a warning (warn_after) or an error (error_after). dbt finds the latest load timestamp, measures its age against the current time and reports Pass, Warn or Error.
1.Declaring freshness thresholds on a source
Your models are only as current as the tables your Extract and Load tools write into the warehouse. If a loader stalls overnight, every model built on top of it still runs successfully, and the dashboards still show yesterday's numbers. Source freshness catches this. On each source you state how stale its data may get, and dbt checks the most recent load against that limit.
Thresholds live in a freshness block under the source's config:. There are two of them. warn_after is how old the newest data can be before the check reports a warning, and error_after is how old it can be before the check reports an error. Each one takes a count (a positive integer) and a period of minute, hour or day. You can set either threshold or both. If you set neither, dbt doesn't check that resource at all.
Configs apply hierarchically, so most projects set the default once on the source. freshness and loaded_at_field set on the source flow down to every table in it. Tables usually share a loader, so they usually share a load-timestamp column too. A table can override the source-level block with stricter or looser limits of its own. A table that is meant to change rarely, such as a product catalogue, would fail a source-wide 24-hour rule every day. Setting freshness: null on that table removes it from freshness checks while the other tables keep the default.
sources:
- name: jaffle_shop
database: raw
schema: jaffle_shop
config:
freshness:
warn_after: {count: 12, period: hour}
error_after: {count: 24, period: hour}
loaded_at_field: _etl_loaded_at
tables:
- name: orders
- name: product_skus
config:
freshness: null # do not check freshness for this tableCheckpoint 1 of 7· Check yourself
A source sets warn_after 12 hours and error_after 24 hours. Its table orders sets its own warn_after of 6 hours and error_after of 12 hours. Which limits apply to orders?
Table-level freshness overrides the source-level values. The source block applies only to tables that don't set their own.
“A freshness and loaded_at_field set on a source table override the source-level values.”Source: docs.getdbt.com
Checkpoint 2 of 7· Exam question
A source table in the warehouse is loaded incrementally by an hourly pipeline, but the table holds billions of rows with no partition pruning available on the load timestamp column. The current config is: ```yaml sources: - name: raw_events tables: - name: clickstream loaded_at_field: _loaded_at freshness: warn_after: {count: 6, period: hour} error_after: {count: 12, period: hour} ``` Every `dbt source freshness` run against `clickstream` times out because the freshness query scans the entire table to compute `MAX(_loaded_at)`. Which addition to the table's config resolves the timeout without changing the freshness thresholds?
Correct answer: A — Add a `filter` config such as `_loaded_at >= dateadd(day, -2, current_date)` so the freshness query only scans rows loaded within the recent window instead of the full table.
- A. The `filter` config narrows the WHERE clause of the freshness query, so the warehouse only scans the recent partition of rows instead of the whole table. This directly addresses a full-table-scan timeout without touching the warn or error thresholds.
- B. `loaded_at_query` is a real alternative to `loaded_at_field` for building a custom timestamp lookup, but it still executes an aggregate query against the table and gains nothing from being templated in Jinja. It does not reduce the amount of data scanned.
- C. Changing the schedule or threshold controls how often the check runs and when it flags stale data, not how much of the table each individual run has to scan. The timeout would still occur every time the check does execute.
- D. Cascading `loaded_at_field` from the source level down to tables only changes where the setting is declared in YAML; every table still runs its own freshness query against its own rows. It has no effect on scan cost.
2.How dbt finds the latest load timestamp
A threshold means nothing until dbt knows when the data last arrived. dbt has three ways to find that timestamp, and which one it uses depends on what you configure:
| Method | When used | How dbt retrieves the timestamp |
|---|---|---|
| loaded_at_query | When configured on the resource | Runs your custom SQL expression |
| loaded_at_field | When configured on the resource | Queries MAX(<loaded_at_field>) against the relation |
| Adapter metadata | When neither is configured | Reads the last-modified time from the warehouse's relation metadata |
loaded_at_field is the common choice. It names a column, or an expression, that records when each row landed, such as _etl_loaded_at. The expression form handles awkward columns. You can cast a date with "completed_date::timestamp", or convert a local timestamp to UTC with convert_timezone. dbt wraps the column in max() and compares the result with the current timestamp. If you leave it unset, dbt reads warehouse metadata where it can. On Snowflake, for example, metadata freshness comes from the LAST_ALTERED column.
Metadata can be wrong for a table. On BigQuery, metadata-based checks are unreliable for sources with wildcard identifiers such as events_*. The fix is to set loaded_at_field, which switches the table to a query-based check. You can also enable the bigquery_reject_wildcard_metadata_source_freshness flag in dbt_project.yml, which makes dbt raise an error rather than report a misleading result.
loaded_at_query (v1.10 and later) covers cases a single column can't. One example is ignoring trickle loads, so only a day with a real batch counts as fresh. It replaces loaded_at_field, it doesn't add to it, and setting both on the same table is a parse error.
Cost is the last concern. A max() over a huge table can scan all of it. The filter key adds a WHERE clause to the freshness query only, which is useful for partitioned BigQuery tables or large Snowflake, Databricks or Spark tables. Models that select from the source are unaffected, and a filter has no effect on loaded_at_query. With filter: datediff('day', _etl_loaded_at, current_timestamp) < 2 on orders, dbt runs this:
select
max(_etl_loaded_at) as max_loaded_at,
convert_timezone('UTC', current_timestamp()) as snapshotted_at
from raw.jaffle_shop.orders
where datediff('day', _etl_loaded_at, current_timestamp) < 2Checkpoint 3 of 7· Check yourself
An engineer adds loaded_at_query to the orders table but leaves the loaded_at_field: _etl_loaded_at it inherited from the source. They plan to have the query win and fall back to the field. What happens?
The two are alternatives. Configuring both on one resource fails at parse time, so remove the inherited field from that table.
“Setting both loaded_at_query and loaded_at_field on the same resource is a parse error.”Source: docs.getdbt.com
3.Running checks and reading the results
dbt freshness is the command that evaluates thresholds. It covers every source and every model that has a freshness config. Models can also carry thresholds, which is why the command takes a resource-type flag. --resource-type source limits a run to sources. The older dbt source freshness command checks sources only and is still supported for backward compatibility.
# Select all sources in a namespace
dbt freshness --select "source:jaffle_shop"
# Select a specific source table
dbt freshness --select "source:jaffle_shop.orders"The terminal output gives one line per table. A table within its limits shows as Passed, and one past a limit shows as Stale, with how long ago it was last updated. A stale result is more than a log line. dbt exits with a nonzero exit code, and that exit code is what an orchestrator or CI step reacts to.
Results are also saved as files. After every run, dbt writes target/freshness.json, and each entry has a resource_type of source or model. Each entry also records max_loaded_at, snapshotted_at, the gap between the two in seconds (max_loaded_at_time_ago_in_s), a status of Pass, Warn or Error, and the criteria it was judged against. A table whose freshness query fails (a renamed column, say) is logged as an error and left out of the file, so a missing entry needs investigating too. When a run includes sources, dbt also writes target/sources.json, which lists sources only and has no resource_type field. The legacy dbt source freshness command writes only sources.json.
Checkpoint 4 of 7· Fill the gap
Complete the command so it evaluates sources only and skips models that have freshness configs.
dbt freshness ? source--resource-type source includes only sources. --exclude-resource-type source would do the opposite and check only models.
Checkpoint 5 of 7· Exam question
During a `dbt source freshness` run, the `orders` table reports state `error` while the `customers` table reports state `pass`. Which statements accurately describe how dbt determines and records these results? (Select all that apply)(Select 3)
Correct answers: A, B, E — dbt compares the query's `calculated_at` timestamp against the maximum `loaded_at_field` value, then classifies the gap against `warn_after` and `error_after`.; The `error` state on `orders` causes `dbt source freshness` to exit with a nonzero code, which a CI job can use to fail that pipeline step and stop downstream runs.; Both `orders` and `customers` are recorded in `target/sources.json`, each with its own `max_loaded_at`, `snapshotted_at`, and `state` values regardless of the outcome.
- A. This is correct: dbt calculates elapsed time as the gap between the snapshot timestamp and the maximum loaded-at value, then checks that gap against the warn and error thresholds to assign a state. That comparison is the core mechanism behind every freshness result.
- B. This is correct: an error state produces a nonzero process exit code, which is exactly the signal orchestration tools use to mark a job step as failed. That is what lets a CI pipeline stop before running expensive downstream models against stale data.
- C. This is incorrect: dbt writes an entry for every table it checks, whether the result is pass, warn, or error, so passing tables are not dropped from the output. Omitting passing tables would make the artifact useless for auditing overall freshness.
- D. This is incorrect: `warn` is a valid per-table state alongside `pass` and `error`, produced whenever elapsed time exceeds `warn_after` but not yet `error_after`. It is not restricted to source-level configuration.
- E. This is correct: `target/sources.json` includes a record for every table dbt checked, each carrying its own timestamps and state, independent of whether that particular table passed or failed. This is what makes the file a complete freshness report.
- F. This is incorrect: both thresholds are evaluated on every invocation of the command regardless of environment or flags used, and `--select` only narrows which sources are checked, not which threshold type applies.
4.Scheduling freshness checks in dbt jobs
Freshness has to be scheduled on purpose. [dbt build](lesson:1.4) runs models, tests, snapshots and seeds, but it doesn't run freshness checks, however many thresholds you declare. In a dbt platform job you add freshness in one of two ways, and they fail differently.
| Option | Where it runs | If data is stale | Use it when |
|---|---|---|---|
| Run source freshness checkbox (Execution Settings) | Always the first step | Later steps still run | You want freshness reported without blocking the build; a freshness-only job still needs a placeholder step such as dbt compile |
| dbt source freshness as a run step | Anywhere in the step list | The step fails and later steps do not run; email notifications follow this step's end state | As the first step, so models never build on stale data; otherwise put it last or in its own job |
How often you check matters as much as the threshold. A daily check on a table with a one-hour SLA could miss a breach for most of a day. The rule of thumb is to check at least twice as often as your tightest SLA: every 30 minutes for a 1-hour SLA, every 12 hours for a 1-day SLA, and about daily for a 1-week SLA. Scheduled runs also build a history, so you can see freshness trends over time as well as get alerts. In the dbt platform, View Sources on the job page shows the current freshness status.
Freshness results can also decide what gets built. Run dbt freshness --resource-type source first, then run dbt build --select source_status:fresher+. The build then covers only models downstream of sources that have received new data since the last check, so no compute is spent rebuilding unchanged inputs. The source_status selector compares the new results with the previous run's results, which is covered in [state selection](lesson:7.1).
Checkpoint 6 of 7· Match them up
Match each job requirement to the setup that meets it.
Tap a term, then the definition that fits it.
A run step that fails stops the steps after it, which protects models from stale data. The checkbox step never blocks. A job needs at least one step besides the checkbox. source_status:fresher+ narrows the build to models downstream of fresher sources.
“If you wanted your job dedicated exclusively to running freshness checks, you still need to include at least one placeholder step, such as dbt compile.”Source: docs.getdbt.com
Checkpoint 7 of 7· Exam question
A nightly CI/CD job should build models only when their upstream sources have newer data than the previous freshness check, to avoid re-running expensive transformations against sources that have not changed. Which command achieves this using dbt's freshness-based state selection?
Correct answer: A — Run `dbt build --select source_status:fresher+` after `dbt source freshness`, so dbt builds only nodes whose sources loaded newer data since the last freshness result.
- A. This is correct: `source_status:fresher+` is the selection method built specifically for this workflow, comparing the current freshness run against the prior one and selecting only downstream nodes whose sources actually loaded new data. Running the freshness check first is what populates the comparison state it relies on.
- B. `state:modified+` compares project code and manifest artifacts between runs, catching changes like edited SQL or config, not changes in the underlying data volume of a source table. A source getting new rows overnight would not register as `modified` under this selector.
- C. A zero exit code only means no table exceeded its error threshold; it says nothing about whether any table actually received new data since the last check, so this cannot distinguish a genuinely refreshed source from one that is simply still within its stale-tolerance window.
- D. A full refresh recomputes every downstream model from scratch regardless of whether the source changed, which is the opposite of the goal of skipping unnecessary rebuilds and adds significant unnecessary warehouse cost every night.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.dbt build checks source freshness automatically once thresholds are declared in YAML.Why is that wrong?
dbt build never runs freshness checks. You have to add the freshness command as a job step or enable the Run source freshness checkbox.
Covered in Scheduling freshness checks in dbt jobs
2.Enabling the Run source freshness checkbox stops the job's models from building when a source is stale.Why is that wrong?
The checkbox step runs first but doesn't break the steps after it. Only a freshness command added as a run step blocks them when it fails.
Covered in Scheduling freshness checks in dbt jobs
3.Removing the freshness block from one table is the way to exclude it from a source-wide freshness rule.Why is that wrong?
A table without its own block inherits the source-level freshness. To exclude it, set freshness: null on that table.
Covered in Declaring freshness thresholds on a source
4.Adding a filter to the freshness config also limits the rows models see when they select from that source.Why is that wrong?
The filter applies only to the freshness query.
Covered in How dbt finds the latest load timestamp
Practise it for real
Add freshness thresholds to an existing source, run the check, and read the result artifact.
1.In the source's .yml file, add a config: block with freshness (warn_after 12 hours, error_after 24 hours) and loaded_at_field set to your loader's timestamp column.
Why: Values set on the source apply to every table in it, so you configure them once.
You should see: dbt parses without errors or warnings.
2.On one rarely updated table, add config: freshness: null.
Why: Otherwise a table that is meant to be stale fails the source-wide rule on every run.
You should see: That table no longer appears in freshness output.
3.Run dbt freshness --resource-type source.
Why: This evaluates only sources, even if models in the project also have freshness configs.
You should see: One Passed or Stale line per checked table. If any table is stale, the command exits nonzero.
4.Open target/freshness.json and find one of your tables.
Why: The artifact records the measured timestamp, its age and the criteria applied.
You should see: An entry with resource_type source, max_loaded_at, max_loaded_at_time_ago_in_s and a status of Pass, Warn or Error. target/sources.json is written as well.
Stuck? Get a nudge
If a table is missing from freshness.json, its freshness query failed. Check the logs for a bad loaded_at_field.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“One or both of warn_after and error_after can be provided. If neither is set, dbt will not check freshness for that resource.”
↩︎ Declaring freshness thresholds on a source“If loaded_at_field is not provided, dbt calculates freshness via warehouse metadata tables when possible.”
↩︎ How dbt finds the latest load timestamp“On BigQuery, metadata-based freshness checks are not reliable for sources defined with wildcard table identifiers (for example, events_*).”
↩︎ How dbt finds the latest load timestamp“dbt source freshness is a legacy command that checks freshness for sources only.”
↩︎ Running checks and reading the results“Use the freshness config to declare how fresh your source or model data should be.”
↩︎ Key concept“To exclude a source from freshness calculations, explicitly set freshness: null.”
↩︎ Exam trap 3“Does not affect other uses of the source or model. Does not apply to loaded_at_query.”
↩︎ Exam trap 4“setting only one issues a parse warning but causes an error when dbt freshness runs.”
↩︎ Prediction“A freshness and loaded_at_field set on a source table override the source-level values.”
↩︎ Checkpoint“Setting both loaded_at_query and loaded_at_field on the same resource is a parse error.”
↩︎ Checkpoint - 2.https://docs.getdbt.com/docs/build/sourcesOfficial docs
“If you've set a freshness property at the source level, this table is likely to fail checks.”
↩︎ Declaring freshness thresholds on a source“Use the dbt build --select source_status:fresher+ command to build and test models downstream of fresher sources.”
↩︎ Scheduling freshness checks in dbt jobs - 3.https://docs.getdbt.com/docs/deploy/source-freshnessOfficial docs
“Source freshness for Snowflake is calculated using the LAST_ALTERED column.”
↩︎ How dbt finds the latest load timestamp“you should run your source freshness jobs with at least double the frequency of your lowest SLA.”
↩︎ Scheduling freshness checks in dbt jobs“dbt build does not include source freshness checks when building and testing resources in your DAG.”
↩︎ Exam trap 1“subsequent steps will not run”
↩︎ Exam trap 2“will run dbt source freshness as the first step in your job and won't break subsequent steps if it fails”
↩︎ Prediction“If you wanted your job dedicated exclusively to running freshness checks, you still need to include at least one placeholder step, such as dbt compile.”
↩︎ Checkpoint - 4.https://docs.getdbt.com/reference/commands/sourceOfficial docs
“If a table is stale, dbt reports a warning or error accordingly and exits with a nonzero exit code.”
↩︎ Running checks and reading the results - 5.
“If the freshness query fails, dbt logs an error but omits the node from the file rather than writing it with a failed status.”
↩︎ Running checks and reading the results - 6.https://docs.getdbt.com/reference/commands/freshnessOfficial docs
“whenever sources are included in a dbt freshness run, dbt also writes target/sources.json.”
↩︎ Running checks and reading the results