What are the standard model layers in a dbt project?

The standard dbt project uses three model layers: staging (stg_), intermediate (int_), and marts (fct_ and dim_). Staging models are 1:1 with source tables and perform basic cleaning like renaming columns, casting types, and filtering deleted records. Intermediate models contain reusable business logic that combines multiple staging models. Marts models are the final output consumed by BI tools and analysts, organized by business domain. This layering enforces separation of concerns and makes the project maintainable as it grows.

When should I use incremental materializations in dbt?

Use incremental materializations when the source table is large enough that full table rebuilds are too slow or expensive, and the data has a reliable timestamp column that identifies new or updated records. Common examples include event logs, transaction tables, and time-series data. Do not use incremental for small reference tables, dimension tables that change unpredictably, or any table where you need to guarantee exact consistency with the source on every run.

How should dbt tests be organized?

Apply generic tests (unique, not_null, accepted_values, relationships) in YAML schema files for every model. These catch data quality issues automatically. Add singular tests as SQL files in the tests directory for complex business logic validation that generic tests cannot express. Test staging models for source data quality, intermediate models for join correctness, and mart models for business rule compliance. Run tests after every dbt run in CI and on a schedule in production.

What is the difference between dbt Core and dbt Cloud?

dbt Core is the open-source command-line tool that compiles and runs SQL transformations against your data warehouse. It requires you to manage scheduling, CI/CD, documentation hosting, and environment configuration yourself. dbt Cloud is the managed service that provides a web IDE, job scheduling, CI/CD integration, documentation hosting, and metadata API out of the box. Use dbt Core when you want full control and have the infrastructure team to support it. Use dbt Cloud when you want to focus on writing transformations rather than managing infrastructure.

How do you handle slowly changing dimensions in dbt?

dbt supports Type 2 Slowly Changing Dimensions through snapshots. A dbt snapshot tracks changes to a source table by storing each version of a row with valid_from and valid_to timestamps. Configure snapshots with a strategy of either timestamp (uses an updated_at column) or check (compares specified columns for changes). Snapshots should be stored in their own directory and run on a schedule before the main dbt run, so downstream models always have the latest dimension history available.

dbt Data Transformation Best Practices for Analytics Engineering

By Tom Henriksen| Oct 1, 2026| 10 min read

dbt has become the standard tool for data transformation in the modern analytics stack. It compiles SQL models into warehouse-native queries, manages dependencies between transformations, and provides testing and documentation as first-class features. But using dbt is not the same as using it well. Projects that start with a flat directory of SQL files grow into unmaintainable tangles within months. The practices described here prevent that outcome by establishing structure, naming conventions, and testing patterns from the beginning.

The core principle is that a dbt project should be organized around how data flows through the warehouse, not around how queries were originally written. Each model serves a specific purpose in the transformation pipeline, with clear contracts between layers. This makes it possible for multiple analytics engineers to work on the project simultaneously without stepping on each other's changes.

Model Layering

Every dbt project should use three model layers: staging, intermediate, and marts. This is not a suggestion — deviating from this pattern creates confusion about where logic belongs, where data comes from, and what downstream consumers can rely on.

Staging Layer

Staging models are 1:1 with source tables. Each staging model reads from exactly one source and performs only light transformations: column renaming to project conventions, type casting, and filtering soft-deleted records. No joins. No aggregations. No business logic.

-- models/staging/stripe/stg_stripe__payments.sql
with source as (
    select * from {{ source('stripe', 'payments') }}
),

renamed as (
    select
        id as payment_id,
        customer_id,
        amount_cents::numeric / 100 as amount_usd,
        currency,
        status as payment_status,
        created::timestamp as created_at,
        updated::timestamp as updated_at
    from source
    where not _fivetran_deleted
)

select * from renamed

Name staging models with the pattern stg_{source}__{entity}. The double underscore separates the source system from the entity name. This naming convention makes it immediately clear where data originates without reading the SQL.

Intermediate Layer

Intermediate models combine staging models to produce reusable building blocks. They contain business logic that multiple mart models need — deduplication, pivoting, joining related entities, and computing values that require multiple sources. Each intermediate model should be referenced by at least two downstream models; if only one model uses it, inline the logic.

-- models/intermediate/finance/int_payments_with_customers.sql
with payments as (
    select * from {{ ref('stg_stripe__payments') }}
),

customers as (
    select * from {{ ref('stg_stripe__customers') }}
),

subscriptions as (
    select * from {{ ref('stg_stripe__subscriptions') }}
),

joined as (
    select
        payments.payment_id,
        payments.amount_usd,
        payments.payment_status,
        payments.created_at as payment_date,
        customers.customer_id,
        customers.email,
        customers.segment,
        subscriptions.plan_name,
        subscriptions.billing_interval
    from payments
    inner join customers using (customer_id)
    left join subscriptions
        on payments.subscription_id = subscriptions.subscription_id
)

select * from joined

Marts Layer

Mart models are the final output. They serve specific business domains — finance, marketing, product — and are consumed by BI tools, reverse ETL, and downstream applications. Name fact models with fct_ prefix and dimension models with dim_ prefix. The approach to mart design shares principles with how organizations structure their data lakehouse table formats.

