Creating custom reusable tests

Intermediate dbt

Mike Metzger

Data Engineer

What is a reusable test?

A test that can be reused in multiple situations
Much like a built-in dbt test, but can check any condition
Uses Jinja templating
Saved as a .sql file in the tests/generic project folder
Must add test to the model_properties.yml for each model that uses it

Reusable

¹ Photo by Sigmund on Unsplash

Creating a reusable test

{% test check_gt_0(model, column_name) %}

select * 
from {{ model }}
where {{ column_name }} > 0

{% endtest %}

First line (at least): {% test testname(model, column_name) %}
Add SQL query, with {{ object }} substitutions
End the file with {% endtest %}

Applying reusable test to model

Add to model_properties.yml
Define the objects as necessary
The models: name value is the model argument
The columns: name argument is the column_name argument

version: 2

models:
  - name: taxi_rides_raw
    columns:
      - name: tpep_pickup_datetime
        tests:
          - not_null

      - name: total_fare
        tests:
          - check_gt_0

Extra parameters

Can add extra parameters to the test
Similar to accepted_values and relationships
Add as arguments to the Jinja header

{% test check_columns_unequal(model, column_name, column_name2) %}

select * from {{ model }}
where {{ column_name }} = {{ column_name2 }}

{% endtest %}

Applying extra parameters tests

Define like other tests
Add the extra arguments below the test details

models:
  - name: order
    columns:
      - name: order_time
        tests:
          - check_columns_unequal:
              column_name2: shipped_time

Let's practice!

Intermediate dbt