Unit testing dbt models - Step-by-Step Execution

Concept Flow - Unit testing dbt models

Write SQL model

↓

Create test SQL or schema test

↓

Run dbt test command

↓

dbt runs tests on model

↓

Check test results

↓

Pass

→Model is correct

↓

Fail

→Fix model or test

↩Back to Write SQL model

Unit testing dbt models means writing tests for your SQL models, running them with dbt, and fixing issues if tests fail.

Execution Sample

dbt

select * from {{ ref('my_model') }} where id is null

This test checks if the model 'my_model' has any rows with null id, which should not happen.

Execution Table

Step	Action	Test SQL Evaluation	Test Result	Next Step
1	Run dbt test	select * from my_model where id is null	No rows returned	Pass - Model is correct
2	Run dbt test	select * from my_model where id is null	Rows with null id found	Fail - Fix model or test
3	Fix model SQL	Update model to filter out null ids	Model updated	Re-run dbt test
4	Run dbt test again	select * from my_model where id is null	No rows returned	Pass - Model is correct

💡 Testing stops when all tests pass or after fixing model and re-running tests

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
Test Result	Not run	Pass	Fail	Pass	Pass
Model SQL	Initial SQL	Initial SQL	Fixed SQL	Fixed SQL	Fixed SQL

Key Moments - 2 Insights

Why does the test fail even though the model runs without errors?

The test checks data quality or logic, not just if the model runs. See execution_table row 2 where test finds null ids causing failure.

What does 'No rows returned' mean in the test result?

It means the test condition found no problematic data, so the test passes. See execution_table rows 1 and 4.

Visual Quiz - 3 Questions

Look at the execution table, what is the test result at step 2?

A Pass

B Fail

C Not run

D Error

💡 Hint

Check the 'Test Result' column in row 2 of the execution_table.

At which step is the model SQL updated to fix the test failure?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Action' column describing fixing the model in execution_table.

If the test SQL changed to check for negative ids instead of null ids, what would change in the execution table?

A Test SQL Evaluation column would show 'where id < 0'

B Test Result would always be Pass

C Model SQL would change

D No changes needed

💡 Hint

Test SQL Evaluation column shows the exact test query being run.

Concept Snapshot

Unit testing dbt models:
- Write SQL or schema tests for your models
- Run tests with 'dbt test'
- Tests check data quality or logic
- Fix model or tests if failures occur
- Repeat until all tests pass

Full Transcript

Unit testing dbt models means writing tests that check your SQL models for correctness. You write test SQL or schema tests that look for problems like null values or duplicates. Then you run 'dbt test' which runs these tests on your models. If tests find issues, they fail and you fix your model or test. You run tests again until all pass. This process helps ensure your data models are accurate and reliable.