Testing Strategy and Data Quality at Scale

More Tests Is Not the Same Thing as Better Data Quality

Module 08 covered unique, not_null, accepted_values, andrelationships, plus custom generic and singular tests, and where in the DAG a test belongs. Everything in that module is correct in isolation. But a real dbt project with 200 or 300 models, run daily by a small platform team, cannot simply apply every one of those tests to every column of every model and call it a strategy. At that scale, "test everything" is not a stronger version of good testing — it is a different failure mode entirely.

Picture a project that has grown to roughly 5,000 individual test instances across its models — the sum of every unique, not_null, accepted_values, andrelationships test on every column anyone thought might be worth checking. On a normal day, some small percentage of those tests fail — a source system briefly emits a null it shouldn't, a rarely-populated optional field trips an accepted_values list that was written too strictly. None of these individual failures is severe. But multiplied across 5,000 tests, "some small percentage failing on a normal day" becomes dozens of red test results in every singledbt build, every single day, most of which nobody investigates because investigating all of them is not a full-time job anyone was assigned.

The fix is not fewer tests for the sake of fewer tests. It is a deliberate strategy: which tests matter enough to block a pipeline, which are worth a human's attention without blocking anything, where in the DAG a given risk actually lives, and who is responsible for looking at a failure when it happens. The rest of this module builds that strategy piece by piece, building directly on the testing mechanics from Module 08 rather than re-explaining them.

Source and Staging Tests Are High Leverage; Mart Tests Are High Visibility

Module 08's Part 07 already introduced the idea that a test's position in the DAG changes what kind of problem it catches. At scale, this distinction becomes the backbone of an entire test strategy, not just a debugging convenience. Two different DAG positions earn tests for two genuinely different reasons, and a mature strategy invests deliberately in both rather than treating "add a test" as one undifferentiated action everywhere.

Source and staging tests: highest leverage

A test on a source table or a thin staging model catches a problem before a single downstream model has built on top of it. This is the highest-leverage place to test, in the literal engineering sense of leverage: one test, placed as early as possible in the DAG, prevents an entire downstream fan of models — every intermediate model and mart that eventually ref()s this source, directly or transitively — from silently inheriting bad data. A singlenot_null test on a source table's primary key can protect dozens of downstream models that were never individually tested for that specific failure, simply because they all sit downstream of the one place it was actually caught.

Mart tests: highest visibility, highest cost of failure

A test on a mart-level fact or dimension table catches a different, later class of problem — one introduced by the transformation logic itself, not by the raw data — but it catches it at the point of maximum consequence: right before a BI tool queries that table, or a stakeholder opens a dashboard built on top of it. A mart-level test failing means the absolute last line of defense caught something; anything a mart-level test misses is what a stakeholder sees next, which is why mart-level test failures deserve the fastest, most senior attention even though they are structurally the "last" tests to run in the DAG, not the first.

A useful way to say this out loud in a design review: source and staging tests are aboutpreventing propagation, and mart tests are about catching what still got through. A strategy that only invests in one of these leaves a real gap — all source tests and no mart tests means a transformation bug introduced entirely within the DAG (a bad join, a miscounted aggregation) sails through to the dashboard with nothing to catch it; all mart tests and no source tests means every downstream model pays the cost of processing bad data before the one test at the very end finally flags it, several models later than necessary.

Not Every Test Failure Should Block a dbt build

Module 08 mentioned severity briefly; at scale it becomes one of the most important levers a test strategy has. Every generic and singular test accepts a severity config of error (the default) or warn. This single setting is what separates a test that is worth writing from a test that is worth writing and having block the entire pipeline when it fails — two genuinely different questions that get conflated when every test defaults to error without a second thought.

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
          # no severity override -- default error is correct here.
          # a duplicate or missing order_id is never acceptable.

      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id
              config:
                severity: error
                # a broken foreign key on the mart's core join key is a
                # hard stop -- downstream revenue reporting depends on it

      - name: shipping_notes
        tests:
          - not_null:
              config:
                severity: warn
                # a missing shipping note is a real but soft signal --
                # worth a human noticing, not worth blocking the whole
                # fct_orders build and everything that depends on it

