6. Sources, seeds and snapshots

Three resource types that are not models. Each solves a problem models cannot.

Sources, and freshness

Chapter 3 introduced source(). The feature that earns its keep is freshness.

sources:
  - name: jaffle_shop
    database: raw
    schema: jaffle_shop
    loaded_at_field: _fivetran_synced
    freshness:
      warn_after:  {count: 12, period: hour}
      error_after: {count: 24, period: hour}

    tables:
      - name: raw_orders
        freshness:                      # override per table
          warn_after:  {count: 1, period: hour}
          error_after: {count: 6, period: hour}

      - name: country_codes
        freshness: null                 # never changes; don't check

dbt source freshness

For each table dbt runs roughly:

select max(_fivetran_synced) as max_loaded_at,
       current_timestamp     as snapshotted_at
from raw.jaffle_shop.raw_orders

and compares the gap to your thresholds. Results land in target/sources.json.

Why this matters more than it looks. Without it, a broken loader is invisible. Fivetran stops syncing at 02:00, dbt runs happily at 06:00 over yesterday’s data, every test passes — because the data is internally consistent, just old — and someone spots it at lunchtime.

   ✗ WITHOUT FRESHNESS
     loader breaks → dbt succeeds → tests pass
     → dashboards quietly show yesterday
     → discovered by a human, hours later

   ✓ WITH FRESHNESS
     loader breaks → freshness errors
     → the pipeline stops before it builds
       anything on stale input

Run freshness before the models, not after. The common pattern:

dbt source freshness || exit 1
dbt build

There is also a selector that runs only what a fresh source affects:

dbt build --select "source_status:fresher+"

which needs a previous sources.json to compare against. Chapter 12 covers state comparison properly.

On loaded_at_field. It must be a column reflecting when the row was loaded, not when the business event happened. order_date is wrong — a day with no orders looks like a broken loader. Loader-added metadata columns (_fivetran_synced, _airbyte_extracted_at) are what you want. Some adapters can use warehouse metadata instead, letting you omit the field entirely.

Seeds

CSV files in seeds/, loaded into the warehouse by dbt seed.

   seeds/country_codes.csv

   country_code,country_name,region
   GB,United Kingdom,EMEA
   US,United States,AMER
   JP,Japan,APAC

dbt seed

Creates a table country_codes, referenced with {{ ref('country_codes') }} exactly like a model.

Configured like anything else:

seeds:
  jaffle_shop:
    country_codes:
      +column_types:
        country_code: varchar(2)
      +quote_columns: false

Set column_types for anything ambiguous. dbt infers types from the CSV, and inference gets leading zeros wrong — a postcode column of 01234 becomes the integer 1234. Declare the type and the problem disappears.

What seeds are for

   ✓ GOOD SEEDS
     country / currency code lookups
     mapping tables maintained by hand
     a list of internal email domains to exclude
     test fixtures
     small, static, business-owned reference data

   ✗ BAD SEEDS
     anything with real data volume
     anything that changes often
     anything with PII
     an export from another system
     "we'll just load this CSV once"

The line is roughly a few thousand rows. dbt seed generates literal INSERT statements; a large CSV produces enormous SQL and a slow, fragile load. It is not a data loading tool.

The real value is that a seed is in git. When the finance team changes a regional mapping, it is a pull request with a diff and a reviewer — not an untracked edit to a warehouse table. That auditability is the point.

Snapshots

The most conceptually interesting thing in dbt, and the one most projects need exactly once.

The problem

Your source table stores current state:

   raw_orders

   order_id   status      updated_at
   1001       shipped     2026-09-08 14:00

Yesterday that row said pending. The day before, placed. Those values are gone — the app updated the row in place. Nobody can answer “how long do orders sit in pending?” because the history was never recorded.

A model cannot fix this. Every dbt run sees only current state.

What a snapshot does

-- snapshots/orders_snapshot.sql

{% snapshot orders_snapshot %}

{{ config(
    target_schema = 'snapshots',
    unique_key    = 'order_id',
    strategy      = 'timestamp',
    updated_at    = 'updated_at'
) }}

select * from {{ source('jaffle_shop', 'raw_orders') }}

{% endsnapshot %}

dbt snapshot

Each run compares the source against what the snapshot table already holds. Where a row has changed, it closes the old version and opens a new one — Slowly Changing Dimension Type 2.

   after three days of dbt snapshot:

   order_id status   dbt_valid_from     dbt_valid_to
   1001     placed   2026-09-06 03:00   2026-09-07 03:00
   1001     pending  2026-09-07 03:00   2026-09-08 03:00
   1001     shipped  2026-09-08 03:00   NULL   ← current

