dbt in Practice: The Clean Way to Build Transformations

Quickstart

If you want immediate wins, do these in order. You can apply them to a new dbt project or an existing one that’s getting messy.

Overview

“Transformations” are where data projects succeed or fail. Raw ingestion can be automated, and BI tools are fast to click through—but transformations are where you define business logic, manage schema changes, and enforce quality. dbt makes those transformations reproducible by treating them like software: modular code, reviews, tests, and documentation.

Core concepts

dbt is simple on the surface—“SQL models + Jinja”—but the power comes from a few core ideas. If you internalize these, your projects stay clean as they grow.

Layering: staging → intermediate → marts

The most practical “clean dbt” pattern is layering. It enforces a boundary between standardizing data and defining metrics.

Tests + docs are part of the transformation

dbt tests aren’t an afterthought. They are the guardrails that keep transformations stable as upstream data changes. And docs aren’t “wiki work”—they are how other humans learn to use (and trust) your models.

Step-by-step

This walkthrough assumes you already have data landing in your warehouse (via Fivetran/Airbyte/custom pipelines). The goal is to turn that raw data into transformations you can safely iterate on: consistent naming, predictable layers, tests that catch breakage, and a deployment path that doesn’t surprise your stakeholders.

Step 1 — Define sources (raw inputs) before writing models

Start by declaring your sources. This gives you a single place to document raw tables, apply basic checks, and (optionally) run freshness monitoring. It’s also how you stop every model from hardcoding raw schema names.

Step 2 — Build staging models (standardize, don’t theorize)

Staging models are the “translation layer” between raw data and the rest of your project. They should be boring: rename columns, cast types, deduplicate, and make timestamps consistent. Avoid business logic here. This is where you win long-term maintainability.

Below is a practical staging model example with an incremental pattern you can reuse for large tables. (In real projects, some staging models are views; make them incremental only when data volume demands it.)

{{ config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='sync_all_columns'
) }}

with src as (

    select
        id as order_id,
        user_id,
        status,
        currency,
        amount_cents,
        created_at as created_at_utc,
        updated_at as updated_at_utc
    from {{ source('app', 'orders') }}

    {% if is_incremental() %}
      -- Reprocess a small window to catch late updates
      where updated_at >= dateadd(day, -3, (select max(updated_at_utc) from {{ this }}))
    {% endif %}

),

typed as (

    select
        cast(order_id as bigint) as order_id,
        cast(user_id as bigint) as user_id,
        cast(status as varchar) as status,
        cast(currency as varchar) as currency,
        cast(amount_cents as bigint) as amount_cents,
        cast(created_at_utc as timestamp) as created_at_utc,
        cast(updated_at_utc as timestamp) as updated_at_utc
    from src

),

deduped as (

    select *
    from typed
    qualify row_number() over (partition by order_id order by updated_at_utc desc) = 1

)

select * from deduped

Step 3 — Add schema tests + model docs (where it matters)

Add tests and descriptions next to the models they protect. The goal is to encode assumptions: primary keys should not be null, they should be unique, and facts should join to dimensions. Keep the first set of tests small and focused—then expand only when failures prove they’re useful.

version: 2

models:
  - name: stg_orders
    description: "Staging table for orders. One row per order_id (latest version)."
    columns:
      - name: order_id
        description: "Primary key from the app database."
        tests:
          - not_null
          - unique

      - name: user_id
        description: "Foreign key to users."
        tests:
          - not_null

      - name: status
        description: "Order status emitted by the app."
        tests:
          - accepted_values:
              values: ["pending", "paid", "canceled", "refunded"]

      - name: amount_cents
        description: "Order amount in cents, non-negative."
        tests:
          - not_null

  - name: fct_orders
    description: |
      Orders fact table for analytics.
      Grain: one row per order_id.
      Use cases: revenue reporting, cohort analysis, funnel metrics.
    columns:
      - name: order_id
        description: "Primary key."
        tests:
          - not_null
          - unique

      - name: user_id
        description: "Join to dim_users on user_id."
        tests:
          - relationships:
              to: ref('dim_users')
              field: user_id

      - name: net_amount_cents
        description: "Net amount after refunds/adjustments, cents."
        tests:
          - not_null

tests:
  - name: orders_amount_non_negative
    description: "Net amounts should not be negative."
    config:
      severity: error
    test: |
      select order_id
      from {{ ref('fct_orders') }}
      where net_amount_cents < 0

Step 4 — Build marts (the “meaning layer”)

