What you will be able to do
- Explain how a dbt data test turns an assumption into a query that returns failing rows
- Declare unique, not_null, accepted_values and relationships tests on source tables and model columns
- Choose a selector that runs only the source tests you need
- Account for the NULL-handling of relationships and accepted_values, and test a multi-column grain
- Tune a test with arguments, config, a custom name and failure thresholds without causing duplicate-test errors
Key concept
Data test as a failing-rows query — A data test is a select statement that looks for rows that break an assumption about a model or source. It passes only when the query returns no rows.
1.What a data test asserts
Every dbt project relies on assumptions that are rarely written down: order_id never repeats, status holds one of four known values, and every customer_id on an order belongs to a real customer. A data test writes one of these assumptions as a query that dbt runs for you. You can attach data tests to models, sources, seeds and snapshots. When you run dbt test, dbt reports whether each one passed or failed.
The query looks for evidence against the assumption. If you assert that a column is unique, the test selects duplicates. If you assert that a column is never null, the test selects nulls. Zero rows back means the assumption held, and any row back is a failure. Keep this inversion in mind when you read compiled test SQL: it describes the bad case, not the good one. Each returned row is one failure, and the selected columns are what you see when you debug it.
Two kinds of assumption are worth stating in every project. The first is the grain of each model: the primary key should be both unique and not_null. The second covers inputs you don't control. Suppose you believe payments can only use three payment methods. Test that belief regularly, because a new method arriving upstream may introduce logic errors in SQL that quietly assumed only three existed.
Four built-in generic tests express most of these assumptions, and any assertion you can write as a select query can become a test. The different test types (singular, custom generic and unit tests) have [their own lesson on test types](lesson:5.1). This page covers which assumptions to state, where to declare them, and how to tune them.
Checkpoint 1 of 8· Check yourself
A not_null test on orders.order_id runs, and its query returns 3 rows. What does dbt report?
Data tests select failing records. Only zero rows is a pass, so 3 returned rows are 3 nulls in order_id.
“they are select statements that seek to grab "failing" records, ones that disprove your assertion.”Source: docs.getdbt.com
Checkpoint 2 of 8· Exam question
The `raw_orders` source table in the `jaffle_shop` schema has a `customer_id` column that must always correspond to an existing `id` value in the `customers` model. Which approach best enforces this assumption using dbt's built-in testing framework?
Correct answer: A — Add a `relationships` data test on the `customer_id` column in the source's YAML, with `to: ref('customers')` and `field: id`, so dbt flags any source rows whose `customer_id` has no matching `customers.id`.
- A. The `relationships` generic test is built exactly for referential-integrity assumptions: it selects child rows whose foreign key value cannot be found in the referenced parent column and returns them as failures. Pointing `to` at `ref('customers')` and `field` at `id` directly tests the stated assumption without any manual SQL.
- B. `not_null` only checks that a value exists in the column; it never verifies that the value corresponds to a real row elsewhere. A `customer_id` can be non-null and still reference a customer that was deleted or never existed, so this misses the actual assumption being tested.
- C. Comparing row counts is a weak proxy for referential integrity: the counts could match by coincidence while individual `customer_id` values still fail to reference a real customer, and it requires hand-written SQL that `relationships` already provides as a maintained built-in.
- D. `accepted_values` checks membership in a static, hand-maintained list, which is the wrong tool for a relationship that changes as customers are added; it would require constant manual updates and does not express a foreign-key assumption.
Sources1
2.Declaring assumptions on sources
Sources name the raw tables that your extract-and-load tools write into the warehouse. Declaring a table as a source does more than give you the source() function and lineage. It also gives you a place to test your assumptions about that raw data. You attach tests to source columns with the same data_tests key you use on models, under sources: → tables: → columns:.
sources:
- name: jaffle_shop
description: This is a replica of the Postgres database used by our app
tables:
- name: orders
database: raw
description: >
One record per order. Includes cancelled and deleted orders.
columns:
- name: id
description: Primary key of the orders table
data_tests:
- unique
- not_nullIn mature projects, data-integrity checks belong on the sources, not on the models built from them. A duplicate id in raw.jaffle_shop.orders is a fact about the loaded data. Testing it on the source reports the failure against the table the problem actually lives in, and every model that selects from that source inherits the guarantee.
Source assumptions also have their own timing. Run tests while you write new code, to confirm a SQL change hasn't broken an existing model. Run them again whenever production transformations run, because the source data can change underneath code that hasn't changed at all. How these runs fit into dbt build and CI is covered under [testing steps in the workflow](lesson:5.3). Checking how recently a source was loaded is a separate mechanism, [source freshness](lesson:6.2), not a data test.
| Command | What it tests |
|---|---|
| dbt test --select "source:*" | Data tests on all sources |
| dbt test --select source:jaffle_shop | Data tests on one source and all of its tables |
| dbt test --select source:jaffle_shop.orders | Data tests on one source table only |
Checkpoint 3 of 8· Check yourself
A loader team says the orders table in the jaffle_shop source was reloaded overnight. You want to re-check only that table's assumptions. Which command fits?
source:jaffle_shop.orders limits the run to tests on that one source table. source:* tests every source, and dbt run with + builds downstream models without running the source tests.
“to run data tests on one source table only:”Source: docs.getdbt.com
Checkpoint 4 of 8· Exam question
A source test is defined as follows: ```yaml sources: - name: jaffle_shop tables: - name: raw_customers columns: - name: email data_tests: - not_null: config: severity: error ``` The team wants failures on this test to be logged for visibility but never block the CI build. Which single change to the snippet accomplishes this?
Correct answer: A — Change `severity: error` to `severity: warn`, so the test still runs and reports any failing rows, but a failure no longer causes the invocation to exit with a build-blocking status.
- A. `severity` is exactly the config that controls this behavior: `warn` keeps the test executing and reporting failing rows in the logs, while dbt still treats the overall invocation as successful instead of exiting non-zero. This satisfies both requirements without removing the check.
- B. Removing the config block does not change severity; the default severity for a data test is `error`, so failures would still block the build. There is no dbt behavior where an unconfigured test automatically becomes non-blocking.
- C. Test severity is unrelated to whether a test is declared at the table or column level in the YAML; both levels default to `error` and both can be overridden with a `severity` config. Relocating the test changes nothing about its blocking behavior.
- D. `accepted_values` is a generic test with the same default `error` severity as `not_null`; nothing about the test type itself makes it informational. Severity always has to be configured explicitly regardless of which built-in test is used.
3.Referential integrity, accepted values and grain
The relationships test asserts referential integrity: every value in a child column must exist in a parent. It takes two arguments, to for the parent relation and field for the parent column. Because to accepts a relation, the parent can be a model (ref('customers')) or a raw table (source('jaffle_shop', 'customers')). That lets you check foreign keys between two source tables before any model is built.
The test ignores NULLs, and that's an easy assumption to leave half-tested. An order with no customer at all passes relationships. If an order must always have a customer, add not_null to the same column. The two tests together say "always present, and always valid".
Checkpoint 5 of 8· Fill the gap
Which generic test completes this assertion that every order's customer exists?
models:
- name: orders
columns:
- name: customer_id
data_tests:
- ? :
arguments: # available in v1.10.5 and higher. Older versions can set the <argument_name> as the top-level property.
to: ref('customers')
field: idto and field are the arguments of relationships, which checks that each customer_id exists as an id in customers.
accepted_values also checks only the non-null values. It fails if any non-null value falls outside the supplied list. By default it single-quotes that list in the query, which suits strings like 'placed' and 'shipped'. For an integer or boolean column, set quote: false, otherwise the test compares numbers against quoted strings.
- name: status_id
data_tests:
- accepted_values:
arguments:
values: [1, 2, 3, 4]
quote: falseA grain made of more than one column can't sit under a single column. Take an orders table that holds records from several countries, where order_id repeats across countries and only the pair of country_code and order_id is unique. There are three ways to test it:
- Build a surrogate_key column in the model and put unique on it.
- Attach unique at model level with column_name set to an expression such as (country_code || '-' || order_id).
- Use dbt_utils.unique_combination_of_columns from the dbt-utils package.
models:
- name: orders
data_tests:
- dbt_utils.unique_combination_of_columns:
arguments: # available in v1.10.5 and higher. Older versions can set the <argument_name> as the top-level property.
combination_of_columns:
- country_code
- order_id| Test | Assumption it states | Arguments |
|---|---|---|
| unique | No duplicate values in the column | None |
| not_null | No null values in the column | None |
| accepted_values | Every non-null value is in a supplied list | values, optional quote |
| relationships | Every non-null value exists in a parent relation | to, field |
Checkpoint 6 of 8· Match them up
Match each assumption to the test that states it
Tap a term, then the definition that fits it.
relationships skips NULLs, so rejecting missing customers needs not_null. A grain spanning two columns needs a model-level test such as unique_combination_of_columns.
“Use the not_null test separately if NULL values should cause failures.”Source: docs.getdbt.com
Checkpoint 7 of 8· Exam question
A source table `raw_events` is known to have occasional late-arriving duplicate rows that a `unique` test on its `event_id` column would flag. The team wants to keep the test in place, record the offending rows for investigation, but not fail the CI pipeline outright. Which two configurations, applied together to the `unique` test, achieve this?(Select 2)
Correct answers: A, B — `severity: warn`, so the test still runs on every invocation and reports failures without stopping the `dbt build` invocation from completing successfully.; `store_failures: true`, so the specific duplicate rows are persisted to an audit table in the `dbt_test__audit` schema for the team to review later.
- A. Setting severity to `warn` is what stops a failing `unique` test from blocking the pipeline: the test still executes and its failure is reported, but the overall invocation does not exit with a build-blocking error status.
- B. `store_failures: true` is what satisfies the requirement to record the offending duplicate rows: dbt materializes the failing rows into an audit table so the team can query them and investigate the late-arriving duplicates afterward.
- C. Setting `limit` to zero does not disable or soften the test; it only caps how many failing rows are returned in the results, so duplicates would still be detected and, without a severity change, would still fail the build.
- D. Setting `store_failures_as` to `ephemeral` defeats the goal of persisting failures for review, since an ephemeral relation is never materialized as a queryable table and disappears after compilation.
- E. A `where` clause that filters out all rows disables the check rather than preserving it; the team would no longer be testing for duplicates at all, which contradicts the requirement to keep the test in place.
- F. Setting severity to `error` is the opposite of what is needed here, since it guarantees that any duplicate found will fail the build and block the CI pipeline the team wants to keep passing.
Sources3
4.Tuning how an assumption is checked
Not every assumption holds over all of history, and not every breach should stop a run. A generic test has two separate blocks for tuning. arguments holds the inputs to the test macro, such as values, to and field. From v1.10.5 these go under arguments, and in v2 that nesting is required. config holds framework options such as severity, where and store_failures. Putting where under arguments sends it to the macro as an input instead of filtering the rows the test checks.
Thresholds turn a strict assumption into a tolerant one. Setting severity to warn reports a failure without failing the run. error_if and warn_if set custom failure thresholds based on how many rows fail. Configs can come from several places. For a specific generic test instance, the test's YAML properties override the generic SQL definition's config(), which in turn overrides dbt_project.yml.
Scoping the same assumption two ways leads to a common error. If you put two accepted_values tests on status that differ only in their where config, dbt generates the same name for both. That name is built from the test name, model, column and arguments, and config is left out. The result is a compilation error. Fix it by giving at least one test its own name. The custom name also appears in logs and can be used with --select.
models:
- name: orders
columns:
- name: status
data_tests:
- accepted_values:
name: unexpected_order_status_today
arguments: # available in v1.10.5 and higher. Older versions can set the <argument_name> as the top-level property.
values: ['placed', 'shipped', 'completed', 'returned']
config:
where: "order_date = current_date"Two more details matter when assumptions start failing. Built-in tests often return only the failing value. If you need context such as the customer_id behind a bad status, write a test that selects those extra columns, then run dbt test --store-failures to keep the rows for querying. The older tests: key is still accepted as an alias for data_tests:, but a single resource can't use both.
Checkpoint 8 of 8· Check yourself
You add a second accepted_values test to orders.status, identical to the first except that its where config targets yesterday's orders. What happens on the next parse, and what fixes it?
The generated name ignores config, so both tests get the same name and dbt cannot tell them apart. A name property gives each one a distinct identity.
“with only a difference in configuration, dbt will consider these data tests to be duplicates”Source: docs.getdbt.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.A relationships test on customer_id also catches orders whose customer_id is NULL.Why is that wrong?
relationships excludes NULLs, as a foreign key constraint does. Add not_null to the same column if a missing value should fail.
2.Options like where and severity are test inputs, so they belong under arguments.Why is that wrong?
arguments holds macro inputs such as values, to and field. Framework options such as severity, where and store_failures go under config.
Covered in Tuning how an assumption is checked
3.Because tests: is still supported, you can keep old tests: entries on a model and add new data_tests: entries next to them.Why is that wrong?
tests: works as an alias, but one resource cannot carry both keys at once.
Covered in Tuning how an assumption is checked
4.accepted_values works the same on an integer column as on a string column.Why is that wrong?
The list is single-quoted by default, so integer or boolean columns need quote: false.
Practise it for real
Declare and run a primary-key assumption on a source table
1.In a .yml file under your models directory, declare the jaffle_shop source with an orders table and add unique and not_null under data_tests on its id column.
Why: The primary key is the first assumption worth stating on any table, and declaring it on the source tests the raw data directly.
You should see: dbt parses the file without errors, with the tests attached to source jaffle_shop.orders.
2.Run dbt test --select source:jaffle_shop.orders
Why: The selector limits the run to that one source table's tests.
You should see: Two tests run, one not_null and one unique, each reporting PASS or FAIL.
3.If a test fails, open the compiled test SQL and run it in a query editor.
Why: The compiled query returns exactly the rows that break the assumption.
You should see: The failing rows: duplicated ids for unique, or null ids for not_null.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.https://docs.getdbt.com/docs/build/data-testsOfficial docs
“We recommend that every model has a data test on a primary key, that is, a column that is unique and not_null.”
↩︎ What a data test asserts“a new payment method may introduce logic errors in your SQL.”
↩︎ What a data test asserts“If you assert that a column is unique in a model, the test query selects for duplicates”
↩︎ What a data test asserts“In advanced dbt projects, we recommend using sources and running these source data-integrity tests against the sources rather than models.”
↩︎ Declaring assumptions on sources“whenever you run your transformations in production (to ensure that your assumptions about your source data are still valid)”
↩︎ Declaring assumptions on sources“Use arguments: for inputs to the test macro (for example, values, to, and field).”
↩︎ Tuning how an assumption is checked“You can use the error_if and warn_if configs to set custom failure thresholds in your tests.”
↩︎ Tuning how an assumption is checked“Built-in tests often return only the failing value.”
↩︎ Tuning how an assumption is checked“If the data test returns zero failing rows, it passes, and your assertion has been validated.”
↩︎ Key concept“Use config for framework options such as severity, where, and store_failures.”
↩︎ Exam trap 2“you can't have a tests and a data_tests key associated with the same resource (for example, a single model) at the same time.”
↩︎ Exam trap 3“they are select statements that seek to grab "failing" records, ones that disprove your assertion.”
↩︎ Checkpoint - 2.https://docs.getdbt.com/docs/build/sourcesOfficial docs
“test your assumptions about your source data”
↩︎ Declaring assumptions on sources“to run data tests on one source table only:”
↩︎ Checkpoint - 3.
“you can pass it a ref to a model (e.g. ref('customers')), or a source (e.g. source('jaffle_shop', 'customers')).”
↩︎ Referential integrity, accepted values and grain“This data test validates that all of the non-null values in a column are present in a supplied list of values.”
↩︎ Referential integrity, accepted values and grain“Some data tests require multiple columns, so it doesn't make sense to nest them under the columns: key.”
↩︎ Referential integrity, accepted values and grain“By providing a custom name, you help dbt differentiate data tests”
↩︎ Tuning how an assumption is checked“This test automatically excludes NULL values from validation, consistent with how database foreign key constraints work.”
↩︎ Exam trap 1“To test non-strings (like integers or boolean values) explicitly set the quote config to false.”
↩︎ Exam trap 4“This test automatically excludes NULL values from validation, consistent with how database foreign key constraints work.”
↩︎ Prediction“Use the not_null test separately if NULL values should cause failures.”
↩︎ Checkpoint“with only a difference in configuration, dbt will consider these data tests to be duplicates”
↩︎ Checkpoint - 4.https://docs.getdbt.com/reference/data-test-configsOfficial docs
“the test's .yml properties would take precedence over any values set in its generic SQL definition's config()”
↩︎ Tuning how an assumption is checked