# How to Build ETL Pipelines with dbt for Data Transformation

> Learn to build robust ETL pipelines with dbt. Transform raw data into version controlled analytics tables using modular SQL models and automated dependency graphs. Master data transformation today.

- Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook)
- Tags: how-to-guide
- Published: 2026-08-12

---

**dbt (Data Build Tool) is a SQL-centric framework that enables data engineers to build ETL pipelines by transforming raw data loads into version-controlled, tested analytics tables using modular SQL models and automated dependency graphs.**

The DataExpert-io/data-engineer-handbook repository identifies dbt as a core orchestration and data quality tool for modern data stacks. When you build ETL pipelines with dbt, you replace brittle SQL scripts with a systematic approach that includes automated testing, documentation generation, and CI/CD integration. This transformation layer handles the "T" in ELT/ETL by compiling modular SQL files into executable warehouse code.

## Setting Up dbt to Build ETL Pipelines

Begin by initializing a project and configuring warehouse connections. According to the source analysis, the typical workflow starts with `dbt init` to create the project skeleton.

### Project Initialization and Profiles

Run `dbt init` to generate the standard directory structure, including [`dbt_project.yml`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/dbt_project.yml) and configuration files. Then define your warehouse connection in [`profiles.yml`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/profiles.yml):

```yaml
my_postgres:
  target: dev
  outputs:
    dev:
      type: postgres
      host: your-db-host
      user: your_user
      password: your_password   # Use environment variables in production

      dbname: analytics
      schema: dbt_demo
      threads: 4

```

This configuration connects dbt to your analytics warehouse, enabling the tool to execute compiled SQL against your data platform.

## Building ETL Pipelines with dbt Models

dbt pipelines center on **models**—SQL files that define transformations. The dependency graph automatically resolves execution order using `{{ ref() }}` and `{{ source() }}` functions.

### Staging Raw Data

Create staging models to clean and prepare raw data from external sources. These typically use `materialized='view'` for efficiency:

```sql
-- models/staging/raw_events.sql
{{ config(materialized='view') }}

SELECT
  event_id,
  user_id,
  event_timestamp,
  event_type,
  payload
FROM {{ source('raw', 'events') }}

```

### Creating Mart Models

Transform staged data into business-ready tables using `materialized='table'` for analytics consumption:

```sql
-- models/mart/user_events.sql
{{ config(materialized='table') }}

SELECT
  user_id,
  DATE_TRUNC('day', event_timestamp) AS event_date,
  COUNT(*) AS events_per_day
FROM {{ ref('staging__raw_events') }}
GROUP BY user_id, event_date

```

### Additional Transformation Tools

Beyond standard SQL models, dbt supports **seeds** (version-controlled CSV files loaded via `dbt seed`), **snapshots** for slowly changing dimensions, and **macros** (reusable Jinja functions that enforce DRY principles across your SQL logic).

## Implementing Data Quality Tests

dbt enables automated testing through YAML configuration files. Define schema tests directly alongside your models to ensure data integrity.

### Configuration and Tests

Create a YAML file specifying tests for uniqueness, nullability, and custom business logic:

```yaml
version: 2

models:
  - name: user_events
    description: "Aggregated daily event counts per user."
    columns:
      - name: user_id
        description: "Unique identifier for each user."
        tests:
          - not_null
          - unique
      - name: event_date
        description: "Date of the event (UTC)."
        tests:
          - not_null

```

Execute tests using the CLI:

```bash

# Compile and execute models

dbt run

# Execute tests

dbt test

```

## Generating Documentation

dbt automatically produces interactive documentation sites showing lineage and model dependencies. Add descriptions to your YAML files and run:

```bash
dbt docs generate && dbt docs serve

```

This creates a browsable web interface showing the complete transformation graph from raw sources to final marts.

## Deploying with CI/CD

Production pipelines require automated testing and deployment. Implement GitHub Actions to run dbt on every pull request.

### GitHub Actions Workflow

Create [`.github/workflows/dbt.yml`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/.github/workflows/dbt.yml) to automate testing:

```yaml
name: dbt CI

on:
  push:
    branches: [ main ]
  pull_request:
    branches: [ main ]

jobs:
  dbt:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: '3.11'
      - name: Install dbt
        run: pip install dbt-core dbt-postgres
      - name: Run dbt
        env:
          DBT_PROFILES_DIR: ${{ github.workspace }}
        run: |
          dbt deps
          dbt run
          dbt test

```

## Key Resources in the Data Engineer Handbook

The DataExpert-io/data-engineer-handbook repository provides specific resources for dbt practitioners:

- **[`README.md`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/README.md)** (line 76): Lists dbt as a core data-quality and orchestration tool in modern data stacks.
- **[`communities.md`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/communities.md)** (line 7): Provides a link to the dbt Community for support and advanced learning.
- **[`books.md`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/books.md)** (lines 22-27): References *Data Engineering with dbt* for deeper study of transformation patterns and best practices.

These files demonstrate the repository's endorsement of dbt as essential infrastructure for data engineering workflows.

## Summary

To build ETL pipelines with dbt effectively:

- **Initialize projects** with `dbt init` and configure warehouse connections in [`profiles.yml`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/profiles.yml).
- **Create modular models** using SQL files in the `models/` directory, separating staging views from table-based marts.
- **Declare dependencies** explicitly using `{{ source() }}` for raw data and `{{ ref() }}` for inter-model references.
- **Implement testing** through YAML configuration files specifying `not_null`, `unique`, and custom tests.
- **Generate documentation** automatically using `dbt docs generate` to maintain lineage visibility.
- **Deploy via CI/CD** using GitHub Actions or similar platforms to ensure code quality on every merge.

## Frequently Asked Questions

### What is the difference between ETL and ELT when using dbt?

dbt specializes in the "T" (Transform) portion of both patterns. In traditional ETL, transformations occur before loading, but dbt typically enables ELT—where raw data loads first into the warehouse, then dbt transforms it using SQL models. This approach leverages modern cloud warehouses' compute power while maintaining transformation logic as version-controlled code.

### How do you handle slowly changing dimensions (SCD) in dbt?

Use **snapshots** to capture historical changes in dimension tables over time. Configure a snapshot block in SQL to track specific columns, and dbt automatically manages the logic to insert new records while preserving history, implementing SCD Type 2 patterns without manual intervention.

### What are the best practices for organizing dbt models?

Structure your `models/` directory into three layers: **staging** (light cleaning of raw sources), **intermediate** (complex transformations and joins), and **marts** (business-ready aggregations). This separation ensures that raw data dependencies remain isolated from analytical endpoints, improving maintainability and debugging clarity.

### How do you connect dbt to different data warehouses?

Configure separate outputs in [`profiles.yml`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/profiles.yml) for each environment (development, staging, production) and warehouse type (Snowflake, BigQuery, Postgres, etc.). Use the `type` parameter to specify the adapter, and install the corresponding dbt adapter package (e.g., `dbt-snowflake`, `dbt-bigquery`) via pip to enable warehouse-specific compilation.