dbt basics: your first project with sources, models, ref, tests and docs

dbt turns SQL SELECT statements into tables and views in your warehouse, in the right order, with tests. You write models/*.sql; dbt works out dependencies and runs them.

#Install and create a project

python -m venv .venv && source .venv/bin/activate
pip install dbt-core dbt-postgres          # or dbt-snowflake, dbt-databricks, ...
dbt init shop_analytics
cd shop_analytics
dbt debug                                  # checks profiles.yml and the connection

Connection details live in ~/.dbt/profiles.yml, not in the project, so credentials stay out of Git.

shop_analytics:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      user: dbt_dev
      password: "{{ env_var('DBT_PASSWORD') }}"
      port: 5432
      dbname: analytics
      schema: dbt_alice
      threads: 4

Each developer gets their own schema, so nobody overwrites anyone else's work.

#Declare sources

models/staging/sources.yml:

version: 2
sources:
  - name: shop
    schema: raw_shop
    tables:
      - name: orders
      - name: customers

Sources are the raw tables dbt reads but does not build.

#Staging models

models/staging/stg_orders.sql:

select
    id            as order_id,
    customer_id,
    cast(amount as numeric(10,2)) as amount,
    cast(created_at as date)      as order_date,
    status
from {{ source('shop', 'orders') }}
where status != 'test'

One staging model per source table: rename, cast, filter junk. Nothing else.

#A mart model with ref()

models/marts/fct_daily_revenue.sql:

select
    o.order_date,
    c.country,
    count(*)        as orders,
    sum(o.amount)   as revenue
from {{ ref('stg_orders') }} o
join {{ ref('stg_customers') }} c using (customer_id)
group by 1, 2

ref() is the heart of dbt. It points at another model and tells dbt to build that model first. Never hard-code schema names in models.

#Build and run

dbt run                                   # build all models
dbt run --select fct_daily_revenue        # one model
dbt run --select +fct_daily_revenue       # it and everything upstream
dbt build                                 # run + test + snapshots + seeds, in order

#Tests

models/staging/schema.yml:

version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'returned', 'cancelled']
      - name: customer_id
        tests:
          - relationships:
              to: ref('stg_customers')
              field: customer_id

dbt test
dbt test --select stg_orders

A failing test prints the SQL it ran; paste it into your SQL client to see the bad rows.

Tip

unique and not_null on every primary key catch most join explosions and duplicate loads before they reach a dashboard.

#Materializations

Set it per folder in dbt_project.yml:

models:
  shop_analytics:
    staging:
      +materialized: view
    marts:
      +materialized: table

#Documentation

dbt docs generate
dbt docs serve --port 8081

You get a site with every model, column description and a lineage graph. Add descriptions in the YAML files as you go.

#Daily commands

#Prevent it next time

• Every model goes through ref() or source(); no hard-coded table names.
• Tests on keys for every model, run in CI before merge.
• Separate dev schemas per developer and a prod target only the scheduler uses.