What a data test actually checks

Description

Since module 2 you've been writing blocks like this, without ever having seen what's behind them:

columns:
  - name: order_id
    data_tests:
      - unique
      - not_null

And you run dbt test, and you see PASS. But unique and not_null aren't magic words dbt understands by instinct — they're the name of two Jinja macros, already written inside dbt-core, each of which compiles to a real SELECT, runs against your warehouse, and produces a result dbt interprets with a very simple rule: if that query returns zero rows, the test passes; if it returns one or more, the test fails, and those rows are, literally, the evidence of the problem.

This lesson opens that box. Before learning to declare the four generic tests in depth (lessons 3 and 4), to write one of your own (lesson 5), or to hand-write a complete singular test (lesson 6), you need to see, with your own eyes and over a test you already declared in module 2, what SQL actually runs behind the word unique.

Connection to the module. Lesson 1 gave you the complete analogy — quality control, generic rules versus specific alarms — and the map of what you're going to build. This lesson is the technical foundation the next five lessons rest on: without understanding that a test is, at bottom, "a SELECT that counts problem rows," declaring accepted_values or writing is_positive would feel like memorizing syntax instead of applying a mechanism you already understand.

An analogy: the question you ask the inspector, not the answer you memorize

When someone tells you "this phone passed quality control," how much real information are they giving you? Not much, yet — it depends completely on what question the inspector asked the phone. "Does it turn on?" is one question. "Does the battery last more than 8 hours?" is another, completely different one, even though both use the same generic phrase ("quality control"). A test that only says "PASS" without you knowing what question it answered is, at best, an empty promise.

A dbt data test works the same way: the name (unique, not_null) is a convenient label, but the real question — the one actually asked of the warehouse — is the SELECT behind that label. This lesson shows you that question, literally, for the two tests you already declared in module 2 — and once you see it once, you're going to be able to predict, with no guessing, what's going to happen when you declare the other two generic tests in lesson 4, or when you write your own in lesson 5.

Worked example: uncovering unique_stg_orders_order_id

Remember the _models.yml that already exists in models/staging/kiosko/ since module 2:

# models/staging/kiosko/_models.yml (already exists, no changes)
models:
  - name: stg_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null

When dbt parses this YAML, it assembles a test node named unique_stg_orders_order_id — the same name you already saw in dbt test's output since module 2. dbt compile lets you see the exact SQL that name represents, without running it:

dbt compile --select unique_stg_orders_order_id

This generates a file inside target/compiled/. Open it:

cat target/compiled/kiosko_analytics/models/staging/kiosko/_models.yml/unique_stg_orders_order_id.sql

What to expect.

select
    order_id as unique_field,
    count(*) as n_records

from "kiosko"."main"."stg_orders"
where order_id is not null
group by order_id
having count(*) > 1

Read it for what it is: an ordinary SQL query, with no Jinja, no magic at all. It groups stg_orders by order_id, counts how many times each value shows up, and keeps only the groups that show up more than once (having count(*) > 1). If order_id really were unique, this query would return zero rows — no group would have more than one member. The where order_id is not null clause exists so a null value, which technically "doesn't repeat" (each null is indistinguishable from the next in a group by, per the SQL standard), doesn't contaminate the count — that responsibility is deliberately left to the separate not_null test.

Now the second one:

dbt compile --select not_null_stg_orders_order_id
cat target/compiled/kiosko_analytics/models/staging/kiosko/_models.yml/not_null_stg_orders_order_id.sql

What to expect.

select order_id
from "kiosko"."main"."stg_orders"
where order_id is null

Even simpler: it literally selects any row where order_id is null. If stg_orders has no null value in that column at all — which you already confirmed is the case, since module 2 — this query returns nothing, and the test passes.

The universal rule: zero rows is success, one or more is failure

Notice something both queries share, and that's the complete rule that governs every dbt data test, with no exception — the four out-of-the-box generic ones, the one you're going to write yourself in lesson 5, and lesson 6's singular one:

The test's SELECT returns the rows that VIOLATE the rule.
0 rows returned  -> PASS
1+ rows returned -> FAIL, and the row count is the failure count

There's no "expected results" table anywhere, no comparison against a fixed value — dbt simply counts how many rows the query returned. This is the reason, by the way, why the dbt test output you already saw in module 2 — Got 1 result, configured to fail if != 0 — has that exact shape: "1 result" is literally the number of rows the query above returned, and "configured to fail if != 0" is the universal rule, said in words.

flowchart LR
    A["data_tests:\n- unique"] --> B["macro unique(model, column_name)"]
    B --> C["compiled SELECT\n(group by + having count(*) > 1)"]
    C --> D{"How many rows\ndoes it return?"}
    D -->|"0 rows"| E["PASS"]
    D -->|"1+ rows"| F["FAIL N\n(N = row count)"]

This also explains something you may have wondered in module 2 without saying it out loud: why does the error message say "Got 1 result" and not "found 1 duplicate order_id"? Because dbt doesn't know, and doesn't need to know, what the query's result means — it only knows how to count it. The meaning ("this is a duplicate order_id") lives in the test's name and in the SELECT's logic, not in any special message dbt generates separately.

Going deeper: why dbt compile runs nothing

