dbt Data Transformation: Best Practices & Pitfalls

This page documents practical guidance for using dbt (data build tool) to implement data transformations in a warehouse environment. It covers how to structure models, manage dependencies, write efficient SQL, and avoid common configuration errors. These patterns apply when building data pipelines with dbt that produce materialized tables or views in a data warehouse like Snowflake, BigQuery, or Redshift.

Model Structure and Composition

Models should be defined as .sql files inside a project's models/ directory. Each model file contains one SQL statement that creates a table or view. For example:

-- models/stg_users.sql
SELECT
  user_id,
  email,
  created_at
FROM {{ source('raw', 'users') }}

The {{ source() }} macro references data from the project's sources.yml configuration file, which defines external data sources. A minimal sources definition looks like:


version: 2
sources:
  - name: raw
    tables:
      - name: users
        description: "User accounts from the application"

Models can reference other models using {{ ref() }}. The ref() function resolves to the fully qualified name of a model, and it also creates dependency tracking. Example:

-- models/fact_orders.sql
SELECT
  order_id,
  user_id,
  amount_usd,
  order_date
FROM {{ ref('stg_orders') }}
WHERE order_date >= '2023-01-01'

Model names must be valid identifiers in the target SQL engine. Avoid special characters, spaces, or reserved keywords.

Materialization Patterns

Use materialized configuration to control how models are built. Supported values include:

table: Creates a physical table
view: Creates a view
incremental: Builds incrementally using a WHERE clause or merge logic

Example of an incremental model:

-- models/fact_events.sql
{{ config(materialized='incremental', unique_key='event_id') }}

SELECT * FROM {{ source('raw', 'events') }}
{% if is_incremental() %}
WHERE event_timestamp > (SELECT MAX(event_timestamp) FROM {{ this }})
{% endif %}

The unique_key parameter is required for incremental materializations. If not specified, dbt will fail with an error like:

> "Incremental models require a unique_key to be defined"

Avoid using table materialization for large datasets unless you explicitly plan for full refreshes. A table materialization without unique_key in incremental mode leads to missing data or duplicate rows.

Configuration and Variables

Project-level configuration goes into dbt_project.yml. This file defines the project's name, target environment, and model configurations:

# dbt_project.yml
name: 'analytics'
version: '1.0.0'
config-version: 2
profile: 'default'
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"
log-path: "logs"
clean-targets:
  - "target"
  - "logs"

Variables can be defined in dbt_project.yml or via command-line flags. Example:

# dbt_project.yml
vars:
  'start_date': '2023-01-01'

Reference them using {{ var('start_date') }}. Using var() with an undefined variable silently returns a string 'None' rather than raising an error, which can lead to subtle bugs.

Testing and Documentation

Use the tests/ directory for unit tests. A basic test definition:

# models/schema.yml
version: 2
models:
  - name: fact_orders
    description: "Orders with their associated user data"
    columns:
      - name: order_id
        tests:
          - unique
          - not_null

Tests are run using the command:

dbt test

If a model has no schema defined, dbt will infer column types but may miss required constraints. A missing unique_key in an incremental model causes the build to fail with:

> "Incremental models require a unique_key to be defined"

Dependencies and Compilation

Models are compiled into SQL based on their dependencies. The ref() macro ensures proper dependency resolution. If a model references another model that doesn't exist, dbt fails with:

> "Model 'analytics.stg_users' not found"

Dependencies must be declared explicitly in the SQL or schema files. Implicit dependencies (like referencing a table without a ref() call) are not tracked and can cause inconsistent builds.

Incremental Builds and Data Freshness

Incremental models support full_refresh flag. Use:

dbt run --models fact_events --full-refresh

To force a full rebuild of an incremental model. Failing to specify unique_key in the config results in failed incremental runs with:

> "Unique key must be specified for incremental models"

When using incremental models, ensure that the SQL query correctly identifies new or updated rows. A common mistake is including only the WHERE clause but not handling deletions properly.

Error Handling and Debugging

Use dbt run --debug to get verbose output, especially during CI/CD builds. The debug mode shows all compiled SQL and environment settings.

If a model fails due to an unknown column, dbt reports the error from the SQL engine directly:

