Custom generic test

A custom generic test is a macro (often under tests/ or macros/) that generates SQL for a reusable check. You then call it from YAML like a built-in.

Example: values must be greater than zero

macros/tests/test_positive_value.sql (pattern; exact path/conventions vary by dbt version):

{% test positive_value(model, column_name) %}

select *
from {{ model }}
where {{ column_name }} <= 0
   or {{ column_name }} is null

{% endtest %}

Use it in YAML

models:
  - name: fct_orders
    columns:
      - name: amount
        tests:
          - positive_value

Mental model

{% test name(model, column_name, ...) %}
    SQL that returns FAILING rows
{% endtest %}
        |
        v
YAML references the test name with optional args

Tips

• The test SQL should return only bad rows (empty result = pass).
• Add arguments for thresholds, where-clauses, or severity-related behavior.
• Prefer a generic test once the same rule appears on 3+ columns/models.

Interview tip: Explain that custom generics keep YAML clean and avoid copy-pasting the same singular test SQL across the repo.