The question every test's severity should answer, explicitly, at the time the test is written: if this fails, is it acceptable for dbt build to keep going and build everything downstream anyway, with a human reviewing the warning later? Or does a failure here mean something downstream is now provably untrustworthy, in which case the build must stop? A soft data-quality signal — an optional field's fill rate dropping, a slightly-out-of-range but plausible value — usually warrants warn. A broken primary key or a foreign key a report's join depends on usually warrants error.

error_if and warn_if — thresholds instead of a binary pass/fail

For tests where a handful of failures is expected and tolerable but a large number is not, dbt'serror_if and warn_if configs let a test specify numeric thresholds instead of failing on any single returned row.

models:
  - name: fct_orders
    columns:
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id
              config:
                error_if: ">100"
                warn_if: ">10"
                # fewer than 10 unmatched customer_ids -- ignored entirely,
                # a normal amount of timing lag between two source systems
                # 10-100 unmatched -- reported as a warning, worth a look
                # more than 100 unmatched -- treated as a genuine break,
                # build stops

This matters specifically for relationships-style tests where a small number of failures is a known, expected artifact of timing (a customer record that arrived a few minutes after their first order, in a system with no strict transactional guarantee across two source tables) rather than a genuine break. Without a threshold, that small and expected timing lag fails the test daily, on every run, contributing directly to the alert fatigue problem from Part 01.

Persisting Failing Rows Is What Makes a Failure Debuggable, Not Just Visible

Module 08 introduced store_failures: true as a way to persist a failing test's result set to a real table. At scale, this stops being a nice-to-have and becomes close to mandatory for any test whose failure needs to be investigated by more than one person, or investigated more than a few minutes after the run finished. Console output from a dbt build that ran overnight is, in practice, gone by the time anyone on a platform team looks at it the next morning — the CI job's log may still technically exist, but nobody opens a build log to hand-copy forty failing customer IDs out of scrollback.

tests:
  +store_failures: true
  +schema: dbt_test_failures

  my_dbt_project:
    staging:
      +store_failures: false
      # high-volume, low-severity staging tests -- persisting every
      # failure here would create enormous storage churn for little
      # value; console output is enough at this tier
    marts:
      +store_failures: true
      # mart-level test failures are exactly the ones worth a
      # queryable, persistent record

This configuration is itself a tiering decision, consistent with Part 02's DAG-position framing: high-volume staging tests, where a failure usually just means "re-run the test, look at the live data," do not need a persisted failure table. Mart-level tests, where a failure is rarer, more consequential, and more likely to need a second engineer's eyes days later, are exactly wherestore_failures earns its storage cost.

FAIL relationships_fct_orders_customer_id__customer_id__ref_dim_customers_
  Got 63 results, configured to fail if != 0 ...................... [FAIL 63 in 1.4s]

  -- persisted at analytics.dbt_test_failures.relationships_fct_orders_...
  -- an on-call engineer the next morning queries this table directly,
  -- filters by customer_id, and joins back to source system logs --
  -- without needing to re-run the test or dig through a CI log at all

The practical difference this makes at scale: a persisted failure table turns "re-run the query by hand to see what broke" into "query the table that already has the answer," which is the difference between a five-minute investigation and a fifteen-minute one repeated by every engineer who later needs to look at the same failure. Multiplied across a project with hundreds of mart-level tests, that time saved compounds directly into whether test failures actually get investigated at all, rather than being waved off because investigating them is annoying.

Source Freshness Is a Team Agreement, Not an Arbitrary Number

A structural test like not_null checks whether the data that arrived is well-formed. It says nothing about whether the data arrived on time. A source table can pass every structural test in the project while being six hours stale, and every downstream model built on top of it will look perfectly healthy — clean, well-formed, and quietly wrong because it reflects yesterday's world instead of today's. Freshness checks close this gap.

