How to Use dbt for Data Transformations: A Complete Guide for Data Engineers

dbt (data build tool) is a SQL-first framework that enables data engineers to model, test, and document data transformations using version-controlled SELECT statements that materialize as tables or views in your warehouse.

According to the DataExpert-io/data-engineer-handbook source code, dbt appears as a core data quality tool in modern data stacks (see README.md lines 76-78). By treating SQL as code, dbt eliminates the need for heavy orchestration scripts while providing automated testing, dependency management, and documentation generation directly within your analytics workflow.

Core Concepts of dbt for Data Transformations

Understanding dbt's architecture requires familiarity with several key components that work together to create a governed transformation layer.

Projects and Configuration

Every dbt transformation pipeline begins with a project—a directory containing a dbt_project.yml file that defines the project name, version, and default materialization settings. This configuration file sits at the root of your repository and orchestrates how dbt compiles your SQL into warehouse-specific syntax.

Models as Transformation Logic

Models are the fundamental building blocks of dbt transformations. These individual .sql files contain SELECT statements that dbt materializes as tables, views, or incremental datasets in your target warehouse. When you execute dbt run, the framework resolves dependencies between models and executes them in the correct order.

Sources, Tests, and Documentation

Sources declare raw input tables in YAML files (typically src.yml or sources.yml), enabling explicit lineage tracking from raw data to transformed outputs. Tests such as unique and not_null validate data quality automatically after each model runs, while auto-generated documentation creates a searchable data catalog via dbt docs serve.

Setting Up Your First dbt Project

Initialize the Project Structure

Create a new dbt project using the CLI to generate the standard directory layout including models/, macros/, and configuration files.

dbt init my_dbt_project
cd my_dbt_project

This creates the directory structure:

my_dbt_project/
├─ dbt_project.yml
├─ models/
│  └─ example.sql
└─ <profiles.yml will be created in ~/.dbt/>

Configure Warehouse Connections

The profiles.yml file stores your warehouse credentials and connection parameters. Located at ~/.dbt/profiles.yml by default, this file keeps authentication details out of version control while supporting multiple environments.

# ~/.dbt/profiles.yml

my_dbt_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: <ACCOUNT>
      user: <USER>
      password: <PASSWORD>
      role: <ROLE>
      database: ANALYTICS_DB
      warehouse: ANALYTICS_WH
      schema: PUBLIC

Replace the placeholder values with your actual credentials. Never commit this file to source control.

Building Transformation Models

Define Sources for Lineage Tracking

Establish explicit dependencies by declaring sources in a YAML file within your models/ directory. This enables dbt to track lineage from raw data through your transformations.

# models/src.yml

version: 2
sources:
  - name: raw
    tables:
      - name: events

Create SQL Transformation Models

Reference declared sources using the {{ source() }} Jinja function to build modular transformations. The following example aggregates raw events into daily counts:

-- models/events_daily.sql
with raw_events as (
    select *
    from {{ source('raw', 'events') }}
),

daily_counts as (
    select
        date_trunc('day', event_timestamp) as event_date,
        count(*) as event_cnt
    from raw_events
    group by 1
)

select * from daily_counts

Testing and Documentation

Add Data Quality Tests

Define tests in schema.yml files to enforce data contracts. dbt automatically runs these assertions after building your models, failing the pipeline if quality checks do not pass.

# models/events_daily.yml

version: 2
models:
  - name: events_daily
    description: "Aggregated daily event counts."
    columns:
      - name: event_date
        tests:
          - not_null
          - unique
      - name: event_cnt
        tests:
          - not_null

Execute the Transformation Pipeline

Run your complete dbt workflow using three core commands:

dbt run           # Compiles and executes all models

dbt test          # Runs tests defined in schema files

dbt docs generate # Creates documentation artifacts

dbt docs serve    # Launches local documentation server

Implementing Incremental Materializations

For large datasets, configure models to process only new or changed data rather than rebuilding completely. Add a configuration block to the top of your model file:

{{ config(materialized='incremental', unique_key='event_date') }}

with raw_events as (
    select *
    from {{ source('raw', 'events') }}
    {% if is_incremental() %}
    where event_timestamp > (select max(event_timestamp) from {{ this }})
    {% endif %}
)
-- transformation logic continues

With materialized='incremental' and a specified unique_key, subsequent dbt run commands process only rows meeting the incremental criteria, significantly reducing compute costs and execution time.

Summary

• dbt transforms SQL SELECT statements into managed data pipelines through version-controlled projects centered on dbt_project.yml
• Sources and models create explicit lineage from raw data to transformed outputs, with dependencies automatically resolved during execution
• Automated testing via schema.yml files enforces data quality constraints like unique and not_null immediately after model builds
• Documentation generation produces a searchable data catalog from your YAML configurations and SQL descriptions
• Incremental materializations optimize warehouse compute by processing only new data after initial full loads

Frequently Asked Questions

What is the difference between dbt models and sources?

Sources are declarations of raw, external tables defined in YAML files that dbt reads from but does not modify. Models are SQL transformation files that reference sources (or other models) and materialize as new database objects. This distinction creates clear lineage boundaries between your raw data layer and transformed analytics layer.

How does dbt handle data quality testing?

dbt runs schema tests defined in YAML files immediately after model execution completes. These tests validate constraints such as unique primary keys and not_null required fields. If any test fails, dbt returns a non-zero exit code, enabling you to halt downstream pipeline execution in orchestration tools.

Can dbt work with any data warehouse?

Yes, dbt supports major cloud data warehouses including Snowflake, BigQuery, Amazon Redshift, and PostgreSQL through adapter-specific plugins. The profiles.yml configuration specifies the connection type (type: snowflake, type: bigquery, etc.) and corresponding credentials for your specific warehouse technology.

Where should database credentials be stored in a dbt project?

Always store sensitive credentials in the profiles.yml file located in your home directory at ~/.dbt/profiles.yml, never within the project repository itself. This separation keeps authentication details out of version control while allowing you to maintain different configurations for development, staging, and production environments.

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