Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 41 ยท Tests and Documentation

Singular and Custom Generic Tests

Write your own dbt tests: one-off singular tests in tests/, and reusable generic tests with Jinja. Business rules become SQL you can run in your browser.

The four built-in tests cover structure. Business rules need something more: "an order total is never negative", "a refund is never larger than the payment", "every shipped order has a ship date". dbt gives you two ways to write them. A singular test is a single SQL file for one specific rule. A custom generic test is a Jinja macro you can attach to any column, like the built-ins.

Both follow the rule you already know: the query returns the failing rows, and zero rows means pass.

Singular tests

Put a .sql file in the tests/ folder (the test-paths setting in dbt_project.yml). The file name is the test name:

SQL
-- tests/assert_order_totals_are_not_negative.sql
select
    order_id,
    order_total
from {{ ref('fct_orders') }}
where order_total < 0

That is the whole test. It is picked up by dbt test, by dbt build, and can be selected with --select assert_order_totals_are_not_negative. Use ref() and source() as in any model, so the test joins the lineage graph.

Singular tests are perfect when a rule involves several models or columns, for example reconciling two tables:

SQL A singular test: line items must add up ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

An empty result means the rule holds for this data, so the test would pass. To see a failing test, change the condition to o.status = 'cancelled' and add HAVING COUNT(i.id) > 0: that asks for cancelled orders that still have items, a different rule that you might or might not want to enforce.

When a singular test becomes repetitive

You will soon write the same shape of test again and again: "this column is never negative" for price, quantity, balance. Copy-pasting singular tests is a smell. Turn the pattern into a generic test.

Custom generic tests

A generic test is a macro with the test block syntax. It receives the model and column as arguments:

SQL
-- tests/generic/not_negative.sql   (or macros/)
{% test not_negative(model, column_name) %}

select {{ column_name }}
from {{ model }}
where {{ column_name }} < 0

{% endtest %}

Now use it in YAML like any built-in:

YAML
models:
  - name: fct_orders
    columns:
      - name: order_total
        data_tests:
          - not_negative
      - name: shipping
        data_tests:
          - not_negative

dbt substitutes model with the relation ("analytics"."fct_orders") and column_name with the column. You can add your own parameters as extra arguments with defaults:

SQL
{% test between(model, column_name, min_value, max_value) %}

select {{ column_name }}
from {{ model }}
where {{ column_name }} < {{ min_value }}
   or {{ column_name }} > {{ max_value }}

{% endtest %}
YAML
- name: quantity
  data_tests:
    - between:
        min_value: 1
        max_value: 100

Here is the SQL that this test compiles to, run against the sample data with the Jinja already filled in:

SQL What between compiles to ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The {% test %} block is Jinja, which dbt renders before sending SQL to the warehouse. The editor above shows the rendered result; the macro itself cannot run here.

Where custom tests live

dbt looks for generic tests in tests/generic/ and in macros/. A good convention is tests/generic/ for tests and macros/ for everything else. If a test has the same name as a built-in, yours wins, which lets you override a built-in. Do that only on purpose.

flowchart TD
  R["Need a data check"] --> Q{"Used on many columns?"}
  Q -->|"No, one-off rule"| S["Singular test: tests/name.sql"]
  Q -->|"Yes"| G["Generic test: {% test name %}"]
  G --> Y["Attach in YAML like not_null"]

Choose singular for one rule, generic for a reusable pattern.

Tips

  • Name singular tests as sentences: assert_refunds_not_larger_than_payments.
  • A test should select the columns that help you debug the failure, not just count(*).
  • Keep tests fast: filter to recent data with a where config if the table is huge.
  • Do not use tests to transform data. A test that "fixes" nothing and only reports is easy to reason about.

Check your understanding

Quick check ยท 3 questions

of 3 answered You scored / 3 ยท perfect!

  1. 1. Where does a singular test live?

    A singular test is one SQL file under tests/ that selects the failing rows.
  2. 2. What makes a custom generic test reusable?

    The {% test %} macro receives the model and column, so any column can use it from YAML.
  3. 3. A custom generic test query returns three rows. What is the result?

    Returned rows are failures, no matter how the test is written.

Summary

  • Singular tests are one-off SQL files in tests/ that return failing rows.
  • Generic tests are {% test %} macros with model and column_name parameters.
  • Extra parameters let you make flexible tests like between.
  • Turn a repeated singular test into a generic one.

Next: Test Severity, store_failures and Selection