How to build transformation models and tests with dbt

What and why

dbt (data build tool) lets analysts transform data in the warehouse using SQL SELECT statements that dbt turns into tables and views. It adds version control, testing, and documentation to the transformation layer of an ELT pipeline. This tutorial builds a staging-to-mart flow with tests.

Prerequisites

• A warehouse (Snowflake, BigQuery, PostgreSQL, etc.) with raw source tables.
• Python with pip to install the dbt adapter.
• Warehouse credentials with create privileges in a target schema.

Steps

1. Install and init dbt

pip install dbt-postgres   # or dbt-snowflake, dbt-bigquery
dbt init analytics

2. Configure the warehouse profile

Edit ~/.dbt/profiles.yml with connection details. Run dbt debug to confirm dbt can connect.

3. Write a staging model

Staging models clean and rename raw columns, one model per source table. Create models/staging/stg_orders.sql:

select
  id as order_id,
  customer_id,
  amount_cents / 100.0 as amount,
  created_at
from {{ source('raw', 'orders') }}

Declare the source in a _sources.yml file.

4. Build a mart model

Marts aggregate staging models into business-facing tables. Create models/marts/customer_revenue.sql:

select customer_id, sum(amount) as lifetime_value
from {{ ref('stg_orders') }}
group by 1

The ref() function builds the dependency graph.

5. Add tests and docs

In a schema YAML, add tests and descriptions:

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [unique, not_null]

Run dbt test to execute them.

6. Run dbt build

dbt build

build runs models and tests together in dependency order, stopping if a test fails.

Verification

dbt build should report all models built and all tests passing. Query customer_revenue in the warehouse to confirm results. dbt docs generate && dbt docs serve produces a browsable lineage graph.

Next Steps

Add incremental models for large tables, use snapshots to track slowly changing dimensions, and run dbt build in CI on every pull request to catch broken transformations.