skip to content

A dbt not_null test fails on 3 rows and blocks the nightly build — how do you unblock it?

level: seniorimportance: should knowfreq 46%

answer

  1. failure is not binary
  2. a count can decide warn versus error
  3. you can keep the bad rows somewhere
  4. error_if, warn_if, store_failures

basics

~20 s

Set the test's severity to warn, or give it a threshold with warn_if and error_if so small counts warn while large ones still fail. Turn on store_failures to persist the offending rows for investigation instead of deleting the test.

solid answer

~40 s

Three levers, in order of preference. First, **thresholds**: `severity: error` with `error_if: '>100'` and `warn_if: '>0'` keeps the run green for a handful of rows while still failing on a real breakage — far better than the binary choice. Second, **`store_failures: true`**, which writes the failing rows to a table in an audit schema (`<schema>_dbt_test__audit` by default) so you can query the three offenders tomorrow morning instead of re-deriving them. Third, `severity: warn` outright, if the rule is aspirational rather than enforced. What you should not do is delete the test or add a `where` clause that hides the rows — that converts a known data problem into an invisible one. Pair the downgrade with a ticket and a date, and use `--warn-error` in CI if you want warnings to stay loud there.

code

yaml · 11 lines
yaml
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - not_null:
              config:
                severity: error
                warn_if: ">0"
                error_if: ">100"
                store_failures: true

go deeper

for a junior

Know that a test can be configured to warn instead of error, and that the right first move is to look at the failing rows rather than to remove the test.

for a middle

Explain how severity, error_if and warn_if combine, and what store_failures writes and where, so a failure can be investigated the next morning.

for a senior

Separate the incident response that unblocks tonight's run from the reviewed configuration change that lands tomorrow, and name the upstream root cause a NULL key usually indicates.

for a principal

Own the policy: which tests may be warn-only, who sets thresholds, how CI stays stricter than production, and how you keep a suite whose warnings are actually read rather than ignored.

## The situation The test is doing its job: three rows genuinely have a NULL where the model contract says there should be a key. But it is 03:00, the test is `error` severity, and because the project runs `dbt build`, everything downstream of that model is now skipped. Dashboards are stale for a data problem affecting 3 rows out of 40 million. The interview is really asking: do you know the graduated responses, and do you know which ones are honest? ## Lever 1 — severity thresholds dbt tests are not binary. Three configs work together: ```yaml - name: order_id tests: - not_null: config: severity: error error_if: ">100" warn_if: ">0" ``` `severity` is the ceiling (`error` or `warn`). `error_if` and `warn_if` are expressions compared against the test's failure count — they compile into the `should_error` and `should_warn` booleans in the wrapped test query. With the config above, 3 failures produce a **warning**: the run stays green, downstream models still build, and the warning is visible in the log and in `run_results.json`. 400 failures produce an error and stop the line. That is the shape most mature projects settle on for tests over large, messy source data, because a hard zero-tolerance rule on every column produces so many red runs that people stop reading them. The judgment is choosing the number. It should reflect the point at which the data stops being usable, not the count you happen to have today. Setting `error_if: '>3'` because there are three bad rows right now is threshold-fitting, and it will silently absorb the fourth. ## Lever 2 — store_failures ```yaml config: store_failures: true ``` With this on, dbt materialises the test's failing rows as a table rather than only counting them. By default it lands in a schema suffixed `dbt_test__audit` next to your target schema, named after the test. You can also pass `--store-failures` on the command line for a one-off run. Now the morning triage is `select * from analytics_dbt_test__audit.not_null_stg_orders_order_id` instead of reconstructing the query. Newer dbt versions add `store_failures_as` to control whether that is materialised as a table, a view or ephemeral. This is the single most useful config for operating a test suite, and it costs a small write per failing test per run. Turning it on project-wide for `error`-severity tests is a common default. ## Lever 3 — downgrade to warn `severity: warn` makes the test always report and never fail. Legitimate when the rule is a goal the upstream system does not yet meet — you want visibility without a broken pipeline. It becomes a smell when everything is `warn`, because a warning nobody is paged for is a comment. CI is where you counteract that: `dbt build --warn-error` turns warnings into errors, so a pull request cannot introduce new warning-level problems even though production tolerates the existing ones. ## What not to do - **Deleting the test.** The failure is information; removing it removes the information, not the bad rows. - **A `where` clause that excludes exactly the bad rows.** `where: "order_id is not null"` on a not_null test is a self-cancelling joke that a reviewer will catch, and a subtler `where: "created_at > '2024-01-01'"` used to hide legacy garbage should at least be commented as a deliberate cutoff. - **Rerunning until it passes.** If the test is non-deterministic — say it compares against a source that is still loading — the real bug is ordering, not the test. ## Getting the build moving again tonight Separate the *incident* from the *policy*. Tonight: `dbt build --exclude <the failing test>` or re-run downstream with `dbt run --select <model>+` to unblock the dashboards, having decided the three bad rows are tolerable. Tomorrow: land the threshold config in a reviewed pull request, with `store_failures` on and a ticket against the upstream owner. Interviewers listen for that split — a candidate who only describes the config change has not operated a pipeline at 03:00, and one who only describes the manual override has not fixed anything. ## Talking about the root cause Finally, say out loud what a NULL key means: usually a source-system change, a late-arriving record joined too early, or a left join in the model that should have been an inner join. The test told you the model contract broke; the fix belongs in the model or upstream, and every config above is a way of managing the blast radius while you get there.

  • Where do stored test failures land, and how long do they live?
    By default in a schema next to your target suffixed `dbt_test__audit`, one relation per test named after the test node. They are rebuilt each run, so the table holds the latest run's failures only — if you need history, snapshot or copy them, and set a retention policy since the audit schema otherwise accumulates relations for tests you deleted.
  • How do you keep CI strict while production tolerates existing warnings?
    Run CI with `--warn-error`, which promotes any warning to an error for that invocation. Existing warn-severity tests still keep the nightly production build green, but a pull request that introduces new warning-level failures fails its check. Some teams scope this to modified nodes with `--select state:modified+`.
  • Why is adding a where clause that excludes the failing rows a bad fix?
    It makes the test permanently blind to the exact condition it was written to detect, and unlike a severity downgrade it leaves no visible signal — the run is green and the failure count is zero. If a cutoff is genuinely intended, such as ignoring pre-migration history, it needs a comment and a review, not a quiet filter.

saying these in an interview costs you the question

  • Deletes or comments out the failing test to get green
  • Sets the error threshold to just above today's failure count
  • Adds a where clause that filters out the offending rows
  • Thinks severity warn stops the test from running
  • Reruns the build hoping the failure disappears

context

resolving links…