Module 4: Testing Your Models

Reading `dbt test`'s report

Next →

Description

In the previous six lessons you saw PASS dozens of times, and FAIL a few — always triggered on purpose, always reverted afterward. But dbt test's report has, in total, four possible states, not two, and confusing one for another can lead you to ignore a real problem or chase one that doesn't exist. This lesson stops on the report itself: what each state means, how they relate to each other when part of the project fails, and what to do in the face of each one.

Connection to the module. Every previous lesson in this module declared a test and ran it. This lesson takes a step back and looks at the output more carefully — the same treatment module 3's lesson 7 gave dbt ls, after previous lessons had already used it several times without pausing. It's the last conceptual piece before lesson 8, where you're going to read a report with real failures, mixed with passing tests, over the project's complete suite.

A data test's four states

PASS   -> the test's query returned 0 rows. All good, no action needed.
WARN   -> the query returned 1+ rows, but the test is configured as a "warning"
          (severity: warn). The problem exists, dbt reports it, but it does NOT
          stop anything else in the run.
ERROR  -> the query returned 1+ rows, and the test is configured as an "error"
          (the default, severity: error). This is the "FAIL" you already know
          from previous lessons -- the report labels it ERROR in the final
          summary, even though the individual test's progress line literally
          says "FAIL N."
SKIP   -> the test never got to run, because something it depends on (the
          model it tests, or another resource upstream in the DAG) failed
          first.

Notice something that can be confusing at first glance: during the run, you see FAIL N on an individual test's progress line (1 of 1 FAIL 1 assert_no_negative_revenue, as you already saw in lesson 6) — but the final count summary uses the word ERROR, not FAIL (Done. PASS=0 WARN=0 ERROR=1 SKIP=0). They're the same state, with two different names in two different parts of the same report: FAIL is the verb describing what happened to that particular test ("this test failed"), ERROR is the noun counting how many tests ended in that state, in the aggregate summary.

Worked example: WARN, the state you haven't seen yet

Until now, every data_tests: you declared used the default behavior: if the query returns rows, the test ends in ERROR. But you can ask a test to just warn instead — useful for rules you want to watch without them stopping the rest of the project yet, for example while you investigate whether they're really a problem. Try this, temporarily, on unique in fact_orders.order_id:

# models/marts/_models.yml (fragment, ONLY for this demonstration -- revert afterward)
      - name: order_id
        data_tests:
          - unique:
              config:
                severity: warn
          - not_null

Notice the shape: config: is a different key from arguments: — arguments: passes parameters to the test's logic (as you already saw with values: or to:/field:), config: adjusts the test's own behavior (severity, among other options), no matter what test it is. Now, temporarily add a duplicate, with the same new-file pattern you already used in previous lessons:

-- raw_data/kiosko/orders_2026-08-11.csv (temporary)
order_id,store_id,product_id,quantity,unit_price,order_ts
ORD-1001,S01,P001,1,0.55,2026-08-11T08:00:00

(Notice the order_id: ORD-1001 already exists in orders_2026-08-03.csv, so this new row creates a real duplicate, with no UNION ALL needed in the SQL.)

dbt run --select fact_orders
dbt test --select fact_orders

What to expect.

1 of 7 START test accepted_values_fact_orders_product_id__P001__P002__P003__P004  [RUN]
2 of 7 START test assert_no_negative_revenue ................................... [RUN]
3 of 7 START test is_positive_fact_orders_quantity__True ....................... [RUN]
4 of 7 START test is_positive_fact_orders_unit_price__False .................... [RUN]
1 of 7 PASS accepted_values_fact_orders_product_id__P001__P002__P003__P004 ..... [PASS in 0.06s]
2 of 7 PASS assert_no_negative_revenue ......................................... [PASS in 0.06s]
3 of 7 PASS is_positive_fact_orders_quantity__True ............................. [PASS in 0.06s]
4 of 7 PASS is_positive_fact_orders_unit_price__False .......................... [PASS in 0.06s]
5 of 7 START test not_null_fact_orders_order_id ................................ [RUN]
6 of 7 START test relationships_fact_orders_store_id__store_id__ref_dim_store_ . [RUN]
7 of 7 START test unique_fact_orders_order_id .................................. [RUN]
5 of 7 PASS not_null_fact_orders_order_id ...................................... [PASS in 0.03s]
6 of 7 PASS relationships_fact_orders_store_id__store_id__ref_dim_store_ ....... [PASS in 0.03s]
7 of 7 WARN 1 unique_fact_orders_order_id ...................................... [WARN 1 in 0.03s]

