Creating singular tests

Intermediate dbt

Mike Metzger

Data Engineer

What is a singular test?

  • Custom data test
  • Written as an SQL query
    • Must return failing rows
  • Defined as .sql file in tests directories

Test image

1 Photo by Antoine Dautry on Unsplash
Intermediate dbt

Example singular test

  • Create a test to verify:
    • order_total >= subtotal
select *
from order
where order_total < subtotal
  • Remember, we want rows that fail the test if rows return, the test fails.
  • Save file as assert_order_total_gte_subtotal.sql

order_table schema diagram

Intermediate dbt

Singular test with Jinja

  • We can use Jinja in our tests
    • ref function
    • Others as appropriate
  • dbt substitutes (replaces) output when test is run
    • Rerun the project if your dbt profile changes
select *
from {{ ref('order') }}
where order_total < subtotal
Intermediate dbt

Test debugging

  • Use a SQL editor to create the initial test query
  • Place query in appropriate file
  • Make sure to name the test uniquely
  • Use the dbt test --select <testname> option
  • Check any errors and update accordingly
Intermediate dbt

Let's practice!

Intermediate dbt

Preparing Video For Download...