dbt compile does exactly what you already did with dbt compile --inline in module 2 (lesson 4), now applied to a test node instead of a standalone query: it takes the test's Jinja — including the call to the unique macro — and turns it into final SQL, with no execution connection ever opened against the warehouse. You can confirm this by comparing timings: dbt compile --select unique_stg_orders_order_id is practically instant, no matter how many rows stg_orders has, because it never even read a single real row — it only translated Jinja to SQL, the same distinction module 2 already stressed between "declaring" and "running."

This distinction matters because it gives you a new diagnostic tool: if a test ever fails with an error you don't understand, dbt compile --select <test_name> and then reading the file in target/compiled/ shows you exactly what SQL query ran — with no need to guess what a macro does from its name. You're going to use this same technique in lesson 5, to confirm your own test_is_positive macro compiles to the SQL you expect before running it.

Common mistakes

Thinking unique compares against some list of allowed values. What happens: someone, seeing the name unique, imagines dbt stores somewhere a list of values "already seen" and compares every new row against that list. Why it happens: it's easy to picture a test as a process with memory, instead of a single stateless SQL query. How to spot it: this lesson's compiled SQL has no list, no state saved between runs — it's an ordinary GROUP BY/HAVING, evaluated once over the data that exists at that moment. How to fix it: remember the universal rule — every dbt test is a fresh query, with no memory of previous runs; if order_id was unique yesterday and today someone inserted a duplicate, today's test catches it with no need to know anything about yesterday's run.

Confusing the number in FAIL N with the table's total row count. What happens: someone sees FAIL 1 and assumes something went wrong with the whole table, or that only "1 good row" is left. Why it happens: FAIL 1 reads, at first glance, like a fraction or a percentage. How to spot it: go back over module 2's lesson 7 exercise 2 — stg_orders had 41 rows total when the test failed with Got 1 result, not 1 row total. How to fix it: the number in FAIL N is always how many rows the test's SELECT returned — that is, how many rows violate the rule, not how many rows the table has or what percentage is wrong. To know that last part, you'd have to query the complete table separately, something no test does for you automatically.

Running dbt compile expecting to see real data rows. What happens: someone runs dbt compile --select unique_stg_orders_order_id and expects to see, in the terminal, whether the test passes or fails. Why it happens: dbt compile and dbt test start with the same conceptual verb ("process a test"), so it's easy to expect the same kind of output. How to spot it: dbt compile never prints PASS or FAIL at all — it only confirms the Jinja compiled with no syntax errors, and writes the resulting SQL to a file. How to fix it: use dbt compile when you want to read what query a test is going to run, and dbt test (or dbt show --inline with that same query, pasted by hand) when you want to run it and see a real result against the data.

Exercises

Exercise 1 — Compile stg_stores's test. Using this lesson's same pattern, run dbt compile --select unique_stg_stores_store_id and read the resulting compiled file. How is it similar to unique_stg_orders_order_id, and how does it differ?

dbt compile --select unique_stg_stores_store_id
cat target/compiled/kiosko_analytics/models/staging/kiosko/_models.yml/unique_stg_stores_store_id.sql

Exercise 2 — Predict the result before running it. Without running anything yet, predict: if stg_products had, by accident, two rows with product_id = 'P001', how many rows would unique_stg_products_product_id's compiled SELECT return? Then, verify your prediction with dbt show --inline, using the same compiled query but pointed at a temporary version of stg_products with a duplicate (you can simulate it with a UNION ALL, without modifying the real file).

dbt show --inline "
  select product_id as unique_field, count(*) as n_records
  from (select * from {{ ref('stg_products') }} union all select * from {{ ref('stg_products') }} where product_id = 'P001')
  where product_id is not null
  group by product_id
  having count(*) > 1
"

Exercise 3 — Argue why the "zero rows is success" rule is simpler than it seems. In 2-3 sentences, explain why the fact that every dbt data test — the four generic ones, the one you're going to write yourself, and any singular test — shares exactly the same success/failure rule is a design advantage, not a limitation.

Summary and next step

In this lesson you opened the box behind two tests you'd already declared without seeing them: unique compiles to a GROUP BY/HAVING count(*) > 1, not_null compiles to a WHERE column IS NULL, and both — like any dbt data test — follow the same universal rule: zero rows returned is success, one or more is failure, and the row count is the failure count. You learned to use dbt compile --select <test_name> to read the real SQL behind any test, without running it.

Before moving on you should be able to: explain, from memory, a data test's universal success/failure rule; and describe, in your own words, the difference between dbt compile and dbt test over the same node.

Lessons 3 and 4 come back to the four out-of-the-box generic tests, this time applied in depth over fact_orders — the star schema's central mart, which up to now has no data_tests of its own — starting with unique and not_null on order_id, the first time those two tests protect the marts layer, not just staging.

Resources

• dbt Developer Hub — "Add data tests to your DAG," which documents the convention that a data test "fails if the query returns one or more rows" — this lesson's central universal rule. docs.getdbt.com/docs/build/data-tests. In English.
• dbt Developer Hub — "dbt compile," the reference for the command used in this lesson to inspect the SQL behind a test node without running it. docs.getdbt.com/reference/commands/compile. In English.
• dbt Developer Hub — dbt-core's global macros repository (global_project), where the source definitions of unique, not_null, accepted_values, and relationships this lesson takes apart live. github.com/dbt-labs/dbt-core. In English.