In dbt, what SQL does a test compile into, and what makes it pass or fail?

## The core contract Every dbt test — built-in generic, custom generic, or singular — obeys one rule: **the query returns the rows that should not exist.** dbt does not ask you to write an assertion that evaluates true; it asks you to write the search for counterexamples. Empty result set means the data is clean. This inversion trips people up the first time. A `not_null` test is not `where col is not null`; it is: ```sql select * from analytics.stg_orders where order_id is null ``` If that returns 12 rows, you have 12 failures. ## What dbt wraps around it dbt does not just run your SELECT and count client-side. It renders your test SQL as a subquery inside an aggregate, in shape roughly like this: ```sql select count(*) as failures, count(*) != 0 as should_warn, count(*) != 0 as should_error from ( select * from analytics.stg_orders where order_id is null ) dbt_internal_test ``` The `should_warn` and `should_error` expressions are where severity thresholds get compiled in: with `warn_if: '>0'` and `error_if: '>100'`, those booleans become `count(*) > 0` and `count(*) > 100`, and dbt decides warn vs error from the single row that comes back. The number in `failures` is what you see in the run summary — "Failure in test not_null_stg_orders_order_id (models/schema.yml): Got 12 results, configured to fail if != 0". ## fail_calc, limit and where Three test configs change the compiled shape: - `fail_calc` replaces `count(*)` with any aggregate expression. The usual reason is to count something other than rows — for example `count(distinct order_id)`. - `limit` puts a `limit N` on the inner query, so dbt stops after N failing rows. Useful when a test on a huge table would otherwise materialise millions of failures. - `where` injects a predicate into the inner query, scoping the test to a slice such as the last seven days. ## Reading the compiled SQL This is the single most useful debugging move in dbt and interviewers ask for it directly. After `dbt test` (or `dbt compile`), the rendered statement for each test is written to `target/compiled/<project_name>/models/<path>/<test_name>.sql`, and the wrapped version dbt actually executed lands under `target/run/`. Open it, copy it into your warehouse console, strip the outer count, and you are looking at the failing rows with no dbt involved. `dbt test --select <test_name> --debug` also prints the executed SQL to the console and `logs/dbt.log`. The alternative, when you want the rows persisted instead of pasted, is `store_failures: true`, which materialises the inner query's results as a table in an audit schema. ## Test names and uniqueness dbt auto-generates a name for each generic test from the test, the model and the column: `not_null_stg_orders_order_id`, `relationships_stg_orders_customer_id__customer_id__ref_stg_customers_`. Those names get long enough to exceed warehouse identifier limits, which is why dbt hashes them when needed and why you can override with `name:` on the test declaration. The name is also your selector: `dbt test --select not_null_stg_orders_order_id`. ## Where a test sits in the graph A generic test is a node in the dbt DAG whose parents are the models it references. That is what lets `dbt build` run a model, then its tests, then only continue downstream if the tests passed. It also means test selection follows graph operators: `dbt test --select stg_orders+` runs the tests on that model and everything downstream of it, and `dbt build --select state:modified+` in CI runs only what a pull request touched plus its descendants. ## Failure vs error Distinguish two outcomes. A **failure** is the test running successfully and finding bad rows. An **error** is the test query itself blowing up — a missing column, a permissions problem, invalid SQL from a bad macro. Both give a red run, but they mean different things: the first is a data problem, the second is a code problem. dbt reports them distinctly in the summary and in `target/run_results.json`, which is the machine-readable record CI systems parse.