Completed with 1 warning:

[WARNING]: in test unique_fact_orders_order_id (models/marts/_models.yml)
[WARNING]: Got 1 result, configured to warn if != 0

  compiled code at target/compiled/kiosko_analytics/models/marts/_models.yml/unique_fact_orders_order_id.sql

Done. PASS=6 WARN=1 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=7

Read the message carefully: Got 1 result, configured to warn if != 0 — the same message format you already know (Got N result(s), configured to fail if != 0), only now it says warn instead of fail. And the final summary: Completed with 1 warning: instead of Completed with 1 error, ...: — the dbt test command ends with a successful exit code when there's only WARN, unlike ERROR, which does make the command end with a failure exit code (relevant, for example, if you ever chain dbt test inside a script that decides whether to continue based on that code).

Undo both changes before continuing — the temporary severity: warn and the file with the duplicate:

rm raw_data/kiosko/orders_2026-08-11.csv
dbt run --select fact_orders

And revert models/marts/_models.yml to the version without config: severity: warn (the one from the end of lesson 5). Confirm with dbt test that you're back to PASS=15.

Worked example (continued): ERROR propagating as SKIP

You already saw SKIP in module 3 — when stg_orders failed, all of fact_orders showed up as SKIP during dbt run. The same mechanism applies to tests, and it's worth seeing it with dbt build — the command, not formally introduced yet in this guide, that runs models and tests together, respecting the DAG order between them, module 8's same central command. Temporarily break stg_orders.sql, with a real error inside the SELECT (not a badly written source(), which stops the whole command before it starts; an error that does let the rest of the DAG run):

