dbt-utils package tests - Step-by-Step Execution

Concept Flow - dbt-utils package tests

Write test config in schema.yml

↓

Run dbt test command

↓

dbt-utils runs test SQL

↓

Test queries data in warehouse

↓

Results: Pass or Fail

↓

Review test output in CLI or logs

This flow shows how dbt-utils tests are configured, run, and produce pass/fail results.

Execution Sample

dbt

models:
  - name: my_model
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - id
            - email

This YAML config runs a dbt-utils test to check unique combinations of 'id' and 'email' columns in 'my_model'.

Execution Table

Step	Action	Test SQL Generated	Test Query Result	Test Outcome
1	dbt reads schema.yml and finds test config	N/A	N/A	N/A
2	dbt compiles test SQL for unique_combination_of_columns	SELECT id, email, COUNT(*) FROM my_model GROUP BY id, email HAVING COUNT(*) > 1	N/A	N/A
3	dbt runs test SQL against data warehouse	N/A	0 rows returned	Pass
4	dbt reports test result in CLI	N/A	N/A	Test Passed

💡 Test stops after running SQL and getting zero rows, meaning no duplicates found.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
test_sql	None	SELECT id, email, COUNT(*) FROM my_model GROUP BY id, email HAVING COUNT(*) > 1	Executed	Executed
query_result	None	None	0 rows	0 rows
test_outcome	None	None	Pass	Pass

Key Moments - 3 Insights

Why does the test SQL use GROUP BY and HAVING COUNT(*) > 1?

The test checks for duplicates by grouping rows on the columns and finding groups with more than one row, as shown in execution_table step 2.

What does zero rows returned from the test query mean?

Zero rows means no duplicates were found, so the test passes, as shown in execution_table step 3.

How does dbt know which model to test?

dbt reads the model name from schema.yml under 'models' and applies the test to that model, as in execution_table step 1.

Visual Quiz - 3 Questions

Look at the execution_table, what SQL does dbt generate for the unique_combination_of_columns test?

A SELECT * FROM my_model WHERE id IS NULL

B SELECT id, email, COUNT(*) FROM my_model GROUP BY id, email HAVING COUNT(*) > 1

C SELECT COUNT(*) FROM my_model

D SELECT DISTINCT id, email FROM my_model

💡 Hint

Check the 'Test SQL Generated' column in execution_table step 2.

At which step does dbt determine the test outcome?

A Step 1

B Step 3

C Step 4

D Step 2

💡 Hint

Look at the 'Test Outcome' column in execution_table; final result is reported in step 4.

If the test query returned rows, what would the test outcome be?

A Fail

B Pending

C Pass

D Skipped

💡 Hint

Refer to execution_table step 3 where zero rows means Pass; rows returned means Fail.

Concept Snapshot

dbt-utils tests are configured in schema.yml under models.
Tests generate SQL queries to check data conditions.
Run 'dbt test' to execute tests.
Test passes if query returns zero rows (no issues).
Test fails if query returns rows (issues found).

Full Transcript

This visual execution shows how dbt-utils package tests work step-by-step. First, you write test configurations in schema.yml specifying the model and test type, such as unique_combination_of_columns. When you run 'dbt test', dbt reads this config and generates SQL to check the data. For the unique combination test, it groups data by specified columns and finds duplicates by counting rows greater than one. The SQL runs in the data warehouse. If no duplicates are found (zero rows returned), the test passes. The results are shown in the CLI. Variables like test_sql, query_result, and test_outcome change as the test runs. Common confusions include why grouping is used, what zero rows mean, and how dbt knows which model to test. The quizzes check understanding of SQL generated, when outcome is determined, and what results mean. The snapshot summarizes the key points for quick reference.