dbt Tutorial: Build Your First Transformation Project the Right Way

dbt turned SQL transformation into software engineering: version control, tests, documentation, and dependency management. Here’s how to start a project you won’t regret in six months.

flowchart LR
  src[Sources] --> stg[Staging models]
  stg --> int[Intermediate]
  int --> marts[Marts / Gold]
  marts --> tests[Tests + docs]

Install and initialize

pip install dbt-duckdb   # or dbt-snowflake, dbt-bigquery, dbt-postgres...
dbt init my_analytics

We’ll use DuckDB locally — the workflow is identical on a cloud warehouse.

Declare the raw relation so freshness and lineage have an owner:

# models/staging/_sources.yml
sources:
  - name: shop
    tables:
      - name: raw_orders
        freshness:
          warn_after: { count: 12, period: hour }
          error_after: { count: 24, period: hour }
        loaded_at_field: _loaded_at

source() is the extract you do not own. ref() is a model in your DAG. Mixing them hides who you page when the data is stale.

The structure that scales

The convention used by most serious dbt teams has two layers:

models/
  staging/          -- 1:1 with source tables; rename, cast, clean ONLY
    stg_orders.sql
    stg_customers.sql
  marts/            -- business logic; what analysts actually query
    fct_orders.sql
    dim_customers.sql

Staging models do nothing clever:

-- models/staging/stg_orders.sql
select
    id            as order_id,
    customer_id,
    status,
    amount_cents / 100.0  as amount,
    created_at::timestamp as ordered_at
from {{ source('shop', 'raw_orders') }}

Marts contain the business logic, built only from staging models:

-- models/marts/fct_orders.sql
select
    o.order_id,
    o.ordered_at,
    o.amount,
    c.customer_name,
    c.first_order_date,
    o.amount > 100 as is_large_order
from {{ ref('stg_orders') }} o
join {{ ref('stg_customers') }} c using (customer_id)
where o.status != 'cancelled'

ref() is the magic: dbt builds the dependency graph from it, runs models in the right order, and parallelizes what it can.

Tests: two lines that save your weekend

# models/marts/_marts.yml
models:
  - name: fct_orders
    columns:
      - name: order_id
        data_tests: [unique, not_null]
      - name: amount
        data_tests:
          - dbt_utils.accepted_range:
              min_value: 0

dbt test now fails your pipeline before bad data reaches a dashboard. This is the single highest-value feature of dbt — use it from day one.

Run it

dbt run    # builds all models in dependency order
dbt test   # runs all tests
dbt docs generate && dbt docs serve  # interactive lineage graph

Three habits of healthy dbt projects

1. Nothing queries raw tables except staging models. Enforce it in code review.
2. Every mart has at least unique + not_null tests on its key.
3. Materialize marts as tables, staging as views — set it once in dbt_project.yml.

# dbt_project.yml
models:
  my_analytics:
    staging:
      +materialized: view
    marts:
      +materialized: table

Add an intermediate model the first time two marts copy the same five-join block. Do not paste it a third time.

flowchart TD
  raw[source shop.raw_orders] --> stg[stg_orders]
  rawc[source shop.raw_customers] --> stgc[stg_customers]
  stg --> fct[fct_orders]
  stgc --> fct
  fct --> test[unique + not_null]

Sources stay at the edge. Marts only ref models.

Incremental when the mart grows

A full refresh of a fact is fine until it is not. Then you need a key or a replaceable partition:

-- models/marts/fct_orders.sql
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'
) }}

select ...
from {{ ref('stg_orders') }}
{% if is_incremental() %}
  where ordered_at >= (select max(ordered_at) from {{ this }})
{% endif %}

Without a unique key, incremental is append. A retry will duplicate yesterday.

Slim CI, briefly

dbt build --select state:modified+ --defer --state path/to/prod-manifest

CI writes to a PR schema. It should not rebuild the warehouse or write prod. You need a prod manifest.json artifact for modified+ to mean anything.

Mistakes that show up in month three

• Business logic in staging. Staging is rename, type, and light clean. Grain and metrics live in marts (or a thin int_ layer).
• ref to a source. Use source() in staging only. Everything else ref()s a model so lineage stays honest.
• CI that runs the whole project. Use a slim CI selector (state:modified+) once you have a prod manifest.
• No freshness on sources. A silent stale extract looks like a dbt bug. Add freshness on the tables you cannot afford to miss.

FAQ

Why did unique fail on fct_orders? The grain is broken. GROUP BY order_id HAVING count(*) > 1 — then fix the join, do not DISTINCT the fact.

Can staging have 12 joins? No. Staging is 1:1 with a source. Shared join logic is an intermediate model.

Should CI run dbt run or dbt build? build — it runs models and tests. run will ship a broken key.

When do I snapshot? When you need Type-2 history of a source you do not control. Owned dims with valid_from / valid_to are better when facts must join as-of.

Is ephemeral a free refactor? Ephemeral inlines as a CTE. Fine for tiny logic. A 20-join ephemeral compiled into every mart is how CI gets sad.

Next: data quality in Python and SQL windows.