dbt_valid_to IS NULL marks the current version. dbt also adds dbt_scd_id and dbt_updated_at.

Now the question is answerable:

select avg(dbt_valid_to - dbt_valid_from)
from {{ ref('orders_snapshot') }}
where status = 'pending' and dbt_valid_to is not null

The two strategies

timestamp — trust a column.

strategy   = 'timestamp',
updated_at = 'updated_at'

If updated_at is newer than the recorded version, the row changed. Cheap: one column compared. Requires the source to maintain that column reliably. If the app ever updates a row without touching updated_at, the change is invisible forever.

check — compare values.

strategy    = 'check',
check_cols  = ['status', 'amount', 'shipping_address']
-- or: check_cols = 'all'

Compares the listed columns directly. No timestamp needed. More expensive, and check_cols = 'all' will create a new version whenever any column moves — including meaningless loader metadata, which produces a new row every single run.

Use timestamp when the column is trustworthy, check on an explicit column list otherwise. Avoid check_cols = 'all' unless you know every column is meaningful.

Deletes

By default, a row deleted at source simply stops appearing and its snapshot version stays open forever — permanently “current”, which is wrong.

invalidate_hard_deletes = true

dbt then closes out rows that have vanished, setting dbt_valid_to. Turn this on unless you know deletes cannot happen.

The rules of snapshots

Snapshots are the one part of dbt you cannot rebuild. Every model can be dropped and recreated from source. A snapshot is the history — drop it and the past is gone, because the source never had it.

That leads to real operational rules:

• Never --full-refresh a snapshot without understanding that you are deleting history.
• Run them on a schedule, reliably. A snapshot that runs daily records daily granularity. Miss three days and those three days never existed. Sub-daily change between runs is invisible.
• Back them up like the irreplaceable data they are.
• Snapshot the source, not a model. Snapshotting a model means a change to your transformation logic pollutes the history with fake events. Take the raw table, and do the transformations downstream of the snapshot.
• Keep the select trivial. select * from {{ source(...) }} and nothing else — no joins, no filters, no casts. Anything you add becomes part of what counts as “changed”.

YAML snapshots

dbt 1.9 added a YAML form, which is nicer to read and lets snapshots use the normal config mechanisms:

snapshots:
  - name: orders_snapshot
    relation: source('jaffle_shop', 'raw_orders')
    config:
      unique_key: order_id
      strategy: timestamp
      updated_at: updated_at

Same behaviour. The SQL block form still works.

dbt build, and the order of things

Four resource types, and the command that runs them all in dependency order:

dbt build

dbt build is seed + snapshot + run + test, interleaved by the graph — and critically, it tests each node as it is built. A model whose test fails does not have its children built, so bad data stops rather than propagating.

   dbt run   then  dbt test
   ─────────────────────────
   build everything, then check
   → failures found after the bad data
     is already in every downstream table

   dbt build
   ─────────────────────────
   build a node, test it, then build
   its children
   → failures stop the branch

Use dbt build in production. dbt run and dbt test separately is an older pattern that runs everything before noticing anything is wrong.

Ordering caveat: snapshots read sources, so they generally run before the models that depend on them — which is what you want, since a snapshot should capture state as it was found, not after a transformation.

What to take from this chapter

• Freshness checks turn a silently-stale pipeline into a loud failure. Run dbt source freshness before building, not after.
• loaded_at_field must be a load timestamp, not a business date — otherwise a quiet day looks like an outage.
• Seeds are small, static, hand-maintained reference data in git. A few thousand rows at most; set column_types to protect leading zeros. Not a loading tool.
• Snapshots record history the source overwrites, as SCD Type 2 with dbt_valid_from / dbt_valid_to.
• timestamp strategy when the column is trustworthy; check on an explicit column list otherwise. Avoid check_cols = 'all'.
• Set invalidate_hard_deletes = true unless deletes are impossible.
• A snapshot cannot be rebuilt. Never full-refresh one casually, run them reliably on schedule, snapshot the raw source with a trivial select, and back them up.
• dbt build tests each node before building its children — bad data stops instead of spreading. Prefer it to run then test.

Next: a dbt run traced end to end — parse, compile, the DAG, execution, the adapter, and the artifacts that come out.