sources:
  - name: raw_ecommerce
    tables:
      - name: orders
        loaded_at_field: _loaded_at
        freshness:
          warn_after: {count: 6, period: hour}
          error_after: {count: 24, period: hour}

      - name: payments
        loaded_at_field: _loaded_at
        freshness:
          warn_after: {count: 1, period: hour}
          error_after: {count: 3, period: hour}
          # payments data feeds fraud and reconciliation checks that
          # cannot tolerate the same staleness window orders can

warn_after and error_after are checked by dbt source freshness(typically run as its own step before dbt build, so a stale source is caught before the pipeline wastes time transforming data that is already known to be too old). The specific numbers chosen here are not a technical fact about the data — they are a documented agreement between the data platform team and whoever consumes this data about what "acceptably fresh" means for this specific source, and different sources legitimately deserve very different thresholds, as the orders-versus-payments contrast above shows.

Freshness as an early-warning system, not just a compliance check

The highest-leverage placement for freshness checks is on the sources feeding the models with the tightest downstream latency requirements — exactly the kind of prioritization Part 02 argues for at the structural-test level, applied here to timeliness instead of correctness. A freshness check on the payments source failing an hour into a staleness window gives the platform team an hour's head start on investigating a broken upstream extract, well before any downstream model's own tests would have a chance to fail from data simply not being there yet.

A Pile of Tests Nobody Owns Is Not a Testing Strategy

Everything covered so far in this module — tiering, severity, thresholds, persisted failures, freshness SLAs — is mechanical. None of it matters if a test failing in production has no defined next step. A mature data-quality practice is as much an operational and cultural commitment as it is a set of YAML configs, and the questions below are the ones an actual team needs answered, in writing, before a critical test failure happens for the first time in production rather than during the incident itself.

The anti-pattern worth naming explicitly: a test that was added by an engineer who has since left the team, that nobody currently on the team fully understands the business reasoning behind, that fails intermittently, and that everyone has learned to re-run rather than investigate. This test is strictly worse than no test — it costs CI time and cognitive load on every failure, and its continued presence gives a false sense that "this business rule is being checked" when in practice nobody would actually notice if the rule were silently violated, because the failure output is never read closely anymore.

Trust is built by what gets caught before a stakeholder sees it, not by test count

A data team's credibility with the business is not a function of how many tests exist in the project — a stakeholder never sees the YAML files. It is a function of a much simpler, harder-won track record: does this team's data consistently turn out to be right, or does a stakeholder periodically discover a wrong number on their own and have to come ask why? Every test tiered correctly by DAG position, given the right severity, with a real owner for its failures, is a small contribution to that track record. A pile of 5,000 untiered, unowned tests that nobody reads is not — it can coexist with a team that still gets caught by a stakeholder finding the bug first, because volume of tests was never actually the thing determining whether problems get caught before they are seen.

Designing a Tiered Test Strategy for a Multi-Source Analytics Project

Consider a project with three source systems feeding one analytics warehouse: a Postgres application database (orders, customers), a third-party payments processor's API extract (payments), and a marketing platform's ad-spend export (ad_spend). A small, two-person platform team owns the dbt project and is on call for it. Below is the tiering decision for each layer, worked through the way Part 02 and Part 03 together would suggest.

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests: [not_null]

  - name: stg_customers
    columns:
      - name: customer_id
        tests: [unique, not_null]

# Reasoning: these are the two highest-traffic, most-depended-on
# staging models in the project. A break here propagates to nearly
# every downstream mart. Default error severity, no thresholds --
# a duplicate or missing key at this layer is never acceptable.

models:
  - name: stg_payments
    columns:
      - name: payment_id
        tests: [unique, not_null]
      - name: order_id
        tests:
          - relationships:
              to: ref('stg_orders')
              field: order_id
              config:
                warn_if: ">5"
                error_if: ">50"

