How to Build ETL Pipelines with dbt for Data Transformation

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 and configuration files. Then define your warehouse connection in profiles.yml:

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:

-- 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:

-- 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:

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:

# 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:

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 to automate testing:

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 (line 76): Lists dbt as a core data-quality and orchestration tool in modern data stacks.
• communities.md (line 7): Provides a link to the dbt Community for support and advanced learning.
• 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.
• 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}