Custom generic and singular data tests
The problem
The four built-in tests do not know that amount must be at least zero. Official dbt documentation describes two extension paths: a singular data test (a SQL file that returns failing rows) and a generic data test (a {% test %} block you can attach to many columns).
A working example
Generic test, in macros/test_amount_is_non_negative.sql:
{% test amount_is_non_negative(model, column_name) %}
select *
from {{ model }}
where {{ column_name }} < 0
{% endtest %}
Attached in YAML:
models:
- name: stg_orders
data_tests:
- amount_is_non_negative:
arguments:
column_name: amount
dbt test in the companion project: PASS (all seed amounts were positive).
Singular test, in tests/assert_completed_orders_have_amount.sql:
select order_id, amount, status
from {{ ref('stg_orders') }}
where status = 'completed' and amount <= 0
Also PASS. Official docs: test-paths (usually tests/) is reserved for singular and generic data tests. Unit test YAML belongs under model-paths.
A generic test is the right shape once you will attach the same assertion to more than one model or column. A singular test is the right shape when the assertion is a one-off join or business rule that will never be reused.
The gotcha
Generic tests are ordinary SQL that must return failing rows. If you accidentally write the query as “rows that are valid,” the test will fail when the data is clean and pass when it is dirty. Invert the predicate and have a look at target/compiled/… for a failing run.
The arguments: wrapper is the newer official YAML (noted as available in v1.10.5 and higher). Older examples set column_name: at the top level of the test. Mixing styles across a large repo is confusing but often still valid; pick one and stay current with the docs for your dbt version.
When not to use this
- A built-in or
dbt_utilstest already says the same thing. - The rule belongs in a model (
where amount >= 0) and a test would only duplicate the filter. Sometimes you want both — the filter hides bad rows, the test tells you they arrived. - You were about to write a generic test that takes no arguments and is only ever applied once. That is a singular test.
Both the generic amount_is_non_negative test and the singular completed-orders test passed on dbt-core 1.12.7 / dbt-duckdb 1.11.1 with the project seeds. The singular-vs-generic distinction and the unit-test path rule are from official dbt documentation.