# Reasoning: the payments API extract runs on a slightly different
# schedule than the orders sync, so a handful of payments referencing
# an order_id that hasn't landed yet is expected and NOT a real break
# -- hence a threshold instead of a strict zero-rows contract, matching
# Part 03's error_if/warn_if pattern.

models:
  - name: stg_ad_spend
    columns:
      - name: campaign_id
        tests:
          - not_null:
              config:
                severity: warn
      - name: spend_amount
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              config:
                severity: warn

# Reasoning: ad spend data feeds marketing-reporting dashboards, not
# revenue-critical pipelines. A malformed row here is worth a human's
# attention on a weekly review, not worth paging anyone or blocking
# fct_orders and everything downstream of the two higher-stakes
# source systems above.

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique:
              config: { severity: error, store_failures: true }
          - not_null:
              config: { severity: error }
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id
              config: { severity: error, store_failures: true }
      - name: order_status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'delivered', 'cancelled', 'refunded']
              config: { severity: error, store_failures: true }

# Reasoning: fct_orders feeds the company's primary revenue dashboard.
# Every test here is error severity with store_failures on -- this is
# the last checkpoint before a stakeholder sees the number, and any
# failure here is worth a persisted, queryable record per Part 04.

Notice the shape of the whole strategy: severity and persistence increase, and thresholds tighten, the closer a model sits to something a stakeholder will actually look at, while the earliest, highest-leverage layer (stg_orders, stg_customers) gets strict but simple error-severity structural tests with no thresholds, because a break that early is never something a small threshold should tolerate. Freshness thresholds, from Part 05, would be layered on top of this same reasoning — a tighter warn_after/error_after on paymentsthan on ad_spend, matching exactly the same stakes-based prioritization used for severity above.

Inheriting 40 Failing Tests: Fix, Downgrade, or Delete

Everything so far assumes a project where tests are tiered and owned from the start. In practice, a platform engineer is far more likely to inherit a project that has been accumulating failures for months — a CI dashboard showing 40 tests currently in a failing state, nobody quite sure which ones matter, and a backlog too large to investigate one at a time before doing anything else. This is the single most common real starting point for a testing-strategy cleanup, and it deserves its own worked method rather than "just go fix them," which is not a plan a small team can actually execute against 40 items.

The instinct to open the oldest failure and start debugging is usually the wrong first move. With 40 unowned failures accumulated over months, the highest-value first pass is not fixing anything — it is triage: sorting all 40 into a small number of buckets so effort gets spent on the failures that actually matter, instead of being spent in whatever order the CI dashboard happens to list them.

A practical way to run this triage across 40 items without it becoming its own multi-week project: spend one focused afternoon sorting every failure into a bucket using only the test's name, the model it is attached to, and its DAG tier — not yet its root cause. A relationships test on a staging model with a stable, small failure count for the last six months is very likely a "downgrade to warn" candidate on inspection alone, without opening a single row of failing data. A not_null test on a mart's primary key that has apparently been failing for three weeks is very likely a "fix now" candidate the moment it is spotted, precisely because nobody should have let it sit that long.

models:
  - name: stg_ad_campaigns
    columns:
      - name: campaign_name
        tests:
          - not_null:
              config:
                severity: warn
                # TRIAGE 2026-09: was error, failing ~8% of rows for
                # 4 months on a legacy campaign type nobody backfilled.
                # downgraded rather than fixed -- see PR #2291 for the
                # full triage pass across all 40 inherited failures

  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          # TRIAGE 2026-09: fix-now bucket -- this had been silently
          # failing for 3 weeks due to a duplicate-insert bug in an
          # upstream ETL job, now fixed at the source (see JIRA-4471)

Note what this triage pass deliberately does not do: it does not fix the underlying data problem behind every "fix now" test in the same afternoon. It separates the sorting decision (which bucket does this belong in) from the fixing work (actually resolving the root cause), because conflating the two is exactly what makes a 40-test backlog feel unmanageable in the first place. Sorting 40 items into four buckets is an afternoon's work; fixing the handful that land in "fix now" is real engineering work that deserves its own time, prioritized like any other bug.

