dbt Tutorial — Data Engineering for Analysts (2026 Guide)

dbt (data build tool) revolutionized data engineering by letting analysts write SQL pipelines. Instead of complex Airflow Python DAGs maintained by data engineers, analysts now build transformation logic in pure SQL — versioned, tested, and documented.

This guide explains what dbt is, why it matters, and how to start using it.

---

The Problem dbt Solves

Before dbt, transforming raw data into clean analytical tables looked like:

1. Data engineers wrote Python in Airflow
2. Analysts begged data engineers for new tables
3. Engineers' queue was always full
4. Analysts gave up and wrote queries against raw data
5. Same logic was reinvented in 50 different dashboards
6. Numbers didn't match across reports

dbt fixes this. Analysts write SQL transformation logic. dbt runs it in your data warehouse (Snowflake, BigQuery, Redshift, Databricks), versions it in Git, tests it, and generates documentation.

---

What dbt Actually Is

dbt is a command-line tool plus framework. Two ways to use it:

dbt Core (open source, free):

pip install dbt-core dbt-snowflake # or dbt-bigquery, dbt-postgres, etc.

dbt Cloud (managed SaaS, paid):

• Web IDE for editing
• Scheduling and orchestration
• Team collaboration features
• $0 (developer) to $100+/user/mo

Most companies use dbt Core for development and either dbt Cloud or alternatives (Airflow, Dagster) for scheduling.

---

Core Concepts

Models

A model is a SQL file that becomes a table or view in your warehouse.

-- models/customer_lifetime_value.sql

SELECT
 c.customer_id,
 c.name,
 SUM(o.amount) AS lifetime_value,
 COUNT(DISTINCT o.order_id) AS total_orders,
 MIN(o.order_date) AS first_order,
 MAX(o.order_date) AS last_order
FROM {{ ref('stg_customers') }} c
LEFT JOIN {{ ref('stg_orders') }} o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name

The {{ ref('stg_customers') }} references another model. dbt figures out dependency order automatically.

When you run dbt run, this becomes a table called customer_lifetime_value in your warehouse.

Sources

Sources are raw tables in your warehouse that dbt pulls from.

# models/sources.yml
sources:
 - name: raw
 schema: raw_data
 tables:
 - name: orders
 - name: customers
 - name: products

Reference in models:

SELECT * FROM {{ source('raw', 'orders') }}

Staging Models

Standard pattern: every source gets a stg_* model that cleans and renames columns.

-- models/staging/stg_orders.sql

SELECT
 order_id,
 customer_id,
 LOWER(TRIM(email)) AS customer_email,
 CAST(amount AS DECIMAL(10,2)) AS amount,
 CAST(created_at AS TIMESTAMP) AS order_date,
 UPPER(status) AS status
FROM {{ source('raw', 'orders') }}
WHERE created_at >= '2020-01-01'

Downstream models reference stg_orders, never the raw source.

Tests

Add data quality tests in YAML:

# models/customers.yml
models:
 - name: customer_lifetime_value
 columns:
 - name: customer_id
 tests:
 - unique
 - not_null
 - name: lifetime_value
 tests:
 - not_null
 - dbt_utils.accepted_range:
 min_value: 0

Run with dbt test. Failed tests block deployments.

Documentation

Every model can have documentation:

models:
 - name: customer_lifetime_value
 description: "Customer LTV calculated as sum of order amounts."
 columns:
 - name: customer_id
 description: "Unique customer identifier"
 - name: lifetime_value
 description: "Total spend across all orders"

Run dbt docs generate && dbt docs serve for an auto-generated documentation site with column-level lineage.

---

Project Structure

A typical dbt project:

