dbt Best Practices for SQL Transformation — Complete 2026 Guide

dbt Core v2.0 — What Every dbt Project Needs to Know in 2026

dbt Core v2.0 entered alpha at Snowflake Summit 2026 on June 1, 2026. It remains fully open source under the Apache 2.0 license. The headline change is that dbt Core now shares the same Rust-based runtime as the dbt Fusion engine — ending the two-engine era where Cloud and Core behaved differently. Every other 2026 best practice update flows from this foundation.

Note: dbt Labs and Fivetran announced a merger in 2026. The dbt product roadmap, licensing (Apache 2.0), and community packages remain unchanged. The merger affects go-to-market — not dbt Core behavior.

dbt Project Structure — Three Layers, One Responsibility Each

The most foundational dbt best practice is layer separation. A three-layer structure — staging, intermediate, and marts — keeps transformations readable and debuggable as projects grow. Each layer has a single job: staging cleans sources, intermediate assembles business logic, marts serve governed data to consumers.

This structure means a change in a source table affects only one staging file. Business logic changes are isolated to intermediate models. Mart restructuring does not touch the source cleanup layer. The DAG becomes predictable.

Recommended Layout

models/
├── 01_staging/
│   ├── _sources.yml        # source() definitions
│   ├── stg_customers.sql
│   └── stg_orders.sql
├── 02_intermediate/
│   ├── int_order_items.sql
│   └── int_customer_ltv.sql
└── 03_marts/
    ├── dim_customers.sql
    └── fct_orders.sql

Numbered folders keep execution order visible in file explorers and logs. Prefixes (stg_, int_, dim_, fct_) communicate a model’s role at a glance in compiled SQL, data catalogs, and error logs where folder context is absent.

Always Use ref() and source() — Never Hardcode Table Names

Hardcoding schema and table names is the fastest way to break a dbt project when environments change. ref() tells dbt about inter-model dependencies, builds the DAG, validates that upstream models exist at compile time, and swaps schemas between dev and prod targets automatically. source() does the same for raw ingestion tables, adding freshness monitoring capabilities.

-- Never do this
SELECT * FROM analytics_staging.stg_orders;

-- Always do this
SELECT * FROM {{ ref('stg_orders') }};

-- For raw source tables, use source()
SELECT * FROM {{ source('raw', 'orders') }};

With ref(), renaming schemas, cloning environments, or reorganizing models becomes a configuration change, not a search-and-replace across dozens of SQL files. In dbt Core v2.0, ambiguous or unused refs now surface as warnings — making it easier to catch stale references before they cause runtime failures.

source() Freshness Testing

Declaring sources in _sources.yml unlocks dbt source freshness — a command that checks how recently each source was updated and warns when data goes stale. This is the earliest possible point to detect upstream pipeline failures before they propagate into downstream models and dashboards.

# _sources.yml
sources:
  - name: raw
    tables:
      - name: orders
        loaded_at_field: _loaded_at
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}

Staging Layer Best Practices — One Source, One File, No Logic

Staging models read directly from source(), standardize column names and data types, apply very light cleaning (trim whitespace, convert units), and nothing else. No joins. No aggregations. No business rules. One staging file per source table.

This simplicity creates a stable contract. When a source changes a column name or data type, only one staging file updates. Every downstream model that uses ref('stg_orders') continues to work because the staging model’s output contract is unchanged.

{{ config(materialized='view') }}

SELECT
  id::int                AS customer_id,
  LOWER(TRIM(email))     AS customer_email,
  created_at::timestamp  AS created_at_utc,
  status::varchar        AS customer_status,
  -- amount_cents → amount in dollars at staging
  amount_cents / 100.0   AS amount_usd
FROM {{ source('raw', 'customers') }}

dbt Testing Best Practices — Test What Matters, Not Everything

dbt has four built-in generic tests: unique, not_null, accepted_values, and relationships. These cover the most common data quality assumptions. Beyond these, dbt_expectations (from the package) adds 30+ additional test types, and custom singular tests handle business-rule validation.

