dbt: Integration Tests vs pytest, Which Approach to Choose

The problem

Native dbt tests (unique, not_null, relationships, custom generic tests) cover constraint validation on a single model very well. But as soon as you need to verify business logic that spans several models, for example that the sum of rows in an aggregated fact table matches the source, or that an upstream schema change doesn't silently break a downstream calculation, dbt YAML tests quickly become unreadable or insufficient. The question we asked ourselves: should we push dbt further, or move part of the validation to an external pytest suite?

Option 1: native dbt tests

Generic tests (schema tests) are enough for most data validation cases:

# models/marts/schema.yml
models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id

For more specific rules, a singular test (a SQL query that must return zero rows) stays in the same language as the models, versioned in the same place:

-- tests/assert_order_totals_positive.sql
select order_id, total_amount
from {{ ref('fct_orders') }}
where total_amount < 0

Advantages: colocated with the model, run by dbt test in the same CI run as the build, zero extra dependency, readable by anyone who already knows dbt.

Limitations: hard to express assertions that compare multiple runs over time, that call external APIs, or that need Python logic (parsing, statistical calculations). Scaling up to complex business rules quickly makes the YAML/SQL hard to maintain.

Option 2: pytest as a complement

We set up a lightweight pytest suite that runs after dbt build in CI, with a warehouse connection client (we use dbt-core's programmatic dbtRunner to stay within the same profile/target context):

# tests/test_revenue_consistency.py
import pytest
from tests.warehouse_client import query
 
def test_daily_revenue_matches_source():
    result = query("""
        select
            (select sum(total_amount) from marts.fct_orders) as mart_total,
            (select sum(amount) from staging.stg_raw_payments) as source_total
    """)
    row = result[0]
    # Tolérance pour les remboursements comptabilisés différemment en amont
    assert abs(row["mart_total"] - row["source_total"]) < row["source_total"] * 0.001
 
@pytest.mark.parametrize("env", ["staging", "production"])
def test_no_orphan_customers(env):
    result = query(f"""
        select count(*) as orphans
        from {env}.fct_orders o
        left join {env}.dim_customers c using (customer_id)
        where c.customer_id is null
    """)
    assert result[0]["orphans"] == 0

Advantages: full Python logic available (parametrization, cross-environment comparison, external calls), natural integration with the rest of the existing CI (often already pytest for application code), better readability for complex assertions with tolerances or multi-step calculations.

Limitations: further from the models in the repo, possible duplication with some basic dbt tests if you're not disciplined about the split, a learning curve for the analytics team who may not know pytest as well as dbt's SQL/YAML.

Comparison

What we use in production, and why

We keep native dbt tests for anything that's a data constraint local to a single model: uniqueness, nullability, referential integrity, value ranges via dbt_utils.accepted_range. It's quick to write, it lives with the model, and it covers 80% of real data-quality needs.

The pytest suite is reserved for cross-cutting consistency rules (source/mart reconciliation, cross-environment comparison, business rules with tolerance or conditional logic) that would be either impossible or unreadable in pure SQL. It runs in a separate CI job, after dbt build, against the same test warehouse.

Recommendation

Don't see this as a binary choice. For a team just starting out, native dbt tests are more than enough at first and cost very little to set up. Moving to pytest becomes worthwhile once you find yourself writing increasingly convoluted singular SQL tests to express logic that would be trivial in Python. That's the signal it's time to add a pytest suite alongside, not replace what's there.