Skip to main content

dbt Models and Sources

dbt turns SQL select statements into a dependency graph of models. A source names data loaded by another system; source() records that boundary. ref() names an upstream dbt model and creates a dependency. This lesson targets stable dbt Core 1.12.4 syntax checked on 2026-10-15; dbt 2.0 was still a prerelease and is not assumed.

Prerequisites​

Complete Slowly Changing Dimensions, ETL vs. ELT, and Incremental Processing.

Learning goals​

  • Declare and document an external source.
  • Stage source columns behind a stable model interface.
  • Configure an incremental model with key, overlap, and schema policy.
  • Know when a full refresh is required.

Worked example​

Define the external boundary in a YAML file under a model path. identifier can map a readable project name to a physical table name.

version: 2
sources:
  - name: raw
    schema: raw
    tables:
      - name: events
        identifier: raw_events
        description: One row per delivered event; deliveries may repeat.
        columns:
          - name: event_id
            data_tests: [not_null]

The staging model references the source; the fact model references staging. The incremental branch rereads an overlap to admit late corrections, while unique_key asks the adapter's strategy to update matching events. Support varies by adapter, so verify generated SQL and rerun behavior on the chosen platform.

{{ config(
    materialized='incremental',
    unique_key='event_id',
    on_schema_change='fail'
) }}

SELECT event_id, customer_id, occurred_at, arrived_at, amount_cents
FROM {{ ref('stg_events') }}
{% if is_incremental() %}
WHERE arrived_at >= (
  SELECT COALESCE(MAX(arrived_at) - INTERVAL '2 days', TIMESTAMP '1900-01-01')
  FROM {{ this }}
)
{% endif %}

on_schema_change='fail' makes a top-level shape change visible; it does not detect every nested change or backfill old rows. A logic change, historical correction outside the overlap, or deletion may require a governed --full-refresh and downstream rebuild.

Limitations and common mistakes​

  • dbt transforms data already available to the platform; it is not an extraction or replication service.
  • unique_key does not prove source uniqueness or non-nullness.
  • A maximum timestamp without overlap can miss late records and ties.
  • Incremental models add state and failure modes; use them only when full builds are materially costly.
  • Compiled behavior and merge support differ by adapter.

Practice​

Add a currency column to the source contract. Decide whether the change should fail, append, or trigger a full refresh, and specify how old rows are populated.

Next steps​

Continue with dbt Data Tests to distinguish SQL-logic tests from assertions over built data.

Primary sources​

Checked 2026-10-15: dbt Core 1.12.4 release metadata, models, sources, and incremental models.