-- models/marts/finance/fct_monthly_revenue.sql
with payments as (
    select * from {{ ref('int_payments_with_customers') }}
),

monthly as (
    select
        date_trunc('month', payment_date) as revenue_month,
        segment,
        plan_name,
        count(distinct customer_id) as paying_customers,
        count(payment_id) as payment_count,
        sum(amount_usd) as total_revenue,
        avg(amount_usd) as avg_payment_amount
    from payments
    where payment_status = 'succeeded'
    group by 1, 2, 3
)

select * from monthly

Incremental Materializations

Full table rebuilds are fine for small tables but become prohibitively slow and expensive as data grows. A 500 million row event table that takes 45 minutes to rebuild on every run wastes compute and delays downstream models. Incremental materializations solve this by processing only new or changed rows.

-- models/marts/product/fct_page_views.sql
{{ config(
    materialized='incremental',
    unique_key='page_view_id',
    incremental_strategy='merge',
    on_schema_change='append_new_columns'
) }}

with events as (
    select * from {{ ref('stg_segment__page_views') }}
    {% if is_incremental() %}
    where event_timestamp > (
        select max(event_timestamp) from {{ this }}
    ) - interval '3 hours'
    {% endif %}
),

enriched as (
    select
        events.page_view_id,
        events.user_id,
        events.page_url,
        events.referrer_url,
        events.event_timestamp,
        sessions.session_id,
        sessions.session_start_at,
        users.signup_date,
        users.account_tier
    from events
    left join {{ ref('int_sessions') }} as sessions
        on events.session_id = sessions.session_id
    left join {{ ref('dim_users') }} as users
        on events.user_id = users.user_id
)

select * from enriched

The 3-hour lookback window accounts for late-arriving events that might have been missed in the previous run. This overlap is processed with merge strategy, which upserts by unique_key so duplicates are resolved. Without the lookback, any event that arrives late — due to network delays, client buffering, or ingestion lag — is permanently lost from the incremental model.

Testing Strategy

dbt provides two testing mechanisms: generic tests defined in YAML schema files and singular tests defined as SQL queries. Generic tests should cover every model in the project. Singular tests validate business rules that generic tests cannot express.

# models/staging/stripe/_stripe__models.yml
version: 2

models:
  - name: stg_stripe__payments
    description: "Cleaned Stripe payment events"
    columns:
      - name: payment_id
        description: "Unique payment identifier"
        tests:
          - unique
          - not_null
      - name: payment_status
        tests:
          - not_null
          - accepted_values:
              values: ['succeeded', 'pending', 'failed', 'refunded']
      - name: amount_usd
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 100000
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_stripe__customers')
              field: customer_id

Run dbt test after every dbt run in both CI and production. Tests that fail in CI block the pull request. Tests that fail in production trigger alerts. Treating test failures as informational rather than blocking undermines the entire testing strategy — if a failing test is acceptable, delete the test rather than ignoring the failure.

Source Freshness

Monitor whether source data is arriving on schedule. dbt's source freshness checks compare the most recent record timestamp against expected arrival intervals, which complements the monitoring approaches used in data quality frameworks at the pipeline level.

# models/staging/stripe/_stripe__sources.yml
version: 2

sources:
  - name: stripe
    database: raw
    schema: stripe
    freshness:
      warn_after: {count: 6, period: hour}
      error_after: {count: 12, period: hour}
    loaded_at_field: _fivetran_synced
    tables:
      - name: payments
        freshness:
          warn_after: {count: 1, period: hour}
          error_after: {count: 3, period: hour}
      - name: customers
      - name: subscriptions

CI/CD Pipeline

Every dbt project should have CI that validates changes before they reach production. The minimum CI pipeline compiles all models, runs changed models against a CI schema, and executes tests against those models.

# .github/workflows/dbt-ci.yml
name: dbt CI
on:
  pull_request:
    paths:
      - 'models/**'
      - 'macros/**'
      - 'tests/**'
      - 'dbt_project.yml'

jobs:
  dbt-ci:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: { python-version: '3.11' }
      - run: pip install dbt-bigquery
      - run: dbt deps
      - run: dbt compile --target ci
      - run: dbt build --target ci --select state:modified+
        env:
          DBT_CI_SCHEMA: "ci_pr_${{ github.event.number }}"

The state:modified+ selector runs only models that changed in the PR plus their downstream dependencies. This keeps CI fast for targeted changes while still catching downstream breakage. The CI schema isolates PR runs from each other and from production, preventing interference between concurrent PRs.

Documentation as Code

dbt generates documentation from model descriptions, column descriptions, and the DAG relationships between models. Maintaining documentation in YAML schema files ensures it stays in sync with the code — when a model changes, the documentation updates in the same pull request.

Write descriptions that explain what the model represents and why it exists, not how it is computed. "Monthly revenue aggregated by customer segment and plan, used by the finance team for board reporting" tells a reader whether this model answers their question. "Joins payments to customers and groups by month" is redundant with the SQL.

Host the documentation site where analysts can access it. dbt Cloud hosts docs automatically. For dbt Core, generate the site with dbt docs generate and serve the resulting target/ directory as a static site. The documentation catalog becomes the single source of truth for what data is available, where it comes from, and what guarantees it provides.