Which data tests to start with
The problem
A new dbt project needs tests, and the ecosystem offers built-in generic tests, dbt_utils, Elementary, legacy dbt-expectations, Soda, and Great Expectations. Official dbt documentation is clear about the core: data tests are SELECT statements that return failing rows. If they return zero rows, the test passes.
Official docs also renamed “tests” to data tests so they are not confused with unit tests. The YAML key tests: is still supported as an alias for data_tests:.
A working example
dbt ships four generic data tests: not_null, unique, relationships, and accepted_values. In this site’s companion project they were declared like this (dbt 1.10.5+ arguments: form from official docs):
models:
- name: stg_orders
columns:
- name: order_id
data_tests:
- not_null
- unique
- name: customer_id
data_tests:
- relationships:
arguments:
to: ref('stg_customers')
field: customer_id
- name: status
data_tests:
- accepted_values:
arguments:
values: ['completed', 'pending', 'returned']
Executed with dbt-core 1.12.7 / dbt-duckdb 1.11.1: unique, not_null, and accepted_values on these models passed. The relationships test failed with 1 row: order_id 105 had customer_id 9, which is not in stg_customers. That is the test doing its job.
A singular data test (a SQL file in tests/) is official too: one SELECT of failing rows. The project file tests/assert_completed_orders_have_amount.sql passed.
How this compares to packages (without installing all of them)
- Start with the four built-ins. Official docs say generic tests should make up the bulk of a suite.
dbt_utilsadds tests such asunique_combination_of_columns, which official dbt docs recommend for uniqueness of two columns, especially on large datasets. See dbt_utils workhorses.- Elementary adds anomaly / volume-style tests and a report if you already run dbt. This site did not install or run Elementary.
dbt-expectationsis a large GE-inspired test library. Its GitHub repository states it is no longer actively supported. Do not add it to a new project without reading that notice.- Great Expectations / Soda are separate products. They can complement dbt; they are not a reason to skip
not_nullanduniqueon your primary keys. Official GE and Soda behavior was not executed here.
Official docs also mention severity (warn or error) and warn_if / error_if thresholds so a test can warn before it fails the run. Use those when a relationship is known-dirty and you still want a signal.
The gotcha
A relationships failure is easy to “fix” by deleting the test. The failing row in this project was a deliberate orphan. In a real warehouse it is often a late-arriving dimension or a broken join key — the test is the news, not the nuisance.
tests: vs data_tests: will both work today (official alias). New YAML should use data_tests: so it does not collide, in your head, with unit tests, which official docs say must live under model-paths, not test-paths.
When not to use this
- Do not add dozens of
accepted_valueson free-text columns you do not control. You will fail every load on a new code. - Do not treat a vendor blog’s “500 generic tests” list as a starting checklist. Official guidance: fewer, meaningful assertions first.
- Do not skip tests because a SaaS observability tool is on the roadmap. They answer different questions.
Built-in tests were executed in the companion project: uniqueness, null, and accepted-values passed; the relationships test failed on order 105 / customer 9. The singular test passed. The data_tests alias, the four built-in tests, and the two-column uniqueness options are from official dbt documentation. dbt-expectations’ “no longer actively supported” notice is from its GitHub README, fetched November 18, 2026. Elementary, Soda, and Great Expectations were not executed.