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