Minimum Test Coverage for Every Model

version: 2
models:
  - name: fct_orders
    description: "Order-level fact table for revenue and conversion reporting."
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: status
        tests:
          - accepted_values:
              values: ['placed','shipped','delivered','cancelled']
      - name: amount_usd
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0"

Unit Tests for Complex Logic (dbt v1.8+)

Unit tests, available from dbt v1.8, let you define small input tables and expected outputs to validate transformation logic in isolation. Use them for models with complex business rules that cannot be expressed as simple constraints — for example, loyalty tier calculation or revenue attribution logic.

# unit_tests/test_loyalty_tier.yml
unit_tests:
  - name: test_loyalty_tier_gold
    model: int_customer_tiers
    given:
      - input: ref('stg_customers')
        rows:
          - {customer_id: 1, lifetime_spend_usd: 1500}
    expect:
      rows:
        - {customer_id: 1, loyalty_tier: 'gold'}

dbt Materializations Best Practices — Match Strategy to Query Pattern

Choosing the wrong materialization wastes warehouse compute or returns stale data. The rule is simple: the closer a model is to a data consumer, the more likely it benefits from physical materialization.

Set Layer Defaults in dbt_project.yml

# dbt_project.yml
models:
  your_project:
    01_staging:
      +materialized: view
    02_intermediate:
      +materialized: view
    03_marts:
      +materialized: table

Setting defaults at the folder level means individual model files only need to declare materialization when they deviate from the layer default — keeping SQL files clean and reducing configuration drift.

dbt Snapshots Best Practices — Slowly Changing Dimensions Without MERGE Complexity

Snapshots capture how rows change over time — implementing SCD Type 2 without hand-written MERGE statements. dbt handles the effective date columns (dbt_valid_from, dbt_valid_to), the hash comparison, and the history table, consistently across all environments.

{% snapshot snap_customer_status %}

{{ config(
  target_schema = 'snapshots',
  unique_key    = 'customer_id',
  strategy      = 'check',
  check_cols    = ['status', 'subscription_tier', 'account_owner']
) }}

SELECT
  customer_id,
  status,
  subscription_tier,
  account_owner,
  updated_at
FROM {{ ref('stg_customers') }}

{% endsnapshot %}

dbt Naming Conventions — Self-Documenting Names That Work in Any Context

dbt model names appear in compiled SQL, data catalogs, logs, error messages, BI tool table pickers, and Data Agent queries. A well-chosen name communicates both the layer and the business concept without needing surrounding context.

Use business vocabulary, not source system vocabulary. dim_accounts is better than stg_sf_account because it describes what the model represents to a business user, not which system it came from. Column names should follow the same principle — customer_id, not cust_pk or c_id.

dbt Documentation Best Practices — Code and Docs in the Same File

Documentation that lives in a separate system drifts from reality within weeks. dbt documentation lives inside schema.yml files alongside test definitions — the same place engineers look when maintaining a model. Run dbt docs generate && dbt docs serve to produce a searchable, navigable data catalog from what is already in the repo.

models:
  - name: fct_orders
    description: >
      Order-level fact table. Source of truth for revenue and conversion
      reporting. Excludes test orders (is_test = true) and internal
      employee orders (customer_type = 'employee').
    columns:
      - name: order_id
        description: "Surrogate primary key for the order."
      - name: customer_id
        description: "FK to dim_customers.customer_id."
      - name: order_placed_at
        description: "UTC timestamp when the order was submitted."
      - name: gross_revenue_usd
        description: "Total order value before returns, refunds, or discounts."
      - name: net_revenue_usd
        description: "Gross revenue minus approved refunds. Use this for P&L reporting."

The description on gross_revenue_usd vs net_revenue_usd is exactly the kind of business context that prevents analysts from picking the wrong column. Column-level descriptions like this are also read by dbt Docs v2 and surface in Data Agent semantic context when agents query the warehouse.

dbt Macros Best Practices — DRY SQL at Scale

