Singular Tests

Concepts covered: Data tests

The 4 generic tests each state one shape of rule about one column: its values are present, its values are distinct, its values come from a list, its values exist in another model. A rule that compares 2 columns, joins 2 models, or applies arithmetic fits none of them. A singular test is that rule written out as SQL: a .sql file in the tests/ folder whose SELECT returns the rows that break the rule. dbt runs the file like any other test, and 0 returned rows means the test passes.

On fct_order_lines, a refund can never exceed what the customer paid for the line. No generic test compares refund_amount with line_total, so the rule becomes a file named tests/assert_refund_within_line_total.sql:

select
    order_line_key,
    line_total,
    refund_amount
from {{ ref('fct_order_lines') }}
where refund_amount > line_total

dbt names the test after the file, so the log reads PASS assert_refund_within_line_total or FAIL 14 assert_refund_within_line_total. The ref() places the test in the DAG behind fct_order_lines, so it runs after that model is built and, at error severity, holds back every model that reads fct_order_lines. As with a generic test, the query dbt sends wraps the file's SELECT in a count, and the count decides the result:

select
    count(*) as failures,
    count(*) != 0 as should_warn,
    count(*) != 0 as should_error
from (
    select
        order_line_key,
        line_total,
        refund_amount
    from analytics.fct_order_lines
    where refund_amount > line_total
) dbt_internal_test

A rule across 2 models

The rule that most often needs a singular test is a reconciliation: a total in one model must equal the sum of its parts in another. stg_orders carries order_total from the checkout system, and fct_order_lines carries 1 line_total per line. The 2 agree for every order when the lines model is right, and they disagree when a join dropped a line or duplicated one. The test sums the lines per order, joins the sum back to the order, and keeps the orders where the figures differ:

with line_sums as (
    select
        order_id,
        sum(line_total) as lines_total
    from {{ ref('fct_order_lines') }}
    group by order_id
)
select
    o.order_id,
    o.order_total,
    l.lines_total
from {{ ref('stg_orders') }} o
left join line_sums l
    on o.order_id = l.order_id
where abs(o.order_total - coalesce(l.lines_total, 0)) > 0.01

On the morning this test fails, 3 details in the query determine how much the engineer can learn from it. The left join keeps an order whose lines were all dropped: it has no row in line_sums, so lines_total comes back null, coalesce counts it as 0, and the order is returned. An inner join would drop that order from the result, and the worst case of a lost line would pass. The SELECT list names the key and both figures, so each returned row identifies 1 order and shows the size of its disagreement; select * over a 40-column model would bury that. The tolerance of 0.01 absorbs rounding between a system that stores cents and one that stores decimals, and it is written in the test where the next engineer can read what was chosen and ask why.

Configs on a singular test

A singular test takes its configs from a config() block at the top of the file, the same way a model does. The reconciliation above keeps the default severity, because a payout computed from a missing line is wrong money. A test that reports orders with a negative total, which a legacy returns process produces on purpose and which finance has agreed to tolerate while it is replaced, warns instead:

{{ config(severity='warn') }}

select
    order_id,
    order_total
from {{ ref('stg_orders') }}
where order_total < 0

A singular test fits 1 rule in 1 place. When the same rule, refund within total, applies to 3 models, 3 copies of the file drift apart as columns are renamed, and that case is a generic test with arguments, which a later lesson writes. Until then, 1 file per rule, named for what it asserts, with assert_ as the prefix so the tests/ folder reads as a list of guarantees the project makes.

Proving the test can fail

A singular test that passes on its first run has shown 1 of 2 things: the data obeys the rule, or the query cannot return rows. A join on the wrong column, a where that compares a varchar with a number, or a filter that excludes every row each produce a test that passes forever. Before trusting a new singular test, run its SELECT in dev with the condition flipped, where refund_amount <= line_total, and confirm that rows come back; then flip it back.

Do

• Name the file for the rule: assert_refund_within_line_total.sql
• Select the key and the compared values, so a failing row explains itself
• Write tolerances and date ranges in the query, where the next reader sees them
• Flip the condition once in dev to prove the query can return rows

Avoid

• Copy one singular test across 3 models; that rule wants a generic test
• Select * from a wide model; the failing rows become unreadable
• Hide the rule in a model's where clause, where it drops rows instead of failing

About this lesson

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