-- models/staging/kiosko/stg_orders.sql (temporary, with a column that doesn't exist)
select
    order_id,
    store_id,
    product_id,
    cast(quantity as integer) as quantity,
    cast(unit_price as decimal(10, 2)) as unit_price,
    cast(order_ts as timestamp) as order_ts,
    this_column_does_not_exist
from {{ source('kiosko_raw', 'orders') }}
dbt build --select stg_orders+

What to expect.

Found 7 models, 15 data tests, 4 sources, 501 macros

Concurrency: 4 threads (target='dev')

1 of 11 START sql view model main.stg_orders ................................... [RUN]
1 of 11 ERROR creating sql view model main.stg_orders .......................... [ERROR in 0.04s]
2 of 11 SKIP test not_null_stg_orders_order_id ................................. [SKIP]
3 of 11 SKIP test unique_stg_orders_order_id ................................... [SKIP]
4 of 11 SKIP relation main.fact_orders ......................................... [SKIP]
5 of 11 SKIP test accepted_values_fact_orders_product_id__P001__P002__P003__P004  [SKIP]
6 of 11 SKIP test assert_no_negative_revenue ................................... [SKIP]
7 of 11 SKIP test is_positive_fact_orders_quantity__True ....................... [SKIP]
8 of 11 SKIP test is_positive_fact_orders_unit_price__False .................... [SKIP]
9 of 11 SKIP test not_null_fact_orders_order_id ................................ [SKIP]
10 of 11 SKIP test relationships_fact_orders_store_id__store_id__ref_dim_store_  [SKIP]
11 of 11 SKIP test unique_fact_orders_order_id .................................. [SKIP]

Finished running 1 table model, 9 data tests, 1 view model in 0 hours 0 minutes and 0.11 seconds (0.11s).

Completed with 1 error, 0 partial successes, and 0 warnings:

[ERROR]: in model stg_orders (models/staging/kiosko/stg_orders.sql)
  Runtime Error in model stg_orders (models/staging/kiosko/stg_orders.sql)
  Binder Error: Referenced column "this_column_does_not_exist" not found in FROM clause!
  Candidate bindings: "store_id", "product_id", "unit_price"

  LINE 12:     this_column_does_not_exist
               ^

  compiled code at target/compiled/kiosko_analytics/models/staging/kiosko/stg_orders.sql

Done. PASS=0 WARN=0 ERROR=1 SKIP=10 NO-OP=0 REUSED=0 TOTAL=11

1 ERROR, 10 SKIP — read the complete cascade, because it tells an exact story. stg_orders fails to build (ERROR), with a DuckDB Binder Error — the this_column_does_not_exist column doesn't exist in source('kiosko_raw', 'orders')'s result, and DuckDB even suggests which columns do exist (Candidate bindings). From there, everything that depends on stg_orders, directly or indirectly, gets marked SKIP, with dbt not even attempting it: the two tests declared on stg_orders (not_null, unique), fact_orders itself (marked SKIP relation, because it depends on stg_orders via ref()), and the seven tests declared on fact_orders — the six column ones plus the singular one — because all of them ultimately depend on fact_orders existing.

flowchart TD
    A["stg_orders\nERROR (nonexistent column)"] --> B["not_null_stg_orders_order_id: SKIP"]
    A --> C["unique_stg_orders_order_id: SKIP"]
    A --> D["fact_orders: SKIP relation"]
    D --> E["6 column tests\non fact_orders: SKIP"]
    D --> F["assert_no_negative_revenue: SKIP"]

dim_store, dim_date, stg_events, stg_products, and stg_stores — which don't show up at all in this output, because --select stg_orders+ only includes stg_orders and everything that depends on it (the + operator, which you already know from module 3) — would keep building with no problem at all if you ran dbt build with no such filter: SKIP never means the whole project stopped, it means the specific part of the DAG that depends on the broken resource couldn't continue, while any independent branch runs its normal course.

Undo the change before continuing:

# restore stg_orders.sql to the version without the made-up column (lesson 3 onward)
dbt build

What to expect. Done. PASS=22 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=22 — 7 models plus 15 data tests, all green again.

Going deeper: --store-failures, to inspect broken rows without repeating the query by hand

Every time you triggered a FAIL in this module, you copied the test's SELECT by hand, with dbt show --inline, to see the exact rows that failed. dbt has a way to save those rows automatically, with no need to repeat the query:

dbt test --select assert_no_negative_revenue --store-failures

When a test fails with this flag, dbt creates (or replaces) a table with the exact rows that violated the rule, in a separate schema (main_dbt_test__audit, prefixed with the project's main schema, main), and the error message tells you exactly where to query it:

    See test failures:
  --------------------------------------------------------------------------
  select * from "kiosko"."main_dbt_test__audit"."assert_no_negative_revenue"
  --------------------------------------------------------------------------

This is more useful the bigger the project gets: instead of rebuilding the test's query from memory (trivial in a one-line file like assert_no_negative_revenue.sql, but much less trivial in a generic test with several CTEs), you simply query the table dbt already left ready. This guide doesn't use --store-failures as part of the lessons' normal flow — for a project Kiosko's size, dbt show --inline with the same query is enough and leaves no extra tables in the warehouse — but it's worth knowing for the day you work on a real, bigger project.

Common mistakes

Treating any WARN as if it were safe to ignore forever. What happens: someone configures a test with severity: warn "so it doesn't break the pipeline," and never comes back to review that warning. Why it happens: since WARN stops nothing and doesn't fail the command, it's easy for it to get lost among the rest of the terminal output. How to spot it: if your project accumulates WARNs no one has reviewed in weeks, check whether those tests should instead be ERROR — the rule they describe probably does matter — or should just be deleted, because no one is actually watching them. How to fix it: use severity: warn with a temporary, explicit purpose — for example, while you confirm whether a new rule is real before it blocks the project — not as a permanent way to silence a problem.

Confusing SKIP with "this test passed." What happens: someone sees a long list of SKIP in the output and, seeing no ERROR on those specific lines, assumes everything's fine. Why it happens: SKIP doesn't have ERROR's alarming red color, so it can go unnoticed in a long output. How to spot it: always review the final summary (Done. PASS=X WARN=X ERROR=X SKIP=X) before considering any run good — any nonzero number in SKIP means there are tests that weren't evaluated at all, not that they passed. How to fix it: SKIP always means "look for the real ERROR upstream in the DAG" — never treat it as a sign that part of the project is fine, exactly the same lesson module 3 already left with dbt run.

Not checking the final summary, and only looking at the progress lines. What happens: someone watches the list of START/PASS/FAIL lines as it runs, but doesn't wait for or read the final block (Completed successfully or Completed with N errors..., and the Done. PASS=... line). Why it happens: in a terminal with a lot of scroll, the first lines disappear from view before the command finishes. How to spot it: if you're ever unsure whether a run finished well, always look for the last line (Done. PASS=X WARN=X ERROR=X SKIP=X TOTAL=X) — it's the complete, reliable summary, no matter how many progress lines you saw go by before it. How to fix it: get used to always reading that last line before deciding whether a module (or any change) is closed out — the same habit you already built with PASS=N in dbt run, since module 1.

Exercises

Exercise 1 — Predict the summary before running it. Without running anything yet: if you broke dim_date.sql (for example, with a made-up column, just like you did with stg_orders in this lesson) and ran dbt build, which resources would you expect to see in SKIP? Use the DAG you already know from module 3 (fact_orders depends on stg_orders, dim_store, and dim_date).

See solution

dim_date would end in ERROR. Everything that depends on dim_date — which, in this project, is only fact_orders — would end in SKIP, along with the seven tests declared on fact_orders (the six column ones plus assert_no_negative_revenue). Unlike when you broke stg_orders, this time dim_store would not show up in SKIP — dim_store doesn't depend on dim_date at all, so it would build with no problem — and neither would not_null_stg_orders_order_id or unique_stg_orders_order_id show up, because those two tests live on stg_orders, which stays intact.

Exercise 2 — Verify your prediction. Temporarily break dim_date.sql (add a nonexistent column at the end of the SELECT, as in this lesson's worked example) and run dbt build --select dim_date+. Compare the real result against your exercise 1 prediction.

See solution 
-- models/marts/dim_date.sql (temporary, with a column that doesn't exist -- do NOT use this version)
select
    cast(strftime(calendar_date, '%Y%m%d') as integer) as date_key,
    calendar_date,
    strftime(calendar_date, '%A') as day_of_week,
    extract(month from calendar_date) as month,
    extract(quarter from calendar_date) as quarter,
    extract(year from calendar_date) as year,
    extract(dow from calendar_date) in (0, 6) as is_weekend,
    this_column_does_not_exist
from date_spine

(Note: this broken version deliberately omits the real model's with recursive date_spine as (...) for brevity — in your real file, add the extra column at the end of the final SELECT, leaving the CTE intact.)

dbt build --select dim_date+

The result confirms the prediction: dim_date in ERROR, fact_orders in SKIP relation, and fact_orders's seven tests in SKIP — neither dim_store nor stg_orders's tests show up in this filtered output, because neither depends on dim_date. Restore dim_date.sql to its original version before continuing, and confirm with dbt build that the complete project is back to PASS=22.

Exercise 3 — Argue why SKIP is preferable to dbt just trying to run everything anyway. In 2-3 sentences, explain what problem dbt avoids by marking as SKIP the tests that depend on a broken model, instead of trying to run them anyway against whatever data was left from an earlier run.

See solution

If dbt tried to run not_null_fact_orders_order_id after fact_orders failed to rebuild, that test would run against an old version of the table — the one from the last successful run — and a PASS under those conditions would be misleading: it would tell you "this is fine" about data that no longer reflects the project's current code state. SKIP is the honest way to say "I don't know if this is fine, because I couldn't verify it with the correct data" — it prevents a false PASS from giving you a false sense of security about a problem that, in reality, is still unresolved somewhere else in the DAG.

Summary and next step

In this lesson you learned to read dbt test's complete report (and dbt build's, which combines models and tests): the four possible states — PASS, WARN, ERROR/FAIL, SKIP — how they're configured (severity: warn versus the error default), and how an ERROR propagates in a SKIP cascade to everything that depends on the broken resource, with no stopping of the DAG's independent branches. You saw, with a real demonstration, that the final number on the Done. PASS=X WARN=X ERROR=X SKIP=X line is the only reliable source of truth about a run's complete result.

Before moving on you should be able to: explain the difference between WARN and ERROR, and when you'd choose the former over the latter; and predict, without running anything, which resources would end up in SKIP if a specific model in the project failed to build.

Lesson 8 closes the module with the complete project: you're going to add the batch of orders with three deliberately broken rows that data-engineering-foundations-guide and data-modeling-for-analytics-guide already used, run the 15-test suite over that batch, and read the real FAIL report it produces — applying, with concrete data, exactly what this lesson taught you to interpret.

Resources

← PreviousSingular tests for business rulesNext →Mini-project: Kiosko's test suite