How to Set Up and Configure dbt for Data Transformation: A Complete 7-Step Guide

Install dbt with your warehouse adapter, initialize a project with dbt init, configure profiles.yml with credentials, write SQL models in the models/ directory, and execute transformations with dbt run.

The DataExpert-io/data-engineer-handbook repository positions dbt (data build tool) as the de facto standard for transforming raw warehouse data into reliable, version-controlled analytics tables using pure SQL. While the handbook does not ship a ready-made dbt project — only referencing dbt through external links in README → [dbt] and README → [dbt Semantic Layer] — this guide demonstrates how to build and configure a production-grade dbt environment from scratch, aligned with the data engineering principles the handbook champions.

---

1. Install dbt and Your Warehouse Adapter

dbt is distributed as a Python package. You must install the specific adapter for your data warehouse to handle SQL dialect and connection logic.

# Choose the adapter matching your warehouse

pip install "dbt-<adapter>"

# Examples

pip install dbt-snowflake    # Snowflake

pip install dbt-bigquery     # Google BigQuery

pip install dbt-redshift     # Amazon Redshift

pip install dbt-postgres     # PostgreSQL

The adapter encapsulates warehouse-specific connection handling, query compilation, and metadata operations. The handbook's Beginner Bootcamp material emphasizes tool selection aligned to your stack — this pattern holds for dbt adapter choice.

---

2. Initialize a New dbt Project

Run dbt init to scaffold a standard project structure:

dbt init my_dbt_project

This creates the following directory layout:

my_dbt_project/
├── dbt_project.yml          # Project-level configuration

├── models/                  # SQL transformation files

│   └── example/
│       └── my_first_dbt_model.sql
├── tests/                   # Data quality tests

├── seeds/                   # CSV files to load as tables

├── snapshots/               # Slowly changing dimension captures

├── analyses/                # Ad-hoc analytical queries

├── macros/                  # Reusable Jinja/SQL functions

└── README.md

The handbook's Intermediate Bootcamp stresses modular folder architecture for data pipelines. Apply this principle by restructuring models/ into domain layers:

models/
├── staging/                 # Source-aligned, light transformations

├── intermediate/            # Business logic, joins, aggregations

└── marts/                   # Consumable analytics tables

---

3. Configure profiles.yml for Secure Connection

dbt stores warehouse credentials in ~/.dbt/profiles.yml — intentionally outside version control for security. The filename in this file must match your dbt_project.yml project name.

Example PostgreSQL profile:

my_dbt_project:              # Must match dbt_project.yml name

  target: dev                # Default environment

  outputs:
    dev:
      type: postgres
      host: "{{ env_var('DBT_HOST') }}"
      user: "{{ env_var('DBT_USER') }}"
      password: "{{ env_var('DBT_PASSWORD') }}"
      port: 5432
      dbname: analytics_db
      schema: dbt_staging
      threads: 4             # Parallel query execution

      keepalives_idle: 0

Security best practice: The handbook's Beginner Bootcamp explicitly covers "secure handling of secrets." Never commit credentials. Use environment variables or a secret manager (AWS Secrets Manager, Azure Key Vault, etc.) as shown above with {{ env_var() }} templating.

---

4. Define Sources and Write Your First Model

Create source definitions in models/staging/sources.yml to establish explicit contracts with raw data:

version: 2

sources:
  - name: raw
    database: analytics_db
    schema: raw_data
    tables:
      - name: orders
      - name: customers
      - name: products

Then build a staging model at models/staging/stg_orders.sql:

{{ config(
    materialized='view',
    unique_key='order_id',
    on_schema_change='sync_all_columns'
) }}

with source as (
    select * from {{ source('raw', 'orders') }}
),

renamed as (
    select
        order_id,
        customer_id,
        order_timestamp::timestamp as order_timestamp,
        total_amount::decimal(18,2) as total_amount,
        status,
        
        -- dbt metadata fields
        current_timestamp as dbt_loaded_at
    
    from source
    
    where order_id is not null
)

select * from renamed

Key configurations explained:

• materialized='view' — Creates a database view; use 'table' for physical tables or 'incremental' for large datasets
• {{ source() }} — References the source definition, enabling lineage tracking and source freshness monitoring
• unique_key — Required for incremental models to identify row changes

The handbook's Intermediate Bootcamp demonstrates this "source-to-model" pipeline pattern extensively in its transformation exercises.

---

5. Execute dbt Run and Test

Run your models to materialize them in the warehouse:

# Compile and execute all models

dbt run

# Run specific models or directories

dbt run --select staging

# Run with full refresh (rebuild incremental models)

dbt run --full-refresh

Define tests in models/staging/schema.yml to validate data quality:

version: 2

models:
  - name: stg_orders
    description: "Cleaned order data from raw source"
    columns:
      - name: order_id
        description: "Primary key"
        tests:
          - unique
          - not_null
      
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: source('raw', 'customers')
              field: customer_id
      
      - name: total_amount
        tests:
          - not_negative

      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'shipped', 'delivered', 'cancelled']

Execute tests:

dbt test                    # Run all tests

dbt test --select stg_orders  # Test specific model