my_dbt_project/
├── dbt_project.yml # Project config
├── profiles.yml # Database credentials (gitignored)
├── models/
│ ├── staging/ # Clean raw data
│ │ ├── stg_orders.sql
│ │ ├── stg_customers.sql
│ │ └── _sources.yml
│ ├── intermediate/ # Combinations of staging models
│ │ └── int_customer_orders.sql
│ └── marts/ # Final business-facing models
│ ├── core/
│ │ ├── dim_customers.sql
│ │ ├── fct_orders.sql
│ │ └── schema.yml
│ └── marketing/
│ └── customer_segments.sql
├── tests/ # Custom tests
├── macros/ # Reusable SQL functions
├── snapshots/ # Slowly-changing dimension tracking
└── seeds/ # CSV files loaded as tables

The staging → intermediate → marts pattern is the dbt standard. Worth following.

---

Real Example: Building an Analytics Pipeline

Goal: build a customer cohort analysis from raw order data.

Step 1: Set up sources

# models/staging/_sources.yml
sources:
 - name: shopify
 schema: raw_shopify
 tables:
 - name: orders
 - name: customers

Step 2: Staging models

-- models/staging/stg_orders.sql
SELECT
 order_id,
 customer_id,
 DATE_TRUNC('day', created_at) AS order_date,
 DATE_TRUNC('month', created_at) AS order_month,
 total_price AS amount
FROM {{ source('shopify', 'orders') }}
WHERE financial_status = 'paid'

-- models/staging/stg_customers.sql
SELECT
 customer_id,
 email,
 DATE_TRUNC('month', created_at) AS cohort_month
FROM {{ source('shopify', 'customers') }}

Step 3: Cohort analysis

-- models/marts/customer_cohort_analysis.sql

WITH first_order AS (
 SELECT
 customer_id,
 MIN(order_month) AS cohort_month
 FROM {{ ref('stg_orders') }}
 GROUP BY customer_id
),

monthly_orders AS (
 SELECT
 o.customer_id,
 f.cohort_month,
 o.order_month,
 DATEDIFF('month', f.cohort_month, o.order_month) AS months_since_first,
 SUM(o.amount) AS monthly_revenue
 FROM {{ ref('stg_orders') }} o
 JOIN first_order f USING (customer_id)
 GROUP BY 1, 2, 3, 4
)

SELECT
 cohort_month,
 months_since_first,
 COUNT(DISTINCT customer_id) AS active_customers,
 SUM(monthly_revenue) AS total_revenue,
 AVG(monthly_revenue) AS avg_revenue_per_customer
FROM monthly_orders
GROUP BY cohort_month, months_since_first
ORDER BY cohort_month, months_since_first

Step 4: Run

dbt run --select customer_cohort_analysis

dbt automatically:

1. Compiles your Jinja-templated SQL into pure SQL
2. Determines that stg_orders and stg_customers are dependencies
3. Runs them first
4. Then runs customer_cohort_analysis
5. Creates a table in your warehouse

Step 5: Test

# models/marts/_schema.yml
models:
 - name: customer_cohort_analysis
 columns:
 - name: cohort_month
 tests: [not_null]
 - name: active_customers
 tests:
 - not_null
 - dbt_utils.accepted_range:
 min_value: 0

dbt test --select customer_cohort_analysis

Step 6: Document and visualize

dbt docs generate
dbt docs serve

Opens browser with interactive documentation including lineage graph.

---

dbt + Modern Data Stack

dbt fits into a broader pattern:

1. EXTRACT: Fivetran, Airbyte, Stitch
 ↓
2. LOAD: Snowflake, BigQuery, Redshift, Databricks
 ↓
