dbt Packages & Tests

Custom generic and singular data tests

A parameterized test block that ran in the companion project, versus a one-off SQL file of failing rows.

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

Verification

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.

Official docs