The handbook's Intermediate Bootcamp source files in src/tests/ illustrate comprehensive testing strategies — mirror this rigor in your dbt test suites.

---

6. Deploy with CI/CD Automation

For production deployments, integrate dbt with GitHub Actions or similar CI/CD platforms. The handbook's Databricks AI Bootcamp demonstrates end-to-end pipeline automation. Here's a simplified dbt CI workflow at .github/workflows/dbt-ci.yml:

name: dbt CI/CD

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

jobs:
  dbt-test:
    runs-on: ubuntu-latest
    
    steps:
      - uses: actions/checkout@v4
      
      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'
      
      - name: Install dbt
        run: pip install dbt-postgres
      
      - name: Install dependencies
        env:
          DBT_HOST: ${{ secrets.DB_HOST }}
          DBT_USER: ${{ secrets.DB_USER }}
          DBT_PASSWORD: ${{ secrets.DB_PASSWORD }}
        run: |
          cd my_dbt_project
          dbt deps  # Install package dependencies

      
      - name: Compile models
        env:
          DBT_HOST: ${{ secrets.DB_HOST }}
          DBT_USER: ${{ secrets.DB_USER }}
          DBT_PASSWORD: ${{ secrets.DB_PASSWORD }}
        run: |
          cd my_dbt_project
          dbt compile
      
      - name: Run tests
        env:
          DBT_HOST: ${{ secrets.DB_HOST }}
          DBT_USER: ${{ secrets.DB_USER }}
          DBT_PASSWORD: ${{ secrets.DB_PASSWORD }}
        run: |
          cd my_dbt_project
          dbt test
      
      - name: Deploy to production
        if: github.ref == 'refs/heads/main'
        env:
          DBT_HOST: ${{ secrets.DB_HOST }}
          DBT_USER: ${{ secrets.DB_USER }}
          DBT_PASSWORD: ${{ secrets.DB_PASSWORD }}
        run: |
          cd my_dbt_project
          dbt run --target prod

Store all sensitive values as GitHub Secrets, adhering to the handbook's security principles.

---

7. Leverage the dbt Semantic Layer

The handbook's README explicitly links to the [dbt Semantic Layer] — dbt's framework for defining reusable metrics and dimensions. After building base models, expose them as semantic objects that BI tools consume without raw SQL.

Create models/marts/semantic_models.yml:

version: 2

semantic_models:
  - name: orders_semantic
    description: "Order-level metrics for business analysis"
    defaults:
      agg_time_dimension: order_timestamp
    
    entities:
      - name: order
        type: primary
        expr: order_id
      - name: customer
        type: foreign
        expr: customer_id
    
    dimensions:
      - name: order_timestamp
        type: time
        type_params:
          time_granularity: day
      - name: status
        type: categorical
    
    measures:
      - name: total_revenue
        description: "Sum of all order amounts"
        agg: sum
        expr: total_amount
      - name: order_count
        description: "Count of orders"
        agg: count
        expr: order_id

metrics:
  - name: weekly_revenue
    description: "Total revenue per week"
    type: simple
    label: Weekly Revenue
    type_params:
      measure: total_revenue
    filter: |
      {{ Dimension('order__status') }} != 'cancelled'

Downstream tools (Tableau, Looker, Hex, etc.) query these metrics directly through dbt's semantic layer API, ensuring consistent business logic.

---

Summary

• Install dbt with warehouse adapter — pip install dbt-<adapter> provides core + connection logic
• Initialize with dbt init — Scaffolds standard project structure per dbt_project.yml
• Configure ~/.dbt/profiles.yml — Secure credential storage outside version control
• Define sources explicitly — sources.yml creates data contracts and lineage
• Write modular SQL models — Use {{ config() }} for materialization strategies
• Validate with dbt test — Schema tests, custom tests, and source freshness checks
• Automate CI/CD deployment — GitHub Actions or equivalent for safe production releases
• Expose metrics via Semantic Layer — Consistent definitions for BI consumption

---

Frequently Asked Questions

What is the difference between dbt Core and dbt Cloud?

dbt Core is the open-source command-line tool installed via pip install dbt-core. dbt Cloud is dbt Labs' hosted service adding a web IDE, scheduling, observability, and governance features. The handbook references dbt generally; both implementations follow identical project structure and SQL patterns.

Where should I store dbt credentials securely?

Store credentials in ~/.dbt/profiles.yml using environment variable references ({{ env_var('DBT_PASSWORD') }}). Never commit this file. For CI/CD, use your platform's secrets manager (GitHub Secrets, GitLab CI variables, etc.). This aligns with the handbook's Beginner Bootcamp guidance on secret handling.

How do I connect dbt to multiple data warehouses?

Define separate outputs within the same profile in profiles.yml, or create distinct profiles entirely. Switch between them using dbt run --target production or the DBT_PROFILES_DIR environment variable. Each output specifies its own type (snowflake, bigquery, etc.) and connection parameters.

What materialization strategy should I use for large tables?

Use materialized='incremental' for large, append-only datasets. dbt inserts only new or changed rows based on your unique_key and incremental_strategy configuration. For frequently queried final tables, use 'table'. For lightweight transformations, 'view' avoids storage overhead.

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