Data testing using dbt

This is a minimal hands-on example project that demonstrates four (4) different ways of using dbt tests.

For purposes of demonstration, these are ordered from least magical to most. But I'd recommend going for the most magical option that works for your use-case, so prioritize these in reverse order in your own dbt projects!

What this demo covers

1. creating a custom singular test
2. creating a custom generic test
3. using an existing test from a dbt package on the Hub
4. using a built-in test

There's actually a lot more that isn't covered here. To avoid an overwhelming amount of text, I put the "not covered" list here.

What is a test?

Like almost everything in dbt, tests are SQL queries. By convention:

• if a test query returns 0 rows, it will PASS
• if a test query returns > 0 rows, it will FAIL

The select statements for tests are specially crafted to find any records that would disprove your assertion. Logically, your assertion should fail if it finds any and pass if it finds none. This overview for tests provides more explanation.

Prerequisites

Verify that both python3 and git are installed and available:

python3 --version
git --version

Clone

Clone this repo using HTTPS (or your method of choice):

git clone https://github.com/dbeatty10/demo-dbt-testing.git
cd demo-dbt-testing

Install

Create a virtual environment and install dependencies using bash/zsh (or your OS shell of choice):

python3 -m venv env
source env/bin/activate
python3 -m pip install --upgrade pip
python3 -m pip install -r requirements.txt
source env/bin/activate

Run all the tests

With the least number of commands:

dbt deps
dbt build

or a little more verbose:

dbt deps
dbt seed
dbt run
dbt test

Exit codes

When all tests PASS, the exit code should be 0. If any test results in a FAIL, then a non-zero exit code will result:

echo $?

Creating a custom singular test

Source:

• tests/singular/check_duplicate_column.sql

Compiled:

• target/compiled/jaffle_shop/tests/singular/check_duplicate_column.sql

Executed:

• target/run/jaffle_shop/tests/singular/check_duplicate_column.sql

Creating a custom generic test

Source:

• tests/generic/my_unique.sql
• seeds/_seeds.yml

Compiled:

• target/compiled/jaffle_shop/seeds/_seeds.yml/my_unique_all_unique_color.sql
• target/compiled/jaffle_shop/seeds/_seeds.yml/my_unique_all_unique_id.sql

Executed:

• target/run/jaffle_shop/seeds/_seeds.yml/my_unique_all_unique_color.sql
• target/run/jaffle_shop/seeds/_seeds.yml/my_unique_all_unique_id.sql

Using an existing test from a dbt package on the Hub

Source:

• seeds/_seeds.yml

Compiled:

• target/compiled/jaffle_shop/seeds/_seeds.yml/dbt_expectations_expect_compou_fe140eeee66ced88a8eee1b7a8d9df03.sql
• target/compiled/jaffle_shop/seeds/_seeds.yml/dbt_utils_unique_combination_o_4eccc42fcbdbe0f27e2c0bd911b83b65.sql

Executed:

• target/run/jaffle_shop/seeds/_seeds.yml/dbt_expectations_expect_compou_fe140eeee66ced88a8eee1b7a8d9df03.sql
• target/run/jaffle_shop/seeds/_seeds.yml/dbt_utils_unique_combination_o_4eccc42fcbdbe0f27e2c0bd911b83b65.sql

Using a built-in test

Source:

• seeds/_seeds.yml

Compiled:

• target/compiled/jaffle_shop/seeds/_seeds.yml/unique_all_unique_color.sql
• target/compiled/jaffle_shop/seeds/_seeds.yml/unique_all_unique_id.sql

Executed:

• target/run/jaffle_shop/seeds/_seeds.yml/unique_all_unique_color.sql
• target/run/jaffle_shop/seeds/_seeds.yml/unique_all_unique_id.sql

Examine the compiled and run tests

Wrap up

Deactivate the virtual environment when finished:

deactivate