Marts are where you define business concepts: “active customer”, “net revenue”, “retention cohort”, “conversion”. Keep marts stable and easy to consume. Two practical patterns that work across teams:

Step 5 — Make performance predictable (incremental + partition-aware design)

As data grows, performance becomes a design problem. dbt gives you the tools; you still need the habits: filter early, avoid huge fan-out joins, and align your fact tables with how they’re queried.

Step 6 — Deploy safely: CI for PRs + scheduled jobs for production

“Clean dbt” isn’t only code structure—it’s also a release process that keeps breakage out of production. The simplest safe pattern: run a subset in pull requests (what changed + dependencies), then run full jobs on a schedule.

name: dbt-ci

on:
  pull_request:
    branches: [ "main" ]

jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout
        uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: "3.11"

      - name: Install dbt
        run: |
          python -m pip install --upgrade pip
          pip install dbt-core dbt-postgres

      - name: dbt deps
        run: dbt deps

      - name: dbt debug
        env:
          DBT_PROFILES_DIR: ./.dbt
          DBT_USER: ${{ secrets.DBT_USER }}
          DBT_PASSWORD: ${{ secrets.DBT_PASSWORD }}
          DBT_HOST: ${{ secrets.DBT_HOST }}
          DBT_DBNAME: ${{ secrets.DBT_DBNAME }}
        run: dbt debug

      - name: dbt build (state: modified)
        env:
          DBT_PROFILES_DIR: ./.dbt
          DBT_USER: ${{ secrets.DBT_USER }}
          DBT_PASSWORD: ${{ secrets.DBT_PASSWORD }}
          DBT_HOST: ${{ secrets.DBT_HOST }}
          DBT_DBNAME: ${{ secrets.DBT_DBNAME }}
        run: |
          dbt build --select state:modified+ --defer --state ./target

Step 7 — Keep the project clean over time (review standards)

Your dbt project stays clean if you enforce a few PR conventions. They’re lightweight, but they prevent “SQL sprawl.”

Common mistakes

Most dbt “pain” comes from a small set of patterns. The good news: each has a simple fix that makes the project cleaner and more reliable.

FAQ

What’s the difference between dbt and ETL tools?

dbt focuses on transformations inside your warehouse (the “T” in ELT). It doesn’t replace ingestion tools; it replaces the untracked SQL that usually grows after ingestion is solved.

Should staging models be views or tables?

Default to views for staging, then promote to tables when performance demands it. Views are great for iteration and clarity. If downstream chains of views get slow, convert the expensive layers (often intermediate/marts) to tables or incrementals.

When should I use incremental models?

Use incremental models for large tables where rebuilding is costly and the data has a reliable update pattern. You need a stable unique key and a correct incremental filter (often with a rolling window) to handle late-arriving updates.

Is dbt run enough, or should I use dbt build?

Use dbt build for the cleanest workflow. It runs models and associated tests (and can include seeds/snapshots) as a single pipeline, which encourages “only merge when green.”

How many tests should a dbt project have?

Enough to prevent incidents, not enough to create noise. Start with key integrity (not null/unique) and relationships. Add custom tests only for high-cost business rules. If CI becomes flaky, tests will be ignored—so keep signal high.

How do I keep dbt docs from becoming stale?

Document the “how to use this model” details right next to the model and enforce it in PR reviews. Focus docs on grain, keys, join paths, and tricky columns—plus link models to dashboards via exposures when you can.

What’s a good dbt deployment pattern for teams?

Use PR CI for modified models (plus dependencies), and scheduled production jobs for full refreshes. This keeps feedback fast during development while ensuring production is stable and reproducible.

Cheatsheet

A compact checklist for building clean dbt transformations, reviewing PRs, and deploying safely.

Wrap-up

The “clean way” to build transformations in dbt is not about fancy macros or perfect folder trees—it’s about clear boundaries: staging standardizes, marts define meaning, and tests encode assumptions so changes don’t silently break downstream work.

Want to go deeper? The related posts below pair well with this guide: choosing ETL vs ELT, writing scalable SQL, reading explain plans, and picking the right modeling approach for analytics.

Related posts

• ETL vs ELT: The Decision That Shapes Your Stack
• SQL That Scales: Joins, Windows, and Real-World Patterns
• Explain Plans Demystified: Find the Slow Part Fast
• Star Schema vs Snowflake: Modeling for Analytics
• Postgres Power Moves: Indexes, JSONB, and Partitioning
• Warehouse vs Lake vs Lakehouse: What to Pick in 2026