Tests and Documentation

## What a dbt test actually is A dbt data test is a SELECT statement that returns the rows you do **not** want to exist. dbt runs that query against the warehouse: zero rows returned means pass, one or more rows means fail. A *generic* test is that query wrapped in a parameterised Jinja macro, so the same logic can be pointed at any model and any column from YAML instead of being rewritten by hand. dbt ships four generic tests in the core package: `unique`, `not_null`, `accepted_values` and `relationships`. ## Where they are declared They live in a YAML file inside your `models/` directory (conventionally `schema.yml`, but the filename is free). Nesting decides the target: a test under a column applies to that column, a test under the model applies to the model as a whole. ```yaml models: - name: stg_orders columns: - name: order_id tests: - unique - not_null - name: status tests: - accepted_values: values: ['placed', 'shipped', 'returned'] - name: customer_id tests: - relationships: to: ref('stg_customers') field: customer_id ``` dbt 1.8 introduced unit tests and added `data_tests:` as the preferred key for these data tests; `tests:` still works, so you will see both spellings in real projects. ## unique Fails when a value appears in the column more than once. The compiled SQL groups by the column and keeps groups with `count(*) > 1`. NULLs are excluded from the comparison — a column full of NULLs passes `unique`, which is exactly why `unique` and `not_null` are almost always declared as a pair on a primary key. ## not_null The simplest of the four: it selects rows `where <column> is null`. Note that it tests for SQL NULL only. Empty strings, the literal text `'NULL'`, and placeholder values like `-1` all pass it happily; catching those needs `accepted_values` or a custom test. ## accepted_values Takes a `values:` list and fails on any value outside it. By default the values are quoted as strings; set `quote: false` for numeric or boolean columns so the generated SQL compares them without quotes. The gotcha: the compiled predicate is a `not in (...)` filter, and in SQL `NULL not in (...)` is NULL rather than true, so NULL rows are **not** returned and the test passes on them. If NULL is unacceptable in that column, add `not_null` alongside it. ## relationships The referential-integrity check. `to:` names the parent relation — normally `ref('some_model')` or `source('raw', 'some_table')` — and `field:` names the parent column. The compiled query left-joins the child column to the parent column and returns child rows whose parent match is missing, ignoring NULL child values. This matters because cloud warehouses typically declare foreign keys as unenforced metadata at best, so the only thing actually verifying that every `customer_id` on an order exists is this test running after the load. ## What these tests are not They are not warehouse constraints. Nothing is enforced at write time; the model is built first, and the test is a separate query that runs afterwards and reports. A failing test does not delete, quarantine or roll back rows — the bad data is sitting in the table you just published. The consequence you actually get is a non-zero exit code and, if you used `dbt build`, downstream models being skipped. ## Configs worth knowing Every test accepts a `config:` block. `where:` injects a filter so the test only inspects a slice (useful on huge tables: check the last seven days rather than five years). `limit:` caps how many failing rows come back. `severity: warn` downgrades a failure to a warning. `store_failures: true` writes the failing rows to a table so you can inspect them. ```yaml - name: order_id tests: - unique: config: where: "order_date >= current_date - 7" ``` ## How you run them `dbt test` runs every test in the project; `dbt test --select stg_orders` runs the ones attached to that model; `dbt build` interleaves each model with its own tests in DAG order. Beyond these four, the `dbt_utils` package adds widely used ones such as `unique_combination_of_columns` and `expression_is_true`, and you can author your own generic tests when the built-ins do not fit.