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 macro with {% test %} and apply it to columns in schema.yml like a built-in test.
Singular test
-- tests/assert_no_negative_amounts.sql
SELECT *
FROM {{ ref('fct_orders') }}
WHERE amount < 0Custom generic test
-- tests/generic/is_positive.sql
{% test is_positive(model, column_name) %}
SELECT *
FROM {{ model }}
WHERE {{ column_name }} <= 0
{% endtest %}Apply it in YAML
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