dbt Guide: Analytics Engineering for Modern Data Teams

dbt (data build tool) is the standard for analytics engineering. It applies software engineering practices — testing, version control, documentation, CI/CD — to data transformations. According to dbt Labs, over 30,000 organizations use dbt to transform data in their warehouses. Unlike ETL tools that extract and load data, dbt focuses exclusively on the transformation step, working on data already loaded into the warehouse. This focus on the “T” in ELT has made dbt the fastest-growing data tool in the modern data stack.

What dbt Does

dbt is the transformation layer in the modern data stack. Data is extracted and loaded by tools like Fivetran, Airbyte, or custom ingestion scripts. dbt then transforms that data within the warehouse using SQL, applying business logic, cleaning, joining, aggregating, and documenting the results:

Extract & Load (Fivetran/Airbyte) → dbt (Transform) → BI Tool (Looker/Tableau)

This separation of concerns allows each layer to use the best tool for its job. Ingestion tools handle the complexity of connecting to source APIs and databases, managing schema drift, change data capture, and incremental extraction. dbt handles the complexity of cleaning, joining, aggregating, testing, and documenting data using SQL, which analysts already know. The dbt documentation emphasizes that this separation enables data teams to focus on transformation quality rather than infrastructure management.

Core Concepts

Models

Models are the building blocks of dbt. Each model is a SQL file that defines a transformation — typically a SELECT statement that reads from source tables or other models. Models are materialized in the warehouse as views, tables, or incremental structures depending on the configuration:

-- models/orders_summary.sql
WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
),
payments AS (
    SELECT * FROM {{ ref('stg_payments') }}
)
SELECT
    o.customer_id,
    o.order_date,
    SUM(p.amount) AS total_amount
FROM orders o
JOIN payments p USING (order_id)
GROUP BY 1, 2

The ref() function is the core of dbt’s dependency management. It automatically resolves the database location of referenced models and builds the dependency graph. When you run dbt run, it builds models in the correct topological order based on the ref graph. This means you never need to manually specify which models depend on which — dbt infers the DAG from your SQL code.

Testing

dbt includes built-in tests and supports custom test definitions written in SQL. Tests are assertions about your data that return failing records if violated:

models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'returned']

Custom tests are SQL files that return records violating your expectations. If a custom test returns any rows, it fails:

-- tests/assert_total_is_positive.sql
SELECT * FROM {{ ref('orders') }}
WHERE total_amount < 0

Running dbt test executes all tests and reports failures with detailed output. In CI/CD pipelines, test failures can block model promotion to production. The dbt documentation recommends testing every primary key for uniqueness and NOT NULL, and testing downstream models that feed executive dashboards with business rule validations.

Documentation

dbt auto-generates searchable documentation from schema YAML files and model SQL. Adding descriptions to models, columns, tests, and sources creates a living data dictionary that evolves with your warehouse:

models:
  - name: orders
    description: "Customer order records with payment information"
    columns:
      - name: order_id
        description: "Unique identifier for each order"
        tests:
          - unique
          - not_null

Run dbt docs generate to produce static HTML documentation that includes lineage graphs showing model dependencies, compiled SQL for each model, test results, and column-level descriptions. Run dbt docs serve to view the documentation locally on a built-in web server. This documentation becomes the single source of truth for your data catalog, replacing spreadsheets and README files.

Jinja and Macros

dbt uses Jinja templating to make SQL dynamic, reusable, and maintainable. Macros are reusable SQL snippets that eliminate repetitive patterns:

{% macro cents_to_dollars(column_name, decimals=2) %}
    ROUND({{ column_name }} / 100.0, {{ decimals }})
{% endmacro %}

Common macro use cases include date spine generation for time-series data, PII hashing for anonymization, cross-database type casting for portable models, and audit column injection for data lineage tracking. dbt ships with a package manager (dbt deps) and a library of community-contributed packages, including dbt_utils with dozens of useful macros and dbt_expectations for Great Expectations-style testing.

Project Structure