> "column 'user_id' does not exist in table 'analytics.stg_users'"

Use dbt run --models +fact_orders to include all dependent models. If a dependency is missing or misnamed, the build fails with:

> "Model 'analytics.stg_orders' not found"

Performance Considerations

Avoid selecting all columns (SELECT *) in intermediate models unless necessary. Select only required columns to reduce data movement and improve performance.

Use --select to run only specific models:

dbt run --select stg_users

The --exclude flag is useful for skipping problematic models during troubleshooting:

dbt run --exclude fact_orders

Schema Management

Schema definitions in schema.yml are used to document and validate model structures. A schema file should define all columns, their types, and tests. Example:

version: 2
models:
  - name: stg_users
    description: "Raw user data"
    columns:
      - name: user_id
        description: "Unique identifier for the user"
        tests:
          - unique
          - not_null

If a column is missing from the schema, dbt does not raise an error but may cause downstream issues if the model is expected to have that column.

Deployment and Rollback

Use dbt deploy (if supported by your warehouse) or manual SQL scripts to apply changes. The --vars flag allows runtime variable overrides:

dbt run --vars '{"start_date": "2023-06-01"}'

When deploying changes, always verify that the new model schema matches expectations. A mismatch can silently overwrite existing data or cause query failures.

Common Pitfalls

Do not use {{ config() }} in a model without a materialization type. The following will produce an invalid SQL:

-- models/bad_model.sql
{{ config(unique_key='id') }}
SELECT * FROM some_table

This fails because no materialization is specified.

Avoid naming models with reserved SQL keywords like order, group, or select. These can cause SQL compilation errors in some warehouses.

Use dbt run --models +stg_users to include dependencies, but be careful not to accidentally build unrelated models that may depend on the target. The --models flag supports glob patterns:

dbt run --models "fact_*"

Model Naming Conventions

Model names must be lowercase and use underscores. Avoid camelCase or PascalCase. Example valid names:

stg_users
fact_orders
dim_products

Invalid names include StgUsers or FactOrders. These are not rejected at compile time but can cause confusion in logs and documentation.

Runtime Configuration

Set runtime options using command-line flags or environment variables. For example, to override the target schema:

dbt run --target production --vars '{"schema": "analytics_prod"}'

If a variable is undefined in the dbt_project.yml, it silently becomes 'None' instead of causing an error.

Environment-Specific Settings

Use profiles in profiles.yml to define warehouse connections. Example:

# profiles.yml
default:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: my-account
      user: my-user
      password: my-password
      role: ACCOUNTADMIN
      database: ANALYTICS_DB
      warehouse: COMPUTE_WH
      schema: analytics_dev

The target field determines which environment configuration is used. Switching environments requires changing the target name or using --target flag.

Model Dependencies and DAG

Model dependencies are tracked based on ref() calls in SQL. If a model references another without including it in its own SQL, dbt does not track the dependency correctly. This can lead to inconsistent builds where dependent models are not updated when upstream tables change.

Use dbt deps to install packages or manage dependencies from the packages.yml file:

# packages.yml
packages:
  - package: dbt-labs/dbt_utils
    version: "1.0.0"

Handling Data Types

Define expected data types in schema files to ensure consistency. If a column has a type mismatch, dbt does not enforce it at compile time, but queries might fail at runtime.

Avoid using {{ config() }} inside SQL without proper materialization. The macro must be used correctly:

-- Good
{{ config(materialized='table') }}
SELECT * FROM some_table

-- Bad
{{ config(unique_key='id') }}
SELECT * FROM some_table

The bad version will compile but fail during execution due to missing materialization.

Summary of Critical Checks

Always specify unique_key for incremental models.
Use proper naming conventions: lowercase, underscore-separated.
Define all dependencies with ref() macro.
Test models with dbt test.
Avoid using SELECT * unless necessary.
Run with --debug when troubleshooting.
Set variables via --vars or profiles.yml.
Use schema files to define expected structures.
Ensure all referenced models exist.
Use --select and --exclude for targeted runs.

This page covers core practices and failure modes in dbt transformation workflows. It avoids vendor-specific features and instead focuses on portable, reliable patterns for data modeling and execution.

This page was rewritten on 16 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.