Analytics Engineering with dbt: A Practical Guide

dbt (data build tool) has become a standard part of the modern data stack because it brings software-engineering discipline to SQL-based data transformation. This tutorial walks through what dbt is, how to set it up, and how to build a maintainable transformation project, with realistic SQL and YAML examples you can adapt to your own warehouse.

What Is dbt and the Analytics-Engineering Workflow

dbt is a transformation tool that lets you express data transformations as SQL SELECT statements. It does not extract or load data; it sits on the T of ELT, running inside your data warehouse (Snowflake, BigQuery, Redshift, Postgres, Databricks, and others). You write models, and dbt compiles them into SQL and executes them in dependency order.

The role that emerged around this workflow is the analytics engineer: someone who applies engineering practices, version control, testing, code review, documentation, and modular design, to analytics code that was traditionally written as ad-hoc queries.

A typical dbt workflow looks like this:

The key benefits are repeatability, lineage tracking, and the ability to test transformations like application code.

Installation

dbt ships as dbt-core plus an adapter package for your specific warehouse. Install only the adapter you need.

# Create an isolated environment first
python -m venv .venv
source .venv/bin/activate

Postgres
pip install dbt-postgres

Or BigQuery
pip install dbt-bigquery

dbt-core is installed automatically as a dependency
dbt --version

dbt reads connection settings from a profiles.yml file. By default it lives in ~/.dbt/profiles.yml, separate from your project so credentials stay out of version control.

# ~/.dbt/profiles.yml
jaffleshop:

  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      port: 5432
      user: analytics
      password: "{{ envvar('DBTPASSWORD') }}"

      dbname: analytics
      schema: dbtdev
      threads: 4

Using envvar() keeps secrets out of the file. Test the connection with:

dbt debug

dbt init jaffleshop

A dbt project centers on dbtproject.yml and a set of conventional directories:

jaffleshop/
  dbtproject.yml      # project configuration

  models/              # SELECT statements (your transformations)
    staging/
    marts/
  seeds/               # static CSV files loaded as tables
  snapshots/           # SCD type 2 tracking
  macros/              # reusable Jinja
  tests/               # singular (custom) tests
  analyses/            # ad-hoc queries compiled but not run

The dbtproject.yml ties everything together and sets defaults:

name: 'jaffleshop'
version: '1.0.0'
profile: 'jaffleshop'

model-paths: ["models"]
seed-paths: ["seeds"]
snapshot-paths: ["snapshots"]
macro-paths: ["macros"]

models:
  jaffleshop:

    staging:
      +materialized: view
    marts:
      +materialized: table

-- models/staging/stgorders.sql
select
    id            as orderid,

    userid       as customerid,

    orderdate,
    status,
    amount / 100.0 as amountusd

from {{ source('jaffle', 'raworders') }}

-- models/marts/orderspercustomer.sql
select
    customerid,

    count()       as ordercount,
    sum(amountusd) as lifetimevalue
from {{ ref('stgorders') }}

group by 1

-- inline configuration
{{ config(materialized='table') }}

select  from {{ ref('stgorders') }}

from {{ ref('stgorders') }}

from {{ source('jaffle', 'raworders') }}

Because dbt resolves these at compile time, switching from a dev schema to a prod schema requires no code changes, only a different target.

Sources and Freshness

Declare sources in a YAML file so they can be referenced, tested, and monitored.

# models/staging/sources.yml
version: 2

sources:
name: jaffle
    database: raw

    schema: public
    tables:
name: raworders
        loadedatfield: loadedat

        freshness:
          warnafter: {count: 12, period: hour}

          errorafter: {count: 24, period: hour}
name: rawcustomers

dbt source freshness

If the most recent loadedat value is older than the thresholds, dbt emits a warning or an error, which is useful as an early signal that an upstream pipeline has stalled.

# models/staging/models.yml
version: 2

models:
name: stgorders

    columns:

name: orderid
        tests:

unique
notnull

name: status
        tests:

acceptedvalues:
              values: ['placed', 'shipped', 'completed', 'returned']

name: customerid

        tests:

relationships:
              to: ref('stgcustomers')

              field: customerid

• unique: no duplicate values.
• notnull: no missing values.
• acceptedvalues: only values from a known set.
• relationships: every value exists in a referenced model (referential integrity).

-- tests/assertamountispositive.sql
select
    orderid,

    amountusd
from {{ ref('stgorders') }}

where amountusd < 0

Run tests with:

dbt test
dbt test --select stgorders

version: 2

models:
name: stgorders
    description: "One row per order, cleaned from raworders."

    columns:
name: orderid
        description: "Primary key for the order."

        tests:
unique
notnull

    description: "{{ doc('stgordersdoc') }}"

dbt docs generate
dbt docs serve

-- a loop to pivot statuses into columns
select
    customerid,
    {% for status in ['placed', 'shipped', 'completed'] %}
    sum(case when status = '{{ status }}' then 1 else 0 end) as {{ status }}count

    {%- if not loop.last %},{% endif %}
    {% endfor %}
from {{ ref('stgorders') }}
group by 1

A macro is a named, reusable block defined in macros/:

-- macros/centstodollars.sql
{% macro centstodollars(columnname, precision=2) %}

    round({{ columnname }} / 100.0, {{ precision }})
{% endmacro %}

Call it from any model:

select
    orderid,

    {{ centstodollars('amount') }} as amountusd
from {{ source('jaffle', 'raworders') }}