my_dbt_project/
├── models/
│   ├── staging/
│   │   ├── stg_orders.sql
│   │   └── stg_payments.sql
│   ├── marts/
│   │   ├── customer_summary.sql
│   │   └── finance/
│   │       └── revenue_daily.sql
│   └── schema.yml
├── tests/
│   ├── assert_total_positive.sql
│   └── assert_recent_data.sql
├── macros/
│   └── cents_to_dollars.sql
├── seeds/
│   └── country_codes.csv
├── dbt_project.yml
└── profiles.yml

The staging layer models raw source data with minimal transformation — type casting, renaming columns, deduplication, and column selection. The marts layer contains business-level transformations: aggregations, joins, calculations, and business logic. This separation makes the project modular, testable, and navigable for new team members.

Materializations

Materializations define how dbt builds models in the warehouse, balancing freshness with cost:

Incremental models are critical for large datasets that cannot be rebuilt from scratch on every run. They process only new or changed data since the last successful run:

{{ config(materialized='incremental', unique_key='order_id') }}
SELECT * FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
    WHERE order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}

The unique_key configuration ensures that dbt can handle updates to existing records by merging new data with existing data using the warehouse’s MERGE or INSERT OVERWRITE capabilities. Without a unique_key, incremental models only append new records.

dbt Cloud vs Core

dbt Cloud provides a managed web UI with scheduling, CI/CD pipelines, documentation hosting, and team management. dbt Core is the open-source command-line tool that runs on any infrastructure. Cloud reduces operational overhead with built-in scheduling and collaboration features; Core offers more flexibility for teams that want to manage their own infrastructure and integrate dbt into existing CI/CD pipelines.

Many teams start with Core for development and proof-of-concept work, then migrate to Cloud as their dbt usage grows and they need managed scheduling, observability, and team collaboration features.

Best Practices

• Use the staging layer: Create staging models for each source table with minimal transformation — column renaming, type casting, and deduplication only
• Test critical columns: Every primary key should have unique and not_null tests to catch data quality issues early
• Document as you go: Write descriptions when creating models, not as an afterthought — documentation is a living artifact
• Version control everything: dbt projects are code — use Git branches, pull requests, and code reviews for all changes
• Use packages: Leverage community packages like dbt_utils for common macros and dbt_expectations for advanced testing
• CI/CD integration: Run dbt build in CI to validate changes before merging into production, preventing bad data from reaching consumers
• Modular design: Keep models focused on a single transformation — if a model is doing too much, break it into multiple models

Frequently Asked Questions

What is the difference between dbt and Airflow? dbt transforms data in the warehouse using SQL and is the “T” in ELT. Airflow orchestrates when and in what order tasks run and is the scheduling layer. They are complementary tools — Airflow can run dbt as one of its tasks, triggering dbt run after upstream ingestion tasks complete. dbt focuses on the quality and reliability of SQL transformations; Airflow focuses on the scheduling, dependency management, and monitoring of all pipeline tasks.

Can dbt be used with a data lake? Yes. dbt connects to any SQL-based query engine, including Trino, Spark SQL, Databricks SQL, and AWS Athena. If you store data in a data lake with an Iceberg or Delta Lake format and query it via a SQL engine, dbt can transform it. The materializations work the same way — views, tables, and incremental models are all supported on lakehouse architectures.

How do I handle dependencies between dbt models? dbt automatically resolves dependencies using the ref() function. It builds a directed acyclic graph (DAG) of model dependencies by parsing ref() calls in your SQL files and runs models in the correct order. You never need to manually specify execution order. Use the dbt docs generate command to visualize the DAG and verify that your model dependencies are correct.

What is the difference between dbt models and views? dbt models are the SQL files that define transformations. The materialization type (view, table, incremental, ephemeral) determines how each model is persisted in the warehouse. A view materialization creates a SQL VIEW that always reflects the latest source data. A table materialization creates a physical table that is refreshed on each dbt run. An incremental model only processes new or changed records since the last run.

How do I debug failing dbt tests? Examine the compiled SQL in the target/compiled directory to see the exact SQL that dbt executed, including all Jinja macro substitutions. Run the failing test’s SQL directly in your warehouse SQL editor to understand the issue without dbt’s abstraction layer. Check that your test expectations match the actual data — sometimes the test definition itself has incorrect assumptions about valid values or acceptable ranges.

Apache Spark Guide — Data Quality Guide — Analytical SQL Guide