The official dbt documentation teaches the mechanics in an isolated, clean environment. Production is not a clean environment. Production is messy source schemas, 500-model DAGs that nobody can visualise, incremental models with wrong unique keys that silently duplicate records, and CI runs that cost $50 each and run on every commit. The tutorial does not prepare you for any of that.

This handbook covers the five realities of production dbt: the project structure that prevents DAG collapse, the twelve model anti-patterns that appear repeatedly in real projects, the testing strategy that distinguishes what matters from what creates noise, the CI approach that keeps build costs rational, and the documentation standard that means something to an analyst at 2am. Everything here comes from real projects, not a sandbox environment.

The Five Production Realities

Before structure and anti-patterns: the things the tutorials omit.

Project Structure

Three mandatory layers. Enforce the separation strictly. The names are not suggestions.

• Staging (models/staging/): The only layer that reads from raw source() data. Purpose: light cleaning, renaming to standard conventions, casting data types. One staging model per raw table. No joins, no business logic.
• Intermediate (models/intermediate/): Where complex logic lives. Joins, complex aggregations, window functions. Building blocks that are not yet ready for end-users. Nothing in this layer should be queried directly by a BI tool.
• Marts (models/marts/): The business layer. Wide, denormalised tables ready for BI tools. End-users query these. Grain should be explicit in documentation or model name.

├── dbt_project.yml
├── packages.yml
├── models/
│   ├── staging/
│   │   ├── stripe/
│   │   │   ├── _stripe__sources.yml
│   │   │   ├── _stripe__models.yml
│   │   │   ├── stg_stripe__payments.sql
│   │   │   └── stg_stripe__customers.sql
│   │   └── salesforce/
│   ├── intermediate/
│   │   └── finance/
│   │       └── int_payments_pivoted_to_customers.sql
│   └── marts/
│       ├── finance/
│       │   ├── _finance__models.yml
│       │   ├── fct_payments.sql
│       │   └── dim_customers.sql
│       └── marketing/
├── macros/
│   └── cents_to_dollars.sql
└── tests/
    └── assert_positive_value_for_total_amount.sql

Configuration notes: use prefixes (stg_, int_, fct_, dim_) consistently. Group YAML config files by source or mart directory, not at project root. Use dbt-utils or dbt-expectations before writing custom macros for standard operations like surrogate keys.

The Twelve Model Anti-Patterns

Each of these appears in real production codebases. Most of them are still running.

1. The kitchen-sink staging model. Joining tables and applying business logic in a stg_ model.
   Fix: One raw table equals one staging model. Renaming, type casting, and light standardisation only.
2. The orphaned model. Models that are built but referenced by nothing downstream or in BI.
   Fix: Audit the DAG regularly. Deprecate and delete orphaned models. Orphaned models pay compute costs and add cognitive load indefinitely.
3. The circular dependency. Model A depends on Model B, which depends on Model C, which depends on Model A.
   Fix: Break the cycle with an intermediate model handling the shared logic. dbt will fail to compile a cycle: this is the most visible anti-pattern and usually the easiest to fix.
4. Hardcoded date filters. WHERE created_at >= '2023-01-01' or WHERE date = CURRENT_DATE.
   Fix: Use variables (var('start_date')) or macros. Historical backfills require this. CI runs require this. Hardcoded dates are discovered at the worst possible time.
5. The god model. A 2,000-line SQL file with 14 CTEs, 20 joins, and a single massive output.
   Fix: Break it into intermediate models. Debugging a 14-CTE file at 3am after a pipeline failure is the situation this anti-pattern creates.
6. Inconsistent grain in the marts layer. Models at user-level, daily-user-level, and session-level with no naming convention indicating which is which.
   Fix: Declare grain in documentation or model name (fct_user_daily_activity). Analysts joining two marts tables at different grains produce wrong numbers silently.
7. Incremental materialisation for small tables. Making a 50,000-row dimension table incremental.
   Fix: Full table rebuild. The overhead of maintaining incremental logic and state exceeds the benefit at this size.
8. Full table rebuild for enormous event logs. Rebuilding a 10-billion-row event table from scratch nightly.
   Fix: Convert to incremental materialisation with is_incremental() filtering. This is the correct use case for incremental models.
9. Missing or wrong unique_key in incremental models. Building an incremental model without defining unique_key.
   Fix: Always define unique_key, typically a surrogate key via dbt_utils.generate_surrogate_key. Without it, every incremental run appends duplicates silently.
10. Over-testing wide tables. Testing not_null on every column in a 200-column table.
    Fix: Test primary keys, foreign keys, and critical metric columns. Over-testing creates an always-failing pipeline and trains analysts to ignore test output entirely.
11. Undocumented sources. Writing models against raw_db.stripe.charges directly without a sources.yml definition.
    Fix: Always define sources. Source freshness tracking and complete lineage graphs require it. A project with undefined sources has no lineage graph worth trusting.
12. Vendor-specific SQL in the marts layer. Hardcoding Snowflake JSON parsing functions deep in business logic models.
    Fix: Push vendor-specific syntax into staging models or macros. Marts layer should be as close to ANSI SQL as possible. Future warehouse migrations depend on this.

