Writing a Generic Test

Concepts covered: Macros and packages

A singular test is one query about one table. Wanting the same check on 12 columns across 5 models would mean 12 copies of that query, each edited by hand. A generic test is a query with 2 holes, the model and the column, and dbt fills the holes from YAML every time a column declares it. The 4 built-in tests are written this way, and a test you write joins them as an equal.

{% test not_negative(model, column_name) %}
select
    {{ column_name }}
from {{ model }}
where {{ column_name }} < 0
{% endtest %}

The {% test %} block works like a macro with a reserved purpose. dbt reads it from tests/generic/ or from macros/ at parse time, and the name after test becomes the key a column writes in YAML. model arrives as the relation the model builds, column_name as the name the YAML sits under, and the query must return the rows that break the rule: zero rows is a pass, any row is a failure.

models:
  - name: stg_orders
    columns:
      - name: amount
        data_tests:
          - not_negative
      - name: tax
        data_tests:
          - not_negative:
              config:
                severity: warn

Each declaration becomes its own test node, so stg_orders now carries 2 tests, not_negative_stg_orders_amount and not_negative_stg_orders_tax, each named after the rule, the model and the column. For tax, dbt renders the block with model set to the table and column_name set to tax, then wraps the result in the counting query it wraps around every test, and a warn severity changes what the count does to the run, never the query.

select
    count(*) as failures,
    count(*) != 0 as should_warn,
    count(*) != 0 as should_error
from (
    select
        tax
    from analytics.stg_orders
    where tax < 0
) dbt_internal_test

Tests That Take an Argument

A hole for the column is often not enough. A currency code must be 3 characters long, a country code 2, and one test should serve both. Any argument after model and column_name is filled from YAML, and a default after the equals sign makes it optional.

{% test has_length(model, column_name, length=3) %}
select
    {{ column_name }}
from {{ model }}
where length({{ column_name }}) != {{ length }}
{% endtest %}

models:
  - name: stg_orders
    columns:
      - name: currency
        data_tests:
          - has_length
      - name: country
        data_tests:
          - has_length:
              arguments:
                length: 2

The first declaration uses the default and compiles to where length(currency) != 3; the second compiles to where length(country) != 2. Watch what a null does here: length of null is null, the comparison is null, and the row stays out of the failures. A generic test checks the rule it states and nothing more, so a column that also must never be null declares not_null beside it, as 2 nodes with 2 results.

Each fail is one row returned by that node's query, so the 3 nodes each report 1 failure. Take away the not_null declaration and the GBP row passes every test its country column has, which is the gap the second declaration on the same column closes.

An Argument That Names a Column

{% test not_before(model, column_name, earlier_column) %}
select
    {{ earlier_column }},
    {{ column_name }}
from {{ model }}
where {{ column_name }} < {{ earlier_column }}
{% endtest %}

models:
  - name: stg_shipments
    columns:
      - name: shipped_at
        data_tests:
          - not_before:
              arguments:
                earlier_column: ordered_at

earlier_column arrives as the text ordered_at, the same way a macro argument does, so the test prints it into the query as a column name and compiles to where shipped_at < ordered_at. Selecting both columns means a stored failure shows the pair that broke the rule: an order placed at 10:15 and shipped at 09:40 the same morning is visible at a glance.

Singular test

• One .sql file under tests/, one query, one table
• Names the table it checks with ref() inside the file
• Fits a rule that holds for exactly one model
• Runs once per project

Generic test

• One test block, reused by any column that declares it
• Takes the table and column as holes filled from YAML
• Fits a rule that several columns share
• Runs once per declaration, each its own node

Do

• Select the failing rows, so stored failures show which values broke the rule
• Give an argument a default when one value fits most columns
• Name the block for the rule that passes, as not_negative does

Avoid

• Order or limit the query, dbt only counts its rows
• Make one test check 2 rules, declare 2 tests instead
• Reference a specific table inside the block, that is a singular test

About this lesson

This section is part of the Jinja and Macros: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.