Testing model outputs in dbt - Step-by-Step Execution

Concept Flow - Testing model outputs

Run dbt model

↓

Generate output table

↓

Run tests on output

↓

Test passes

→Continue

↓

Test fails

→Fix model or data

↓

Deploy or iterate

This flow shows how dbt runs a model, generates output, tests it, and either continues or fixes issues.

Execution Sample

dbt

select id, count(*) as cnt
from source_table
group by id

This SQL model groups data by id and counts rows per id.

Execution Table

Step	Action	Evaluation	Result
1	Run model SQL	Group by id and count rows	Output table with id and cnt columns
2	Run test: unique id	Check if all ids are unique	Pass if no duplicates
3	Run test: cnt > 0	Check if count is positive	Pass if all counts > 0
4	Test results	All tests pass	Model output is valid
5	End	No errors found	Ready for deployment

💡 All tests pass, so execution stops successfully.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
output_table	empty	table with id and cnt	same	same	same
test_unique_id	not run	not run	pass	pass	pass
test_cnt_positive	not run	not run	not run	pass	pass

Key Moments - 2 Insights

Why do we run tests after the model runs?

Tests check if the output data meets expectations, catching errors early as shown in steps 2 and 3 of the execution table.

What happens if a test fails?

If a test fails, the flow stops and you fix the model or data before continuing, preventing bad data from moving forward.

Visual Quiz - 3 Questions

Look at the execution table, what is the result of step 3?

A Test passes because all counts are positive

B Test fails due to negative counts

C Model output is empty

D Test not run yet

💡 Hint

Check the 'Result' column in row with Step 3 in execution_table

At which step do we verify that all ids are unique?

A Step 1

B Step 4

C Step 2

D Step 5

💡 Hint

Look at the 'Action' column in execution_table for the unique id test

If the test for unique ids failed, what would happen next?

A Continue to deploy model

B Fix model or data before continuing

C Ignore and run other tests

D Delete the model

💡 Hint

Refer to the concept_flow where test failure leads to fixing issues

Concept Snapshot

Testing model outputs in dbt:
- Run your SQL model to create output
- Use dbt tests to check data quality
- Tests include uniqueness, non-null, and value checks
- If tests pass, deploy or continue
- If tests fail, fix model or data before proceeding

Full Transcript

In dbt, after running a model that creates a table, we run tests to check the output data. These tests verify things like whether ids are unique and counts are positive. If all tests pass, the model output is valid and ready for deployment. If any test fails, we stop and fix the model or data. This process helps catch errors early and ensures data quality.