Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 41 ยท Sources, Seeds and Snapshots

Sources and Freshness

Declare raw tables as dbt sources, read them with source(), add tests and descriptions, and monitor data freshness with loaded_at_field, warn_after and error_after.

Every dbt project starts from data that dbt did not create: tables loaded by Fivetran, Airbyte, an application database replica, or a file import. dbt calls these sources. Declaring them in YAML gives raw tables names, descriptions, tests and a place in the lineage graph, and it lets you check whether the loading pipeline is still delivering fresh data.

Declaring a source

Sources are described in a YAML file, usually next to the staging models that use them:

YAML
# models/staging/shop/_shop__sources.yml
version: 2

sources:
  - name: shop
    description: "Replica of the shop application database."
    database: raw          # optional: where the tables live
    schema: shop_app       # the schema in the warehouse
    tables:
      - name: orders
        description: "One row per order placed."
        columns:
          - name: id
            description: "Primary key."
            data_tests:
              - unique
              - not_null
      - name: customers
      - name: order_items
        identifier: order_item_rows   # real table name, if different
  • name (the source) is a label you choose, usually the system name.
  • schema and database say where the raw tables are. If you omit schema, dbt uses the source name.
  • Each table gets a name; use identifier when the warehouse table has a different name (for example a name with spaces or a prefix).

Reading a source with source()

Use source('source_name', 'table_name') instead of a hard-coded name:

SQL
-- models/staging/shop/stg_shop__orders.sql
select
    id as order_id,
    customer_id,
    ordered_at,
    status
from {{ source('shop', 'orders') }}

It compiles to something like raw.shop_app.orders. As with ref(), you get three benefits: the raw name lives in one place, the lineage graph starts at sources, and dbt knows what depends on what. If a raw table is renamed, you change one YAML line.

Here is the mechanism in a stand-in with real YAML and Jinja (not dbt): it parses the source definition and resolves source() calls, including identifier.

Python Stand-in: resolving source()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Testing and documenting sources

Raw data breaks. A column that was always unique starts to repeat, or a key arrives null. Putting tests on sources catches the problem at the boundary, before it spreads through every model. The same data_tests: syntax works under source columns, and dbt test --select source:shop runs them.

A test is just a query that returns the failing rows. For example, "is id unique in orders":

SQL What a source test checks ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Both queries return nothing wrong for this clean sample, which is a passing test. The next chapter on tests covers writing your own.

Source freshness

Imagine your loader stalls on Tuesday and nobody notices. On Friday, the dashboard still renders, but with old data. Freshness checks catch this. You tell dbt which column records when a row was loaded and how old is too old:

YAML
sources:
  - name: shop
    schema: shop_app
    config:
      loaded_at_field: _loaded_at
      freshness:
        warn_after: { count: 6, period: hour }
        error_after: { count: 24, period: hour }
    tables:
      - name: orders
      - name: customers
        config:
          freshness:
            warn_after: { count: 7, period: day }
      - name: country_codes
        config:
          freshness: null          # do not check this one

Run it with:

Terminal
dbt source freshness

dbt computes max(_loaded_at) for each table, subtracts it from the current time, and compares the age with the thresholds. The outcome is pass, warn or error, and results go to target/sources.json. In production, schedule dbt source freshness before dbt build; if the data is stale, stop the run and alert someone.

Table-level settings override source-level ones, and setting freshness: null turns it off. (Older projects write loaded_at_field and freshness without the config: wrapper; both spellings work in current dbt versions.)

Here is the freshness query reproduced in SQL. We treat 2025-12-29 as "now" and use ordered_at as the loaded-at column:

SQL The freshness calculation ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The newest order is 24.3 hours old at the check time, just past the 24-hour error threshold, so the status is error. Change the check time to see the warn and pass outcomes. A real source uses a dedicated load timestamp, because ordered_at only tells you when customers last bought something, not when the pipeline last ran.

Using source freshness in selection

Because sources are nodes, you can select on them:

Terminal
dbt build --select source:shop+                      # all models downstream of the shop source
dbt build --select source_status:fresher+ --state ./prev   # only models with newer source data

The second form lets a pipeline skip models whose sources did not change since the previous run.

Best practices

  • One YAML file per source system, next to its staging models, named with a leading underscore (_shop__sources.yml).
  • Reference each source table from one staging model only. Everything else uses ref() on that model.
  • Add unique and not_null tests to primary keys, and freshness to every table that is loaded regularly.
  • Document what the raw columns mean while you are looking at them; future you will be grateful.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does source('shop', 'orders') refer to?

    source() resolves to a raw table declared in a sources YAML file and adds it to the lineage graph.
  2. 2. Which field tells dbt when a row was loaded for freshness checks?

    loaded_at_field names the timestamp column dbt uses to compute how old the data is.
  3. 3. Which command checks whether sources are fresh?

    dbt source freshness compares the newest loaded row with the thresholds you configured.
  4. 4. Why use identifier in a source table definition?

    identifier maps the friendly source table name to the real object name in the warehouse.

Summary

  • Sources declare raw tables in YAML; source('name', 'table') replaces hard-coded names and adds them to the DAG.
  • Put tests and descriptions on sources to catch problems at the boundary.
  • Freshness uses loaded_at_field with warn_after and error_after, checked by dbt source freshness.
  • Read each source table in exactly one staging model.

Next: Seeds: Version-Controlled CSV Data