10. A Reusable not_negative Test
Difficulty: Medium · Topic: Data tests
Write a generic test that any column can use from YAML:
columns:
- name: amount
data_tests:
- not_negative
Define it with a {% test not_negative(model, column_name) %} block. dbt calls it with model (the relation being tested) and column_name, and the test fails for every row it returns. Return all columns of the rows where the column is below zero. Null values are not this test's business (that is what not_null is for), so they must not fail.
Behind the scenes dbt compiles a test named not_negative into a macro called test_not_negative, which is what the tests here call, on different models and columns.
Your model is compiled like dbt does and run in DuckDB against 3 test cases, including hidden edge cases.
Sample inputs
{{ ref('payments') }}
| payment_id | amount |
|---|---|
| 1 | 10 |
| 2 | -5 |
| 3 | 0 |
| 4 | null |
{{ ref('order_items') }}
| order_id | quantity | discount |
|---|
Expected output
| payment_id | amount |
|---|---|
| 2 | -5 |
Hints
Hint 1
A test body is a SELECT that returns the failing rows:select * from {{ model }} where ....Hint 2
{{ model }} prints the relation and {{ column_name }} the column, so the same test works anywhere.