dbt Testing Strategy by Layer

Testing intensity in a dbt project should increase toward the edges of the DAG. Sources are where problems enter the pipeline; marts are where they reach consumers. Applying the same test density at every layer tends to produce over-coverage at intermediate models and under-coverage at marts.

Sources: Test Only What’s Fixable

At the source layer, you’re testing data you don’t control. The discipline is restraint — only add tests you can actually act on.

Source freshness is the most valuable test at this layer. Configure loaded_at_field and appropriate thresholds based on your SLA commitments. The severity matters: use error for business-critical sources where stale data is worse than no data, warn for informational sources where delays are tolerable.

sources:
  - name: salesforce
    freshness:
      warn_after: {count: 12, period: hour}
      error_after: {count: 24, period: hour}
    tables:
      - name: accounts
        loaded_at_field: systemmodstamp
        columns:
          - name: id
            data_tests:
              - unique
              - not_null

Primary key tests belong at sources only if duplicates or nulls in those keys are actually fixable upstream. If your source system has known duplicate records you can’t remove, don’t add a unique test that will fail every run. Handle the deduplication in your base model and skip the source-level test. A test you can’t act on is noise.

This is the layer where many teams over-test. They add unique, not_null, accepted_values, and relationships to every source column because “that’s what you do with dbt tests.” But when those tests fail because the source system sends inconsistent data (which it will), there’s nothing actionable to do. You’ll either accept permanent failures or delete the tests — and the latter is the right call.

Base: The Clean Contract

Base models have one job: produce well-typed, deduplicated, null-handled records from raw sources. Testing at this layer enforces the “clean contract” — the promise that everything downstream receives data that meets basic structural guarantees.

The key principle: don’t test your cleanup. If your base model filters out null order_id values, adding a not_null test on order_id is redundant. It will always pass because you just filtered them. This adds execution time without adding safety.

What to test instead:

Primary keys — unique + not_null on the grain of the base model. After deduplication, the primary key must be clean.

Business-specific anomalies — values outside acceptable ranges, unexpected categorical values. These catch issues that survive your cleanup logic.

models:
  - name: base__shopify__orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: order_total
        data_tests:
          - dbt_utils.expression_is_true:
              expression: ">= 0"
      - name: currency
        data_tests:
          - accepted_values:
              values: ['USD', 'EUR', 'GBP', 'CAD']

Notice what’s absent: freshness tests (those belong at the source), referential integrity tests (foreign keys haven’t been joined yet), and duplicate checks on columns other than the primary key (deduplication should handle those).

Intermediate: Consequences of Joins

Intermediate models join and aggregate. The testing focus shifts to verifying that those operations produced the expected results.

Primary key integrity after granularity changes. If you’re aggregating from order line items to orders, the order_id should now be unique. If you’re joining two tables, the resulting model’s grain should be what you intended. A unique test on the post-join primary key is your first signal that a join multiplied rows unexpectedly.

models:
  - name: int__orders__enriched
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - relationships:
              to: ref('int__customers__base')
              field: customer_id

Referential integrity on joined tables. The relationships test here verifies that your join conditions are correct — that the customer_id in your orders model actually exists in the customers model. If it doesn’t, you have orphaned records that will produce NULLs downstream.

Sanity checks on aggregations. If you’re summing revenue per customer, an expression_is_true on total_revenue >= 0 catches sign errors in the aggregation logic.

The intermediate layer is where you catch join explosions before they corrupt mart outputs. A missing condition in a join condition that multiplies rows by 10x will cause your mart’s revenue to look 10x too high — and the intermediate layer primary key test is what stops this before it reaches dashboards.

Marts: Maximum Investment

The mart layer deserves the heaviest testing investment. These are the models stakeholders query, BI tools connect to, and downstream pipelines consume. Problems that reach this layer are customer-facing.

Unit tests for complex business logic. Customer segmentation rules, financial calculations, forecasting metrics — these are where unit tests earn their keep. The 1% of columns that warrant unit tests is almost entirely concentrated here.

Comprehensive generic tests on all exposed columns. Not just primary keys — all key metrics should have range checks, all categorical fields should have accepted_values.

Model contracts for schema stability. Any mart model that BI tools or reverse ETL pipelines query should have contract: {enforced: true}. This prevents schema changes from silently breaking consumers.

models:
  - name: mrt__sales__customers
    config:
      contract:
        enforced: true
    columns:
      - name: customer_id
        data_type: int64
        data_tests:
          - unique
          - not_null
      - name: lifetime_value
        data_type: numeric
        data_tests:
          - dbt_utils.expression_is_true:
              expression: ">= 0"
      - name: segment
        data_type: string
        data_tests:
          - accepted_values:
              values: ['champion', 'loyal', 'at_risk', 'new', 'prospect']

Focus on net-new calculated columns. Don’t re-test passthrough fields that were already validated upstream. If order_id was tested at the base layer and it hasn’t changed grain or been joined, testing it again at the mart layer is redundant. Concentrate test coverage on the columns that were computed at this layer.

The Layered View

Across a typical project:

This distribution means most of your test YAML will live in the mart layer, with lighter but targeted coverage at sources and base. The intermediate layer gets the “paranoid join” tests — primary key checks after every model that changes granularity.

If you’re instrumenting a new project and don’t know where to start: add unique + not_null to every primary key at every layer, add source freshness on every ingestion source, and add contracts on every mart. That baseline alone will catch the majority of production incidents before they reach stakeholders.