The Four Generic Tests

Concepts covered: Data tests

dbt ships 4 generic tests. not_null and unique need only a column, while accepted_values and relationships take inputs as well. Each is a query template with a model and a column filled in, and each reports 1 kind of mistake that raw data makes often. Read the query behind a test and you know which rows it reports and how dbt counts them; the same query shows which bad rows it lets past.

not_null reports a missing value

Its query selects the column where the value is null, as the last section showed. It catches a loader that wrote a row before the value existed, and a left join in the model that matched nothing and left null where an id belongs. The count is the number of rows holding the null.

unique reports a value that appears twice

unique groups the column and keeps every group holding more than 1 row. It catches a loader that replayed a batch, and a join that matched several rows on the far side and multiplied each order.

select
    order_id as unique_field,
    count(*) as n_records
from analytics.stg_orders
where order_id is not null
group by order_id
having count(*) > 1

The clause where order_id is not null means nulls never form a duplicate group, so a column with 3 null ids passes unique. Reporting those is not_null's job, which is why the 2 tests are declared together on an id column. The count is the number of duplicated values rather than rows, so 1 id that appears 3 times prints FAIL 1.

That single row is what the query returns after a loader delivers order 103 a second time: 1 value, seen 2 times, printed as FAIL 1.

accepted_values reports a value outside the list

accepted_values groups the column by its values and keeps the values absent from the list you give it. It catches a new status the application started sending, a casing change such as Shipped, and a typo that reached the source.

with all_values as (
    select
        status as value_field,
        count(*) as n_records
    from analytics.stg_orders
    group by status
)
select
    *
from all_values
where value_field not in ('placed', 'shipped', 'delivered')

Here 2 of the 5 orders in stg_orders arrived with a status the list does not hold, 1 recased as Shipped and 1 missing entirely. The grouped result above becomes 1 returned row, Shipped with its capital letter, printed as FAIL 1. The null status passes this test: null not in (...) evaluates to neither true nor false, and the where clause drops the row. The count is the number of distinct unexpected values, so 500 rows of cancelled also print FAIL 1, which says 1 new value exists and nothing about how many rows carry it.

relationships reports a key that points at nothing

relationships takes each value in a column and looks for it in a column of another model, keeping the values that find no match. It catches an order whose customer was deleted, a customer created after the orders loaded, and a customers model that filtered out rows the orders still reference.

with child as (
    select
        customer_id as from_field
    from analytics.stg_orders
    where customer_id is not null
),
parent as (
    select
        customer_id as to_field
    from analytics.stg_customers
)
select
    from_field
from child
left join parent
    on child.from_field = parent.to_field
where parent.to_field is null

The left join keeps every order and attaches a customer when one exists; the final where keeps the orders where nothing attached, which above is orders 103 and 105 from customer 4. Nulls are removed in child, so an order with no customer id at all passes relationships and is not_null's to report. The count is child rows, so those 2 orders print FAIL 2, even though they share 1 missing customer.

About this lesson

This section is part of the Testing: Beginner 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.