Macros are how packages like dbtutils ship reusable logic. Install packages by listing them in packages.yml and running dbt deps.

Seeds

Seeds are small CSV files in the seeds/ directory that dbt loads as tables. They are ideal for static reference data such as country codes, status mappings, or a list of test accounts to exclude.

seeds/
  countrycodes.csv

dbt seed

select
    o.orderid,
    c.countryname

from {{ ref('stgorders') }} o
left join {{ ref('countrycodes') }} c

    on o.countrycode = c.code

Seeds are version-controlled, so the reference data lives alongside your transformation logic. Avoid using seeds for large or frequently changing datasets, they are meant for small, stable lookups.

Snapshots (SCD Type 2)

Source systems often overwrite records in place, so historical states are lost. Snapshots capture changes over time using slowly changing dimension (SCD) type 2 logic, recording when each version of a row was valid.

-- snapshots/orderssnapshot.sql
{% snapshot orderssnapshot %}

{{
    config(
      targetschema='snapshots',

      uniquekey='orderid',

      strategy='timestamp',
      updatedat='updatedat'

    )
}}

select * from {{ source('jaffle', 'raworders') }}

{% endsnapshot %}

With the timestamp strategy, dbt compares the updatedat column on each run. The check strategy compares specified columns instead, useful when the source has no reliable timestamp.

dbt snapshot

dbt adds dbtvalidfrom and dbtvalidto columns. A null dbtvalidto marks the current version of the row, which makes point-in-time analysis straightforward.

Incremental Models with isincremental()

Incremental models process only new or changed records instead of rebuilding the whole table each run. They are the answer when a table materialization becomes too slow or expensive on a large fact table.

-- models/marts/fctevents.sql
{{
    config(
        materialized='incremental',
        uniquekey='eventid'

    )
}}

select
    eventid,
    userid,

    eventtype,
    eventat

from {{ source('app', 'rawevents') }}

{% if isincremental() %}

    -- only scan rows newer than what we already loaded
    where eventat > (select max(eventat) from {{ this }})

{% endif %}

dbt run --select fctevents --full-refresh

Running dbt: run, test, and build

dbt's commands operate on a selection of resources. Use --select to target a subset.

# build all models
dbt run

build a single model and everything downstream
dbt run --select stgorders+


run only tests
dbt test

build: runs models, tests, snapshots, and seeds together in DAG order
dbt build

# .github/workflows/dbt.yml
name: dbt build
on: [pullrequest]

jobs:
  dbt:
    runs-on: ubuntu-latest
    steps:
uses: actions/checkout@v4
uses: actions/setup-python@v5
        with:

          python-version: '3.11'
run: pip install dbt-postgres
run: dbt deps
run: dbt build --target ci
        env:

          DBTPASSWORD: ${{ secrets.DBTPASSWORD }}

For efficient CI, look into Slim CI using state:modified+ with deferral, which builds only the models changed in a pull request rather than the entire project. Either way, the goal is the same: every change is tested before it reaches production data.

Best Practices

• Layer your models. Use staging (one model per source table, light cleaning), intermediate (reusable joins), and marts (business-facing models). This keeps logic modular and reviewable.
• Always use ref() and source(). Never hard-code schema or table names; let dbt manage lineage and environments.
• One staging model per source table, named stg, doing only renaming, type casting, and light cleaning.
  Test your assumptions. At minimum, add unique and notnull on every primary key. Add relationships tests on foreign keys.
  Keep credentials out of the repo. Use env_var() in profiles.yml.
  Prefer view for staging and table for marts. Only reach for incremental when run time or cost demands it.
  Document as you go. A model with a description and tested columns is far easier for the next analyst to trust.
  Run dbt build in CI so untested changes never reach production.

  Conclusion and Key Takeaways

  dbt brings the practices of software engineering, version control, modularity, testing, and documentation, to the transformation layer of your data stack. By writing models as plain SELECT statements and connecting them with ref() and source(), you get an automatically managed dependency graph, environment portability, and clear lineage.

  Key points to remember:

  • dbt handles only transformation (the T in ELT); it runs SQL inside your warehouse.
  • Materializations (view, table, incremental, ephemeral) let you tune the cost/freshness trade-off per model.
  • ref() and source() are mandatory for lineage and environment management.
  • Tests (generic and singular) and dbt build keep bad data from propagating downstream.
  • Snapshots capture history (SCD type 2); incremental models scale large tables.
  • Documentation and the DAG make the project understandable to the whole team.

  Start small with a few staging models and a handful of tests, then grow the project layer by layer. The discipline pays off as your transformations and your team scale.

  Related Articles

  Airbyte: A Complete Guide to Open-Source Data Integration from Zero to Custom Connectors

  Airbyte: Panduan Lengkap Data Integration Open-Source dari Nol sampai Custom Connector Halo temen-temen, di tutorial kal...

  SQLMesh: The Modern Data Transformation Framework and dbt Alternative

  SQLMesh: Framework Transformasi Data Modern yang Bikin Aku Ninggalin dbt Halo temen-temen! Kali ini aku mau ngajak kalia...

  Ibis Tutorial: The Portable Python DataFrame API Across Backends

  Ibis: API Dataframe Python yang Portabel di Banyak Backend Ibis adalah library dataframe Python yang memungkinkan Anda m...

  dlt Tutorial: Python-First Data Ingestion Pipelines

  Membangun Pipeline EL Berbasis Python dengan dlt (data load tool) Sebagian besar tim data menghabiskan waktu yang tidak ...