The Testing Strategy

The goal is not comprehensive test coverage. The goal is targeted testing of what matters, with a clear distinction between failures that stop the pipeline and failures that generate a warning.

The Four Generic Tests

• unique: Essential for all primary keys.
• not_null: Essential for primary keys, foreign keys, and critical metric flags.
• accepted_values: For status columns with a finite expected set (['pending', 'success', 'failed']).
• relationships: Foreign key tests ensuring referential integrity between fact and dimension tables.

Custom Singular Tests

Write custom tests only when business logic requires it. This is an example of a test that belongs in production: it catches a real failure mode.

-- tests/assert_order_total_matches_line_items.sql
with orders as (
    select * from {{ ref('fct_orders') }}
),
line_items as (
    select * from {{ ref('fct_order_line_items') }}
)

select
    o.order_id,
    o.total_amount,
    sum(l.item_amount) as calculated_amount
from orders o
left join line_items l on o.order_id = l.order_id
group by 1, 2
having o.total_amount != sum(l.item_amount)

If this query returns rows, the test fails. The test question being asked: does the order total match the sum of its line items? This is a business invariant that should never be violated. Tests like this are worth writing. Tests on every column of a dim table are not.

Severity Configuration

• severity: warn: Data quality degradation that should not stop the pipeline. A user missing an email address is a data quality signal, not a deployment blocker.
• severity: error: Pipeline-breaking issues that must halt execution. A duplicate primary key in a fact table is an error. Letting it through corrupts downstream reporting.

Source vs Data Freshness

• Source freshness: Whether raw data has landed in the warehouse. Configure in sources.yml. Run dbt source freshness on a schedule, not on demand.
• Data freshness: Implied by a successful DAG run. If the pipeline ran successfully, the marts are as fresh as the last run.

Performance and Materialisation

Materialisation is a cost decision. Make it deliberately at each layer.

• Ephemeral: Very light SQL snippets used once or twice downstream. Functions as a CTE. Difficult to debug. Limit usage.
• View: Default for staging models. Correct for models that compute quickly without heavy joins.
• Table: Default for marts and heavy intermediate models. Compute once, query fast many times.
• Incremental: For massive event logs, immutable data, or large fact tables where full refreshes are cost-prohibitive. Not for small tables. Not for frequently changing dimensions.

Warehouse-specific optimisation example for BigQuery. Align partition keys with the filters the BI tool uses most frequently:

{{
    config(
        materialized='table',
        partition_by={
            "field": "created_at",
            "data_type": "timestamp",
            "granularity": "day"
        },
        cluster_by=['customer_id', 'status']
    )
}}

Before making a model incremental: does rebuilding this table in full cost more than the engineering time required to maintain incremental logic? For tables under 10 GB, the answer is almost always no. Full rebuild is cheaper and safer.

Slim CI

Running dbt build on the entire project for every pull request is the default configuration. It is also the configuration that makes a large project economically unrunnable. Slim CI is the fix.

The principle: when a PR is opened, build only the models that changed and their downstream dependencies. Defer to the production run for everything else.

# Three steps:
# 1. Production job produces manifest.json after every successful run.
# 2. CI job downloads the production manifest.json.
# 3. CI job runs:
dbt build --select state:modified+ --defer --state ./prod-run-artifacts

This builds only modified models and their children. Upstream dependencies are deferred to production, which means CI tests the actual changes against real production data without rebuilding the entire DAG.

Environment Targets

• Dev: Builds into developer-namespaced schemas (dbt_alice, dbt_bob). Isolates development from shared environments.
• CI: Builds into a temporary schema (dbt_pr_123). Schema is dropped when the PR merges.
• Prod: Builds into analytics or marts schemas. Runs from the main branch only.

Run vs Deploy Jobs

• Run job: Runs on a schedule (every few hours) to keep marts fresh. dbt build --select marts.
• Deploy job: Runs on merge to main. Occasionally runs a full --full-refresh on weekends to correct incremental drift.

Documentation That Gets Read

The documentation that matters is not comprehensive column documentation. It is the information an analyst needs at 2am answering a CEO question.

Document three things: the grain of the table (one row per what?), complex join decisions (why left instead of inner?), and critical business metrics (revenue, is_active, churn_date). Everything else is noise.

# _marts__models.yml
version: 2

models:
  - name: fct_orders
    description: '{{ doc("fct_orders_description") }}'
    columns:
      - name: order_id
        description: Primary key.
        tests:
          - unique
          - not_null
      - name: status
        description: The fulfilment state.
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'returned']

# docs.md
{% docs fct_orders_description %}
This fact table contains all orders placed by customers.
Grain: one row per order.
Important: Excludes test orders created by internal staff
(filtered via is_test_account = false).
{% enddocs %}

The note about is_test_account = false is the documentation that matters. Without it, the next analyst to query the table will include internal test orders in revenue figures and spend two hours investigating a discrepancy that does not exist.

Nauman Shahid builds zero-dependency data infrastructure for organisations in the UAE and Gulf region. If your production dbt project has grown beyond the point where a single engineer can reason about the full DAG, a diagnostic engagement identifies the structural issues: www.mindflex.tech.