Analytics Engineering Guide

dbt Best Practices

dbt transformed analytics engineering, but a messy project can slow you down worse than raw SQL. This guide covers project structure, testing, incremental models, and performance optimization.

Diagram Your dbt DAGMedallion Architecture
20 min readFor Analytics Engineers & Data Engineers

See it as a diagram

Everything below, as a diagram you can edit. Describe yours and see it in seconds.

89/2000• 3 credits left
Try:

No account needed · Editable canvas, not a picture

1. Project Structure

A well-organized dbt project follows a layered directory structure. Each layer has a clear purpose and only references the layer before it.

models/
├── staging/           # 1:1 with sources, renamed columns
│   ├── stripe/
│   │   ├── _stripe__sources.yml
│   │   ├── stg_stripe__payments.sql
│   │   └── stg_stripe__customers.sql
│   └── salesforce/
│       ├── _salesforce__sources.yml
│       └── stg_salesforce__accounts.sql
├── intermediate/      # Business logic, joins, transformations
│   ├── int_payments_pivoted.sql
│   └── int_customer_orders.sql
└── marts/             # Final business-ready models
    ├── finance/
    │   └── fct_revenue.sql
    └── marketing/
        └── dim_customers.sql

Anti-Pattern: Flat Models

Don't put all 200 models in one directory. Without layers, you get circular dependencies, untestable logic, and impossible debugging.

2. Model Layering

Staging (stg_)

One model per source table. Rename columns, cast types, add surrogate keys. No joins, no business logic. Materialized as views.

Intermediate (int_)

Join staging models, apply business logic, pivot/unpivot. Not exposed to BI tools. Materialized as ephemeral or views.

Marts (fct_ / dim_)

Final business-ready fact and dimension tables. Consumed by BI tools and analysts. Materialized as tables or incremental.

3. Testing Strategy

Schema Tests

Built-in: unique, not_null, accepted_values, relationships. Define in .yml files. Every primary key should have unique + not_null.

Data Tests

Custom SQL queries in tests/ directory. Should return 0 rows. Test business rules like "revenue should never be negative."

Unit Tests (dbt 1.8+)

Mock input data, assert expected output. Test complex transformations in isolation without running against the warehouse.

CI Tests

Run tests on PRs with dbt slim CI. Use state:modified to only test changed models and their downstream dependents.

4. Incremental Models

Incremental models process only new or changed records instead of rebuilding entire tables. Essential for large datasets where full refreshes are too slow or expensive.

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

SELECT *
FROM {{ ref('stg_events') }}

{% if is_incremental() %}
WHERE event_timestamp > (
    SELECT MAX(event_timestamp) - INTERVAL '3 hours'
    FROM {{ this }}
)
{% endif %}

Use incremental when table > 10M rows

Always define unique_key for merge strategy

Add lookback window for late-arriving data

Run --full-refresh weekly as a safety net

5. Performance Tips

1

Use ref() everywhere — never hardcode table names

2

Materialize staging models as views to save storage

3

Use ephemeral for intermediate calculations

4

Limit columns in SELECT — avoid SELECT *

5

Use clustering/partitioning on large tables

6

Pre-aggregate in intermediate models before final joins

7

Enable dbt Cloud defer to avoid rebuilding everything

8

Profile slow queries with database query plans

6. FAQ

What is the recommended dbt project structure?
Use layered directories: staging/ (1:1 source mappings), intermediate/ (business logic), marts/ (final models). Each layer only references the prior layer.
How should you test dbt models?
Four layers: schema tests (unique, not_null), data tests (SQL returning 0 rows), unit tests (dbt 1.8+), and CI tests (slim CI on PRs).
When should you use incremental models?
When tables exceed 10M rows, have a reliable timestamp, and full refreshes are too slow/expensive. Always define unique_key and add a lookback window.

Visualize your dbt DAG

AI-generated dbt lineage diagrams. Map models, sources, and dependencies with cloud icons.

Try Datadef Free