Transformations with dbt

Models, refs and the graph

A model is a SQL file containing one SELECT. dbt wraps it in the DDL for the chosen materialisation (view, table, incremental, ephemeral) and runs it in the warehouse. The important part is ref(): instead of hard-coding analytics.stg_orders, a model writes {{ ref('stg_orders') }}, and dbt resolves the name for the target environment and records the dependency. Run the project and dbt executes models in graph order; select a model with + and dbt runs everything it depends on. source() does the same for raw tables that other systems load, which is where lineage begins.

-- models/staging/shop/stg_shop__orders.sql
with source as (
    select * from {{ source('shop', 'orders') }}
),
renamed as (
    select
        id                                   as order_id,
        customer_id,
        cast(ordered_at as timestamp)        as ordered_at,
        lower(status)                        as status,
        cast(amount_cents as numeric) / 100  as amount,
        _loaded_at                           as loaded_at
    from source
)
select * from renamed

-- models/marts/finance/fct_orders.sql
{{ config(materialized='table') }}

select
    o.order_id,
    o.customer_id,
    c.country,
    o.ordered_at,
    cast(o.ordered_at as date) as order_date,
    o.status,
    o.amount
from {{ ref('stg_shop__orders') }} o
left join {{ ref('stg_shop__customers') }} c using (customer_id)
where o.status != 'cancelled'

Project structure that survives growth

The convention most teams converge on has three layers. Staging (stg_<source>__<entity>): one model per raw table, light cleaning only — rename, cast, standardise; materialised as views. Intermediate (int_<purpose>): joins and reshaping that several marts share, not exposed to end users. Marts (fct_ facts and dim_ dimensions, grouped by business area): the tables dashboards and analysts query, materialised as tables or incremental. The rule is that data flows one way — sources to staging to intermediate to marts — and each layer references only the layer below or itself.

models/
  staging/
    shop/
      _shop__sources.yml         source definitions, freshness, column docs
      _shop__models.yml          tests and docs for the staging models
      stg_shop__orders.sql
      stg_shop__customers.sql
    payments/
      ...
  intermediate/
    finance/
      int_orders_with_refunds.sql
  marts/
    finance/
      _finance__models.yml
      fct_orders.sql
      fct_refunds.sql
      dim_customers.sql
    marketing/
      ...
macros/
tests/                            singular tests (SQL that must return zero rows)
snapshots/                        SCD2 captures
dbt_project.yml                   materialisation defaults per folder

Set materialisation defaults per folder in dbt_project.yml (staging as views, marts as tables) so individual models rarely need config. Keep metrics logic in the marts, not in dashboards, and define each metric once — the modelling module's rule, now enforced by having one fct_orders that everybody refs.

Incremental models

Rebuilding a billion-row fact table every night is slow and expensive. An incremental model processes only new or changed rows on each run and merges them into the existing table. The model's SQL is filtered with an is_incremental() block on the first run (or a full refresh) it builds everything; afterwards it selects rows newer than what the table already holds. A unique_key tells dbt how to merge, so late or updated rows replace their earlier versions rather than duplicate them. Combine with a lookback window to absorb late data, exactly as the batch module described.

-- models/marts/events/fct_events.sql
{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge',
    on_schema_change='append_new_columns'
) }}

select
    event_id,
    user_id,
    event_name,
    event_at,
    cast(event_at as date) as event_date,
    properties
from {{ ref('stg_app__events') }}

{% if is_incremental() %}
  -- reprocess a trailing window so late-arriving events are picked up and merged by event_id
  where event_at >= (select max(event_at) from {{ this }}) - interval '3 days'
{% endif %}

Three cautions. The max() subquery over a huge table can itself be slow; partition-aware warehouses handle it, others benefit from a watermark stored elsewhere. Changing the model's logic does not rewrite old rows; run dbt run --full-refresh --select fct_events after a logic change and treat that as a backfill with the same care. And on_schema_change decides what happens when a column appears; the default (ignore) silently drops it, so choose deliberately.

Tests, sources and documentation

dbt tests are assertions that run as SQL and fail if any rows come back. Generic tests are declared in YAML on columns: unique, not_null, accepted_values, relationships (every customer_id in orders exists in customers). Singular tests are SQL files for business rules ("no order has a negative amount after refunds"). Run them after every build; a failing test on a mart should block downstream consumers, which dbt build does by running tests in graph order and stopping dependents on failure.

version: 2

sources:
  - name: shop
    schema: raw_shop
    loaded_at_field: _loaded_at
    freshness:
      warn_after: {count: 6, period: hour}
      error_after: {count: 24, period: hour}
    tables:
      - name: orders
      - name: customers

models:
  - name: fct_orders
    description: One row per non-cancelled order. Revenue is SUM(amount) where status in ('paid', 'shipped').
    columns:
      - name: order_id
        description: Natural key from the shop database.
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - not_null
          - relationships: {to: ref('dim_customers'), field: customer_id}
      - name: status
        tests:
          - accepted_values: {values: ['placed', 'paid', 'shipped', 'refunded']}
      - name: amount
        tests:
          - dbt_utils.accepted_range: {min_value: 0, inclusive: true}

dbt source freshness checks that raw tables are being loaded, turning "the pipeline is silently stuck" into a failing check. dbt docs generate produces a site with every model's description, columns, tests and a lineage graph from sources to marts; it is the documentation people actually consult because it is generated from the code and cannot drift. Descriptions on marts should state the grain and the metric definitions, so the docs answer the questions the modelling module said to write down.

Running it: environments, CI and schedule

dbt reads a target (development, CI, production) that maps to a schema and credentials, so the same code builds into a developer's own schema locally and into the production schema on the scheduler. In CI, a pull request builds only the models it changed and their descendants, comparing against the production manifest — the slim CI pattern — and runs their tests, so a broken model never merges. Production runs are an orchestrator task (dbt build on a schedule, or after the raw loads land) with the manifest and run results stored for the next slim CI and for observability.

# CI: build changed models and their descendants against the last production manifest, test them
dbt build --select state:modified+ --state ./prod-manifest --target ci --fail-fast

# production: fail early if sources are stale, then build everything with tests in graph order
dbt source freshness --target prod
dbt build --target prod

# a logic change on an incremental model: rebuild it from scratch once
dbt run --full-refresh --select fct_events --target prod

• Pin the dbt version and adapter in the project; upgrade deliberately.
• Version packages (dbt_utils and friends) in packages.yml and commit the lock file.
• Use the + and @ selectors and tags to run subsets; never run a 400-model project to test one change.
• Store run artifacts (manifest, run results) after each production run; they feed slim CI, lineage tooling and the observability module's freshness dashboards.
• Treat a test failure in production like a failing deploy: alert, block dependents, fix, rerun.