Turning Test Results Into Something a Non-Technical Stakeholder Can Actually Read

Part 06 argued that a data team's credibility comes from a track record, not from test count. That track record is invisible to stakeholders by default — nobody outside the data team reads YAML files or a raw dbt build log, and "we have good tests" is not a claim a finance or product stakeholder has any way to verify on their own. Closing that gap means translating test results into a small, regularly-updated summary a non-technical reader can actually use, rather than assuming trust will accumulate on its own.

The three numbers that matter most to a stakeholder are simpler than the full YAML strategy behind them: is the data fresh enough to trust right now, what percentage of tests passed on the most recent run, and — the number that actually matters most — is anything currently broken that this stakeholder specifically depends on. A useful digest reports exactly these three things and nothing more; a stakeholder does not need to know the difference between warn and error severity to understand "3 tests are currently failing on the revenue mart."

{% macro post_test_digest() %}

  {% set query %}
    select
        count(*) as total_tests,
        sum(case when status = 'pass' then 1 else 0 end) as passed,
        sum(case when status = 'fail' and severity = 'error' then 1 else 0 end)
            as critical_failures
    from analytics.dbt_test_results
    where run_date = current_date
  {% endset %}

  {% set results = run_query(query) %}
  {% set row = results.rows[0] %}
  {% set pass_rate = (row['passed'] / row['total_tests'] * 100) | round(1) %}

  {% set message %}
Daily Data Quality Digest -- {{ run_started_at.strftime('%Y-%m-%d') }}
Pass rate: {{ pass_rate }}% ({{ row['passed'] }}/{{ row['total_tests'] }})
Critical failures (error severity): {{ row['critical_failures'] }}
Source freshness: see #data-freshness-alerts for any active warnings
  {% endset %}

  {{ log(message, info=true) }}
  {% do run_query("call analytics.post_to_slack('#data-quality', '" ~ message ~ "')") %}

{% endmacro %}

This is deliberately a run-operation-style macro rather than a hook, scheduled once a day outside of every individual dbt build — the digest is a reporting action, not a side effect that needs to fire on every developer's local test run. It reads from adbt_test_results table populated the same way store_failures populates a failure table in Part 04, aggregated into the handful of numbers a stakeholder actually needs.

Daily Data Quality Digest -- 2026-09-11
Pass rate: 99.1% (438/442)
Critical failures (error severity): 1
Source freshness: see #data-freshness-alerts for any active warnings

A single number like "1 critical failure" posted to a channel finance and product stakeholders already read is worth more, in trust terms, than a perfect internal test suite nobody outside the data team ever hears about. It also creates a useful discipline in the other direction: a data team that has committed to posting this digest daily has a strong incentive to keep the critical-failure count near zero, because the number is now visible to the exact people the team's credibility depends on.

Beyond Generic Tests: Encoding an Actual Business Rule as a First-Class Assertion

unique, not_null, accepted_values, and relationships check structural properties of a column in isolation — is it present, is it one of a fixed list, does it point somewhere real. A large category of genuine data-quality problems has nothing to do with any single column, though: they are relationships between columns, or arithmetic that should always hold across a row, and none of the four built-in generic tests can express that on their own. dbt_utils.expression_is_true is the step up: it lets a test assert any SQL boolean expression against every row, which means a real business rule — not just a structural shape — becomes a first-class, named, tiered, owned test exactly like any other.

models:
  - name: fct_orders
    tests:
      - dbt_utils.expression_is_true:
          expression: "order_total_cents = subtotal_cents + tax_cents + shipping_cents"
          config:
            severity: error
            store_failures: true

# no combination of unique/not_null/accepted_values/relationships can
# check that four separate columns are internally arithmetically
# consistent with each other on every row -- this is a genuine
# business rule about how an order's total is composed, and it
# deserves to be tested as directly and explicitly as a foreign key

