How to Write a Custom Test in dbt
Last updated July 25, 2026 · By the SaturnSQL team
A singular test is a SELECT saved in tests/ that returns the failing rows; dbt test fails if it returns anything. For reusable logic, define a custom generic test as a test-block macro and apply it to columns in schema.yml like a built-in test.
Singular test
A singular test is just a query that should return nothing. Save it anywhere under tests/ and dbt test runs it, failing if a single row comes back. The rows it returns are the failing rows, so keep SELECT * rather than SELECT COUNT(*), which would always return one row and therefore always fail.
-- tests/assert_no_negative_amounts.sql
SELECT *
FROM {{ ref('fct_orders') }}
WHERE amount < 0Custom generic test
Wrapping the query in a {% test %} block makes it reusable. The model and column_name arguments are supplied by dbt from the YAML that applies the test. Files go in tests/generic/ (macros/ also works), and the test name comes from the block rather than the filename, though keeping the two the same avoids confusion.
-- tests/generic/is_positive.sql
{% test is_positive(model, column_name) %}
SELECT *
FROM {{ model }}
WHERE {{ column_name }} <= 0
{% endtest %}Apply it in YAML
Once defined, a generic test is applied exactly like unique or not_null. Use data_tests: on dbt 1.8 and later; the older tests: key still works but is deprecated. Extra arguments are nested under the test name, and severity: warn reports failures without failing the run.
models:
- name: fct_orders
columns:
- name: amount
data_tests:
- is_positiveRun this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free