dbt in Anger: Modeling, Contracts, Testing, CI, and the Setup That Scales to 500+ Models

I've inherited enough dbt projects to know the failure mode: 300 models, no layering, tests everywhere and nowhere, CI takes 40 minutes and everyone waits. The fix is boring and repeatable. This guide is the exact playbook I use when a team asks me to unstick their warehouse.

It pairs with modern data stack 2026 (where dbt fits in the wider stack), BigQuery cost control (the warehouse bill dbt drives), and Postgres for product engineers (the source system dbt usually reads from).

Enforce with a linter (SQLFluff + dbt-checkpoint or the built-in dbt-project-evaluator package). Manual review is not enough — the moment someone joins a source directly in a mart, the swamp starts.

A dbt contract asserts that a model's output columns, types, and constraints are stable. Change the contract and dbt refuses to run until you version the model. It turns silent schema drift into a build failure — the single cheapest insurance in analytics engineering.

models:
  - name: mart_orders_daily
    description: "One row per order per day. Source of truth for revenue dashboards."
    config:
      contract: { enforced: true }
      meta:
        owner: analytics-eng
        pii: false
        refresh_sla: 1h
    columns:
      - name: order_date
        data_type: date
        constraints: [{ type: not_null }]
      - name: order_id
        data_type: string
        constraints: [{ type: not_null }, { type: unique }]
      - name: revenue_usd
        data_type: numeric
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0"

Rule: every model under marts/ has an enforced contract. Every intermediate model has at least a described schema. Staging models get away with just not_null on the PK.

There are four categories of tests. Only three earn their keep.

1. Structural — PK uniqueness and not-null on every mart. Cheap, always on.
2. Referential — foreign-key style relationships on the joins that matter. relationships test in dbt.
3. Business invariants — revenue ≥ 0, refunds ≤ original charge, event_time ≤ now(). These catch upstream data bugs your dashboards would otherwise inherit.
4. Freshness — SLA on every source table via freshness:. When a producer breaks, freshness fires before your tests do.

The category to skip: exhaustive not-null tests on every column. They pass forever, add noise on failure, and give false confidence. Test what matters.

sources:
  - name: shopify
    tables:
      - name: orders
        loaded_at_field: updated_at
        freshness:
          warn_after: { count: 2, period: hour }
          error_after: { count: 6, period: hour }

Full dbt build on every PR is slow, expensive, and trains contributors to ignore CI. The recipe:

1. In CI, materialize into a PR-scoped schema like pr_1234.
2. Fetch prod's manifest.json as the state baseline.
3. Run dbt build --select state:modified+ --defer --state ./prod-manifest.
4. On merge, drop the PR schema in a scheduled cleanup job.

# GitHub Actions excerpt
- run: |
    aws s3 cp s3://our-bucket/manifest.json ./prod-manifest/manifest.json
    dbt deps
    dbt build \
      --select state:modified+ \
      --defer --state ./prod-manifest \
      --target ci --vars '{ pr_id: ${{ github.event.number }} }'

Deferred references let modified models point at prod tables for unmodified ancestors — you don't rebuild the world for a one-line change. This is the single change that makes dbt CI feel fast.

{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={ 'field': 'order_date', 'data_type': 'date' },
    on_schema_change='append_new_columns'
) }}

SELECT ... FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
  WHERE order_date >= date_sub(current_date(), interval 3 day)
{% endif %}

If active user is defined in four dashboards, all four are wrong. Adopt the dbt Semantic Layer (MetricFlow) or a peer like Cube, and forbid metric math in BI tools. This is the single change that ends the “which number is right?” meeting.

semantic_models:
  - name: orders
    model: ref('mart_orders')
    entities:
      - name: order_id
        type: primary
    dimensions:
      - name: order_date
        type: time
        type_params: { time_granularity: day }
      - name: country
        type: categorical
    measures:
      - name: revenue_usd
        agg: sum
        expr: revenue_usd

metrics:
  - name: gross_revenue
    type: simple
    label: "Gross revenue (USD)"
    type_params: { measure: revenue_usd }

BI tools query the semantic layer, not the mart directly. Currency, timezone, and business logic live once, in code, versioned.

dbt docs are the only up-to-date documentation most data teams will ever ship. Two habits make them worth the disk space:

1. Descriptions on every mart column — enforced in CI. If a contributor can't explain a column, it doesn't belong in a mart.
2. Exposures for every downstream dashboard, notebook, or reverse-ETL job. This is what turns the lineage graph from a curiosity into an impact-analysis tool: change a mart, dbt tells you which dashboards you're about to break.

exposures:
  - name: exec_dashboard
    type: dashboard
    url: https://looker.example.com/dashboards/42
    owner: { name: "CFO team", email: "cfo-team@example.com" }
    depends_on: [ref('mart_orders_daily'), ref('mart_costs_daily')]

The warehouse bill is 80% driven by three things: over-eager full refreshes, unpartitioned tables, and CI runs that materialize everything. Fixes:

• Tag models by refresh cadence (hourly, daily, weekly) and run selectors per cadence.
• Partition and cluster every mart > 10GB — this is warehouse-specific but always worth it.
• Set +require_partition_filter: true on BigQuery marts so nobody queries the full table by accident.
• Move ad-hoc exploration to a sandbox project with its own budget alerts.

For the full playbook on the warehouse side, see BigQuery cost control that scales and LLM cost dashboard in BigQuery.

• CODEOWNERS on marts — domain owners must approve schema changes on their marts.
• Every PR runs dbt-checkpoint — linter, model-must-have-description, model-must-have-tests.
• Weekly graph review — 30-minute session with the whole analytics-eng team. Look at the lineage, prune stale models, promote intermediates to marts when they've earned it.
• Deprecation policy — mark models with deprecated: true, keep them for one release cycle, then delete. No zombie models.

The safe pattern is add-alias-remove, spread across three PRs:

1. PR1 — add the new column alongside the old one. Ship. Update the contract to include both.
2. PR2 — migrate all downstream references (dashboards, notebooks, exposures). Verify via dbt list --select +old_column that nothing depends on it.
3. PR3 — drop the old column, update the contract, bump the model version.

This is boring on purpose. Boring analytics engineering is what gets you to 500 models without pager pain.

dbt tells you if a model built successfully. It doesn't tell you if the numbers are quietly wrong — a null spike, a distribution shift, a row-count drop that's within tolerance for tests but obviously anomalous.

Wire in elementary-data or re_data. Both piggyback on the dbt manifest to emit anomaly alerts, freshness dashboards, and lineage-aware incident reports. This is the analytics-engineering equivalent of the AI-observability patterns in observability for AI systems.

1. Three folders only (staging/intermediate/marts), enforced by linter.
2. Contracts on every mart; descriptions on every mart column.
3. Structural, referential, invariant, and freshness tests — categorized and severity-tagged.
4. CI runs state:modified+ --defer, uses PR-scoped schema, drops on merge.
5. Semantic layer live; BI tools query metrics, not marts.
6. Model tags for refresh cadence; separate schedules per tag.
7. Every downstream dashboard registered as an exposure.
8. CODEOWNERS on marts; deprecation policy documented.
9. Elementary or re_data wired into on-call.
10. Warehouse cost dashboard live (see BigQuery cost control).