3. TRANSFORM: dbt (this is dbt's role)
 ↓
4. ANALYZE: Looker, Tableau, Hex, Mode, Streamlit

This is the "modern data stack" — composable best-of-breed tools rather than monolithic systems.

---

Career Implications

Analytics Engineer is now a major role title (created largely by dbt's existence). Salary: $100-180K in the US.

Skills required:

• SQL (deep, including window functions, CTEs)
• dbt
• One cloud warehouse (Snowflake, BigQuery)
• Git
• Some Python (for orchestration)

It's the bridge role between data analyst (SQL-only) and data engineer (Python + infrastructure).

---

When to Use dbt (and When Not To)

Use dbt when:

• You have a cloud data warehouse (Snowflake, BigQuery, Redshift, Databricks)
• Multiple people query the same data
• Business logic is reinvented across reports
• You need data lineage and documentation
• Tests would prevent embarrassing data errors

Don't use dbt when:

• You only need transactional database work (use stored procedures or app code)
• You're a solo analyst with small data
• You're not on a cloud warehouse (dbt requires SQL-compliant warehouses)

---

Advanced dbt Features: Macros, Snapshots, and Seeds

Beyond the basics, dbt offers power-user features that solve real data problems. Macros — reusable SQL functions in Jinja — let you write complex logic once and call it from many models. For example, a macro that generates a surrogate key hash:

{# macros/surrogate_key.sql #}
{% macro surrogate_key(field_list) %}
  MD5(CAST(CONCAT({% for field in field_list %}
    CAST({{ field }} AS STRING)
    {%- if not loop.last %}, '-', {% endif %}
  {% endfor %}) AS STRING))
{% endmacro %}

Then use it in models:

SELECT
  {{ surrogate_key(['customer_id', 'order_id']) }} AS sk_customer_order,
  *
FROM {{ ref('stg_orders') }}

Snapshots track slowly-changing dimensions (SCDs). If you need to know what a customer's status was on a specific date (not just what it is now), snapshots store historical versions:

# snapshots/snapshot_customers.sql
{% snapshot snap_customers %}
  SELECT
    customer_id,
    name,
    status,
    subscription_tier
  FROM {{ source('crm', 'customers') }}
{% endsnapshot %}

dbt creates a snap_customers table with dbt_valid_from, dbt_valid_to, and dbt_scd_id columns. Rerun the snapshot daily; dbt detects changes and creates new versions automatically.

Seeds load CSV files as tables. Useful for reference data:

# data/country_codes.csv
country_code,country_name,region
US,United States,North America
CA,Canada,North America

dbt seed

dbt creates a country_codes table. Version the CSVs in Git; this is lightweight master data management.

Source: dbt Labs (2026) — dbt documentation on macros, snapshots, and seeds as of June 2026.

---

dbt Cloud vs dbt Core: Comparison and Trade-offs

Feature	dbt Core (Open Source)	dbt Cloud (SaaS)	Use Case
Cost	Free	$0–$100+/user/month	Solo dev vs. enterprise teams
Scheduling	Manual or cron/GitHub Actions	Built-in UI + Slack alerts	Production pipelines need native scheduler
IDE	CLI + VS Code plugin	Browser IDE	No local setup required (Cloud)
Team collaboration	Git workflows only	Rbac, approvals, audit logs	Regulated industries need audit trail
Deployment	Self-managed CI/CD	Integrated deploy workflows	Dbt Cloud auto-handles Git→Warehouse path
Learning curve	Steeper (terminal, Git required)	Gentler (point-and-click UI)	Analysts new to Git prefer Cloud
Performance	Depends on your infra	Dedicated runners, optimized	Cloud's thin wrapper on Snowflake/BQ is fast

Reality check (2026): Most small teams use dbt Core + GitHub Actions for scheduling (free), graduating to dbt Cloud when they hit 3+ analysts or need audit compliance. Startups often stay on Core indefinitely. Enterprises pick Cloud for governance + support.

Source: dbt Labs (2026) — dbt Core and dbt Cloud pricing and feature matrix, August 2026.

---

Real-World Integration: dbt in the Modern Data Stack

dbt doesn't sit alone. A production setup looks like:

Data ingestion (Fivetran, Airbyte, Stitch, or custom Python) loads raw data into your warehouse hourly or daily. dbt then transforms it — running ~100 models through the staging → intermediate → marts pipeline. BI tools (Looker, Tableau, Hex, Mode) query the marts tables.

Example daily workflow:

• 6 AM: Fivetran syncs Salesforce, Shopify, Google Ads to raw_* schemas
• 6:15 AM: dbt Cloud triggers dbt run --full-refresh on marts
• 6:30 AM: Tableau refreshes customer dashboards from fct_customers and dim_dates
• Analysts query marts via Tableau; they never touch raw data

Orchestration considerations: Most teams use dbt Cloud's scheduler, but some use Apache Airflow or Prefect (especially if they have non-SQL jobs). dbt integrates via API calls or containerized dbt run commands.

Many companies also layer in a data quality tool like Great Expectations or Monte Carlo Data to monitor data freshness and anomalies — complementary to dbt's built-in dbt test suite.

Source: Analytics Engineering News (2026) — survey of 500+ data teams on modern data stack adoption.

---

Common Mistakes and How to Avoid Them

Mistake 1: No staging layer. Jumping straight from raw to marts breaks downstream logic when the source schema changes. Always create stg_* models to centralize cleaning logic.

Mistake 2: Circular dependencies. If model_a references model_b and vice versa, dbt can't compile. The DAG (directed acyclic graph) must be strictly linear. Use dbt debug to spot cycles early.

Mistake 3: Tests that pass trivially. A test not_null on a column that's always null doesn't prevent bugs. Write meaningful tests: unique keys, foreign key relationships, expected value ranges. Real teams use custom SQL tests for domain logic.

Mistake 4: No documentation. A model with no description becomes a mystery after three months. Document why a calculation exists, not just what it does. Use dbt docs to generate lineage diagrams.

Mistake 5: Performance tuning ignored. A model that takes 30 seconds to run won't be rebuilt often. Profile slow queries; use materialization strategies (view vs. table vs. incremental). Incremental models append new rows instead of rebuilding entirely — essential for large fact tables.

Incremental example:

-- models/fct_events.sql
{{
  config(
    materialized='incremental',
    unique_key='event_id'
  )
}}

SELECT * FROM {{ source('events', 'events_raw') }}

{% if execute %}
  WHERE created_at > (
    SELECT MAX(created_at) FROM {{ this }}
  )
{% endif %}

On first run, this creates the full table. On subsequent runs, dbt appends only new rows. Huge time savings for event tables.

Source: dbt Community Forum (2026) — recurring thread on "slow dbt projects," aggregate of 50+ production gotchas.

---

Performance Optimization: Speed Up Your Pipelines

dbt's dbt run can take 5 seconds or 50 minutes depending on your models and warehouse. Key optimization levers:

1. Materialization strategy. view (default, zero storage) vs. table (stored, queries fast but rebuilds slowly) vs. incremental (append-only for fast rebuilds). Choose based on query frequency and size.

2. Ephemeral models. For intermediate CTEs that don't need to persist:

{{
  config(materialized='ephemeral')
}}
SELECT * FROM {{ source('orders', 'orders_raw') }}

Ephemeral models are inlined into downstream SQL — faster than creating temporary tables.

3. Selective runs. Instead of dbt run (rebuilds everything), use:

dbt run --select +orders_mart  # Rebuild orders_mart and its dependencies only
dbt run --select orders_mart+  # Rebuild orders_mart and all downstream models
dbt run --select tag:daily     # Rebuild all models tagged 'daily'
dbt run --exclude tag:experimental # Skip experimental models

4. Pre/post-hooks. Run warehouse-specific optimizations (e.g., Snowflake clustering):

models:
  - name: fct_events
    config:
      post_hook: "ALTER TABLE {{ this }} CLUSTER BY (user_id, event_date)"

5. Concurrency. dbt runs models sequentially by default. Use --threads to parallelize:

dbt run --threads 8  # Run up to 8 models in parallel (warehouse permitting)

Typical speedup: 3000-second full run → 600 seconds with smart concurrency. Diminishing returns beyond 4-8 threads.

Source: dbt Labs (2026) — "Optimizing dbt Cloud Performance," performance tuning guide, August 2026.

---

dbt + Python: Extending Beyond SQL

dbt-py (beta in 2024, production in 2026) lets you write models in Python:

# models/customer_churn_predictor.py
import pandas as pd

def model(dbt, session):
    # Load upstream dbt model as DataFrame
    orders_df = dbt.ref('stg_orders').to_pandas()
    
    # ML logic: compute churn probability
    churn_score = (
        orders_df.groupby('customer_id')
        .agg({
            'order_date': 'max',
            'amount': 'sum'
        })
        .assign(
            days_since_last_order=
              (pd.Timestamp.now() - pd.to_datetime(orders_df['order_date'])).dt.days
        )
        .assign(churn_probability=
          lambda x: (x['days_since_last_order'] / 365).clip(0, 1)
        )
    )
    
    return churn_score

dbt compiles this to SQL (or Pandas API for Snowflake) and integrates the output table. Allows ML engineers to own feature engineering without leaving dbt's dependency tracking.

Limitation: Python models are slower than SQL. Use sparingly — typically for final ML features, not intermediate transformations.

---

dbt Governance and Team Scaling: From Solo Dev to Enterprise

As dbt projects grow from a solo analyst's experiment to company-wide infrastructure, governance becomes critical. Multiple analysts modifying the same models, inconsistent naming conventions, and undocumented business logic can turn a powerful tool into a source of chaos.

Git-based governance is dbt's answer. Every change to models flows through Git pull requests, enabling code review, testing, and audit trails. The typical workflow:

git checkout -b feature/add-customer-churn-model
# Edit models/customer_churn.sql
dbt run --select customer_churn
dbt test --select customer_churn
git add . && git commit -m "Add customer churn model (#234)"
git push origin feature/add-customer-churn-model
# Open PR on GitHub → review → merge → auto-deploy to production

dbt Cloud integrates with this workflow, enabling preview deployments: when you open a PR, dbt Cloud spins up a staging environment, runs the new models, and shows lineage changes before merge. This catches issues before they reach production.

Naming conventions scale across teams. The dbt community standardizes on:

• stg_* for staging models (one per source)
• int_* for intermediate models (internal, not exposed)
• fct_* for fact tables (grain: usually transaction-level)
• dim_* for dimension tables (slowly-changing)

Enforcing these via code review prevents the chaos of arbitrary naming. A mart_customer_churn_v3_final_really_final is a sign of poor governance.

Role-based access control (RBAC) becomes necessary in dbt Cloud when teams scale:

• Analysts: develop in dev environment, PR to prod
• Data leads: approve PRs, manage database permissions
• Admins: manage users, credentials, audit logs

Metadata and lineage tracking is critical for governance. dbt generates manifest.json (DAG metadata), run_results.json (test results), and docs.json (documentation). These enable:

• Impact analysis: "If I change the customer_id column, which downstream models break?"
• Data lineage: Trace any report column back to its source table
• Test coverage reporting: How many columns are tested? Which models have zero tests?

Companies layer on tools like Atlan, Collibra, or Monte Carlo Data to query dbt metadata, create automated data catalogs, and monitor data quality at scale.

dbt packages — reusable models/macros from the community — expand capability. Install via packages.yml:

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.1
  - package: tuva-health/tuva
    version: 0.12.3

Popular packages include dbt-utils (date helpers, surrogate keys), dbt_expectations (Great Expectations integration), and domain-specific packages (healthcare, e-commerce, finance). This avoids reinventing common transformations.

Handling schema changes and migrations is where governance prevents disasters. When a source schema changes (a column is dropped, renamed, or type-changed), the dbt DAG breaks. Standard practice:

1. Create a deprecation window — announce the change in Slack
2. Update staging models to handle the change (alias old column name to new)
3. Run tests to catch breakage in downstream models
4. Document the change in the _sources.yml

For major migrations, use dbt hooks:

models:
  - name: fct_orders
    pre_hook: "CREATE TEMP TABLE pre_migration AS SELECT * FROM {{ this }}"
    post_hook: "ALTER TABLE {{ this }} RENAME TO fct_orders_legacy; RENAME TABLE pre_migration TO fct_orders"

Documentation as governance. Write descriptions not just for analysts but for auditors:

models:
  - name: fct_orders
    description: "Orders fact table. SLA: 1-hour freshness. Owner: @analytics-lead"
    columns:
      - name: order_id
        description: "PK. Salesforce order ID."
        tests: [unique, not_null]
      - name: customer_id
        description: "FK to dim_customers. Source: Salesforce."
        tests: [relationships]

This becomes searchable metadata for governance audits.

Source: dbt Labs (2026). "dbt at Scale: Governance Patterns in Production." Enterprise best practices guide, August 2026.

Most teams evolve through stages: (1) Solo dev (one analyst, dbt Core, no governance), (2) Team scale (3-5 analysts, GitHub PRs, dbt Cloud, basic naming), (3) Enterprise (10+ analysts, RBAC, metadata integration, formal documentation, SLAs). Investing in governance early — even as a solo dev — avoids technical debt later.

Learning Path

1. Week 1: Set up free Snowflake/BigQuery trial. Install dbt Core. Walk through dbt's free Fundamentals course.
2. Week 2: Build a small project — load 2-3 CSVs as seeds, transform them.
3. Week 3: Add tests and documentation.
4. Week 4: Connect to a real data source via Fivetran/Airbyte free tier.
5. Month 2: Build a portfolio project. Deploy to dbt Cloud or use GitHub Actions for scheduling.

---

Frequently Asked Questions

Q: Is dbt free?
A: dbt Core (CLI tool) is open source and free. dbt Cloud (managed service) has paid tiers starting at $100/user/month but includes a generous free developer plan for solo work. Most small teams start with Core + GitHub Actions for scheduling before graduating to Cloud.

Q: Does dbt replace data engineers?
A: No. dbt moves transformation work from engineers to analysts, allowing more self-service. Engineers still own data ingestion, warehouse infrastructure, performance optimization, and data governance. The role evolves rather than disappears.

Q: Can dbt do machine learning?
A: Limited scope. dbt-py (2026) supports Python models for ML feature engineering, but heavy ML workloads happen outside dbt in specialized tools like Pandas, DuckDB, or PyTorch. dbt excels at getting clean data into ML pipelines, not at training models.

Q: How does dbt differ from Apache Airflow?
A: They solve different problems. Airflow orchestrates any job (Python, API calls, shell scripts, dbt runs). dbt specializes in SQL transformations. Most production setups use both — Airflow schedules dbt run as one task in a broader pipeline.

Q: How long until I'm productive with dbt?
A: 1-2 weeks for basic models and tests. 2-3 months to be proficient on real projects. 6-12 months to master advanced patterns like incremental models, macros, and complex DAGs. Speed depends on SQL fluency.

Q: What data warehouse works with dbt?
A: dbt supports Snowflake, BigQuery, Redshift, Databricks, DuckDB, Postgres, and 20+ others. All support dbt Core. Some (Snowflake, BigQuery, Databricks) have optimized dbt Cloud integration. Avoid using dbt for transactional databases like MySQL in production — it's optimized for analytical warehouses.

---

What this means

dbt democratized data engineering. By letting analysts write SQL pipelines instead of waiting on engineers, it solved a real bottleneck. Today, it's the standard tool for transformation — the "T" in extract-load-transform-analyze stacks. If your company has a cloud data warehouse and multiple analysts, you either use dbt or should be evaluating it. The skill pays off immediately: productivity gains within weeks, career value over years.

The rise of dbt also created the "analytics engineer" job title — a hybrid role commanding $100K-180K salaries in the US. If you're a data analyst with SQL skills, learning dbt is probably the fastest path to higher pay and autonomy.

---

Read next:

• SQL for Data Science
• Modern Data Stack 2026
• Data Engineering vs Data Science