This example is deliberately mundane, which is the point: a mismatch here almost always means a real bug in the transformation logic that computes order_total_cents — a rounding error, a discount applied twice, a shipping fee dropped during a refactor — and it is exactly the kind of bug that a column-by-column not_null check would never catch, because every individual column still holds a perfectly valid, non-null number. The problem only exists in the relationship between them.

models:
  - name: fct_subscriptions
    tests:
      - dbt_utils.expression_is_true:
          expression: "case when status = 'cancelled' then cancelled_at is not null else true end"
          config:
            severity: error

# the actual business rule: "a cancelled subscription must have a
# cancellation timestamp." not_null on cancelled_at alone would be
# wrong -- an active subscription is SUPPOSED to have a null
# cancelled_at. the rule is conditional, and expression_is_true is
# what lets that conditional logic be expressed directly.

Reaching for a custom singular test, instead, is still the right call when the underlying SQL needed to express a rule genuinely requires a join, a window function, or an aggregation — expression_is_true is deliberately limited to a single boolean expression evaluated per row of one model, with no JOIN of its own. The practical dividing line: if the rule can be written as one CASE expression or comparison referencing only columns already present on the row, expression_is_true is the more direct match than reaching for a full custom singular test; anything requiring its own subquery or join is still singular-test territory.

Introducing a New Business-Rule Test to an Existing, Already-Dirty Model

A detail worth planning for before adding a dbt_utils.expression_is_true test to a mart that has been in production for a long time: the very first run after adding it is very likely to fail, immediately, on historical rows that predate the business rule being enforced consistently. Treating that first failure as a reason to abandon the test, or as a sign something is wrong with the test itself, is the wrong read — it usually means the test is doing exactly its job, surfacing a real, pre-existing inconsistency the team simply never had a way to see before.

models:
  - name: fct_orders
    tests:
      - dbt_utils.expression_is_true:
          expression: "order_total_cents = subtotal_cents + tax_cents + shipping_cents"
          config:
            severity: warn
            store_failures: true
            # STAGED ROLLOUT 2026-09: added as warn first specifically
            # because this model has 6 years of historical orders and
            # an old pricing engine that predates tax being tracked as
            # its own column. upgrade to error once the historical
            # failure count is understood and either backfilled or
            # explicitly scoped out via a WHERE clause below.

Once the initial warn-severity run reveals how many rows actually fail, and roughly why, there are two honest paths forward, and the choice between them should be made deliberately rather than defaulting to whichever is less work: either the historical data genuinely gets fixed (a backfill correcting the old rows), or the rule is scoped to only apply going forward, with that scoping made explicit in the test itself rather than left as an unexplained gap.

models:
  - name: fct_orders
    tests:
      - dbt_utils.expression_is_true:
          expression: "order_total_cents = subtotal_cents + tax_cents + shipping_cents"
          config:
            severity: error
            store_failures: true
          # scoped via the test's own where clause, not a silent
          # exclusion buried in the model's SELECT
          where: "order_placed_at >= '2024-01-01'"
# using the test's own where config, instead of silently filtering
# inside the model's SELECT, keeps the scope decision visible right
# next to the test itself -- anyone reading schema.yml sees both the
# rule and its documented boundary in the same place

Using the test's own where clause rather than a filter buried inside the model's SELECT keeps this decision honest and visible. A silent WHERE order_placed_at>= '2024-01-01' hidden somewhere in the model file's business logic looks, to the next engineer reading it, like ordinary transformation logic rather than a deliberate scoping decision made specifically to accommodate a known historical gap — exactly the kind of undocumented judgment call that turns into a confusing surprise later.

Five Misconceptions About Testing Strategy at Scale

Three Companies That Learned Testing Strategy the Hard Way

5 Interview Questions — With Complete Answers

Five Mistakes Teams Make Building a Test Strategy at Scale

Testing-at-Scale Errors — And Exactly Why They Happen