In dbt, how does the source freshness check decide a raw table is stale?

## What the command actually does `dbt source freshness` is a standalone command. For every source table that has freshness configured, dbt issues a small query against the warehouse of roughly this shape: ```sql select max(_etl_loaded_at) as max_loaded_at, current_timestamp as snapshotted_at from raw.jaffle_shop.orders ``` It then computes the lag between `max_loaded_at` and `snapshotted_at` and grades it against the thresholds you declared. Nothing is transformed and nothing is written to your models; the only output is a verdict per table plus an artifact. ## The configuration ```yaml sources: - name: jaffle_shop database: raw schema: jaffle_shop freshness: # source-level default warn_after: {count: 12, period: hour} error_after: {count: 24, period: hour} loaded_at_field: _etl_loaded_at tables: - name: orders - name: pricing_reference freshness: null # opt this table out ``` `period` accepts `minute`, `hour` or `day`. Both `freshness` and `loaded_at_field` can be set once on the source and overridden per table, and setting `freshness: null` on a table opts it out — useful for genuinely static reference tables where a stale warning is noise. ## Choosing loaded_at_field This is the decision that determines whether the check means anything. The column should record **when the row landed in the warehouse**, not when the business event happened. Ingestion tools usually provide one (`_etl_loaded_at`, `_fivetran_synced`, `_airbyte_extracted_at`), or your load job can stamp one. If you point `loaded_at_field` at an event timestamp such as `order_placed_at`, the check reports on customer behaviour rather than on your pipeline: a quiet Sunday night with no orders produces a stale alert even though ingestion is perfectly healthy, and a pipeline that has been dead for a day can still look fresh if it happens to have loaded a batch containing future-dated events. Two further gotchas. The column must be castable to a timestamp; dbt compares in UTC, so a naive local-time column produces a constant offset that silently shifts every threshold. And the field must be indexed or partitioned in a way that makes `max()` cheap, which leads directly to the next point. ## The filter key On a large partitioned table, `select max(loaded_at_field)` can be a full scan and a real bill. The `filter` key injects a `where` clause into the freshness query: ```yaml freshness: warn_after: {count: 1, period: hour} filter: _etl_loaded_at >= dateadd(day, -3, current_date) ``` Now the query only touches the last three days of partitions. The tradeoff is that if the table goes stale for longer than the filter window, `max()` returns null and the check reports an error — which for freshness purposes is the correct outcome anyway. ## Metadata-based freshness Newer dbt versions can, on some adapters, compute freshness from warehouse metadata (a table's last-modified time in the information schema) with no `loaded_at_field` at all. That avoids scanning the table entirely, but it measures when the *table object* was last written, which is not the same as when the newest row arrived — a full-refresh rewrite of old data will look fresh. Treat it as a cheap approximation and keep an explicit `loaded_at_field` where the distinction matters. Check what your adapter and dbt version support before relying on it. ## Outputs Each table gets one of three states. `pass` means the lag is under both thresholds. `warn` means it exceeded `warn_after` but not `error_after`. `error` means it exceeded `error_after`. Results are printed and written to a `sources.json` artifact in the target directory, which is what tooling reads to render freshness dashboards, and what state-comparison selection uses to find sources that received new data since the last run. ## Selecting what to check The usual selectors apply: `dbt source freshness --select source:jaffle_shop` checks one source, `source:jaffle_shop.orders` one table. Teams typically run the whole check on a schedule that matches their tightest SLA rather than on every deploy. ## Setting thresholds that are worth having Thresholds should encode the promise you made to consumers, not the pipeline's best case. If the hourly load usually finishes in eight minutes, `warn_after: 1 hour` fires on every hiccup and gets muted within a week. Set `warn_after` where a human should look and `error_after` where a downstream consumer would be misled, and expect the two to be hours apart for a daily pipeline.