How to write a custom SQL test in dbt

When the built-in tests are not enough, a test is just a query: it returns the rows that break the rule, and it passes when it returns none. A rule that applies to one model goes in a singular test. A rule you want to reuse on many columns becomes a generic test, used from YAML like unique.

Copy and paste

A singular test, one SQL file in tests/:

-- tests/assert_revenue_matches_orders.sql
-- Total revenue in customer_orders must equal the sum of all orders.
select co.total, o.total
from (select sum(revenue) as total from {{ ref('customer_orders') }}) co
cross join (select sum(amount) as total from {{ ref('stg_orders') }}) o
where co.total <> o.total

A generic test, in tests/generic/:

-- tests/generic/positive_value.sql
{% test positive_value(model, column_name) %}
select *
from {{ model }}
where {{ column_name }} < 0
{% endtest %}

      - name: amount
        data_tests:
          - positive_value

A generic test with an argument (Snowflake SQL):

-- tests/generic/not_older_than.sql
{% test not_older_than(model, column_name, days) %}
select *
from {{ model }}
where {{ column_name }} < dateadd(day, -{{ days }}, current_date)
{% endtest %}

      - name: order_date
        data_tests:
          - not_older_than:
              arguments:
                days: 30

How they run

dbt test -s assert_revenue_matches_orders     # one singular test
dbt test -s test_type:singular                # all singular tests
dbt test -s test_type:generic                 # all generic tests
dbt build -s +customer_orders                 # models and the tests that use them

dbt attaches a singular test to every model it refs. The example above runs together with customer_orders and stg_orders, and if it fails, dbt build skips what depends on them.

Things to know

• Return the bad rows, not a count. A query that returns select count(*) ... always returns one row, so the test always fails. Filter with where or having instead.
• model and column_name are filled in by dbt. In a generic test, model is the relation the test is attached to and column_name the column in YAML. Other arguments come from arguments:.
• Names. dbt names generic test instances after the test, the model, the column and the arguments, for example not_older_than_stg_orders_order_date__30.
• Warnings and thresholds work as for built-in tests; see how to make a test warn instead of fail.

Tested with dbt Core 1.12 (DuckDB adapter); the date function in the third example is written for Snowflake.