When the same SQL pattern appears in three or more models, it is a macro candidate. Macros centralize complex expressions, ensure consistent logic across models, and make changes in one place rather than many. Common macro use cases: surrogate key generation, date spine creation, conditional filter logic, data masking, and recurring CTE patterns.

-- macros/generate_surrogate_key.sql
{% macro generate_surrogate_key(column_list) %}
  {{ dbt_utils.generate_surrogate_key(column_list) }}
{% endmacro %}

-- Usage in any model
SELECT
  {{ generate_surrogate_key(['order_id', 'product_id']) }} AS order_line_id,
  order_id,
  product_id,
  quantity
FROM {{ ref('stg_order_lines') }}

When Not to Write a Macro

Macros add indirection that can confuse engineers unfamiliar with Jinja. If SQL logic only appears once or twice, a comment explaining the pattern is simpler than a macro. Reserve macros for genuinely repeated patterns — the three-occurrence rule is a reliable threshold.

dbt Performance Best Practices — Incremental Models and Physical Design

dbt performance is largely determined by two things: how much data moves through the DAG on each run, and how well the warehouse can scan and join that data. Both are under your control through model design and materialization choices.

Incremental Model Pattern

{{ config(
  materialized       = 'incremental',
  unique_key         = 'order_id',
  incremental_strategy = 'merge'
) }}

SELECT
  order_id,
  customer_id,
  order_placed_at,
  amount_usd
FROM {{ ref('stg_orders') }}

{% if is_incremental() %}
  WHERE order_placed_at >= (
    SELECT MAX(order_placed_at) FROM {{ this }}
  )
{% endif %}

On Microsoft Fabric, Databricks, and Snowflake, aligning cluster or partition columns with common WHERE clause patterns can reduce query cost dramatically — the warehouse skips entire file partitions rather than scanning all rows. For Fabric specifically, OPTIMIZE and ZORDER on the Delta table that underlies the model are applied separately after the dbt run. See Fabric Data Warehouse Optimization for those patterns.

dbt Packages Best Practices — Community Tools That Save Hundreds of Lines

dbt packages provide pre-built macros, tests, and models that the community maintains. Adding them takes two lines in packages.yml and one dbt deps command. The three packages every production dbt project should evaluate:

# packages.yml
packages:
  - package: dbt-labs/dbt_utils
    version: 1.3.0
  - package: calogica/dbt_expectations
    version: 0.10.4
  - package: dbt-labs/codegen
    version: 0.12.1

dbt CI/CD Best Practices — Slim CI, Linting, and Production Safeguards

Manual checks do not scale as teams and projects grow. CI/CD integration turns dbt best practices into automated gates that run on every pull request. The goal is fast feedback on what changed, not running thousands of models on every commit.

Slim CI — Only Run What Changed

Slim CI uses dbt state:modified with a deferred state artifact from the previous production run. Only modified models and their downstream dependents are built and tested — dramatically reducing CI run time on large projects.

# GitHub Actions example — slim CI
- name: dbt slim CI
  run: |
    # Download the production manifest from your artifact store
    dbt deps
    dbt lint                          # dbt Core v2.0 beta linter
    dbt build       --select state:modified+              --defer                               --state ./prod-manifest               --target ci

dbt lint (Beta — dbt Core v2.0)

dbt lint is built into the dbt Core v2.0 alpha and is SQLFluff-compatible. For projects already using SQLFluff as an external step, dbt lint replaces it with a faster, dbt-aware implementation that understands Jinja templating natively. Test it with dbt lint --select path:models/.

Full CI/CD Pipeline Structure

# Pull request pipeline
dbt deps                              # Install packages
dbt lint                              # Style and convention checks (v2.0 beta)
dbt parse --use-v2-parser             # Validate v2.0 compatibility
dbt build --select state:modified+    # Build and test modified models only

# Nightly production run
dbt source freshness                  # Check upstream data freshness
dbt build --full-refresh --select tag:daily_full  # Full refresh for daily models
dbt test --select tag:critical        # Run all critical tests

dbt Best Practices — Frequently Asked Questions