dbt · Testing & Documentation

Custom generic test

Mediumcustom testgeneric testmacrosyaml

Interview question

How do you write a custom generic test in dbt?

How to answer

The short answer

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):

SQL
{% test positive_value(model, column_name) %}

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

{% endtest %}

Use it in YAML

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

Mental model

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.