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.

Practice

1. What is the main purpose of using dbt-utils package tests in your data models?

easy

A. To write complex SQL queries faster

B. To speed up data loading processes

C. To check data quality by validating conditions like uniqueness and nulls

D. To create visual dashboards automatically

Solution

1. Step 1: Understand the role of dbt-utils tests

   dbt-utils tests are designed to check data quality by validating conditions such as uniqueness, null values, and accepted values.

2. Step 2: Compare options with the purpose

   Only To check data quality by validating conditions like uniqueness and nulls describes checking data quality, which matches the purpose of dbt-utils tests.

3. Final Answer:

   To check data quality by validating conditions like uniqueness and nulls -> Option C

4. Quick Check:

   dbt-utils tests = data quality checks [OK]

Hint: Tests in dbt-utils check data quality, not speed or visuals [OK]

Common Mistakes:

• Thinking dbt-utils speeds up data loading
• Confusing tests with dashboard creation
• Assuming tests write SQL queries automatically

2. Which of the following is the correct syntax to add a uniqueness test using dbt-utils in a model YAML file?

easy

A. tests:
- dbt_utils.unique:
column_name: column_name

B. tests: - dbt_utils.unique: ['column_name']

C. tests: - dbt_utils.unique - column_name

D. tests: - dbt_utils.unique: {column_name}

Solution

1. Step 1: Recall dbt-utils test syntax in YAML

   The correct syntax uses column_name parameter under the test, like dbt_utils.unique: column_name: column_name.

2. Step 2: Check each option format

   tests: - dbt_utils.unique: column_name: column_name correctly uses the column_name key-value pair, matching dbt-utils documentation.

3. Final Answer:

   tests: - dbt_utils.unique: column_name: column_name -> Option A

4. Quick Check:

   Unique test syntax = column_name key [OK]

Hint: Use column_name: column_name for dbt_utils.unique [OK]

Common Mistakes:

• Broken YAML with multiple dashes
• Using list ['column_name'] instead of column_name key
• Using curly braces for parameters

3. Given this YAML snippet in a dbt model file:

tests:
  - dbt_utils.not_null:
      column_name: user_id
  - dbt_utils.unique:
      column_name: user_id

What will happen when you run dbt test?

medium

A. dbt will throw a syntax error due to wrong YAML format

B. dbt will check that user_id has no nulls and is unique

C. dbt will only check for nulls in user_id

D. dbt will ignore these tests and run default tests

Solution

1. Step 1: Analyze YAML test syntax

   The syntax uses column_name: user_id under dbt_utils tests, which is the correct format for not_null and unique tests.

2. Step 2: Identify dbt behavior

   This correct format will cause dbt to run both tests: no nulls and uniqueness on user_id.

3. Final Answer:

   dbt will check that user_id has no nulls and is unique -> Option B

4. Quick Check:

   Correct YAML format = runs both tests [OK]

Hint: dbt-utils tests use column_name: col correctly [OK]

Common Mistakes:

• Using lists [- user_id] instead of column_name key
• Wrong YAML indentation
• Missing quotes around column_name value

4. You wrote this test in your model YAML:

tests:
  - dbt_utils.accepted_values:
      column_name: ['status']
      values: ['active', 'inactive']

But when running dbt test, it fails with an error. What is the likely problem?

medium

A. The column_name should be a string, not a list

B. The values parameter should be a list under accepted_values, not a key-value pair

C. The accepted_values test requires a where clause

D. The values list must be a string, not a list

Solution

1. Step 1: Review dbt-utils accepted_values test syntax

   The accepted_values test expects column_name as a string identifier, not a list.

2. Step 2: Identify the YAML structure issue

   column_name: ['status'] passes a list instead of string 'status', causing an error.

3. Final Answer:

   The column_name should be a string, not a list -> Option A

4. Quick Check:

   accepted_values column_name = string [OK]

Hint: column_name must be string for accepted_values test [OK]

Common Mistakes:

• Passing column_name as list instead of string
• Using string instead of list for values
• Adding unnecessary where clause

5. You want to ensure a column email in your users model has no nulls and all values are unique using dbt-utils tests. Which YAML snippet correctly applies both tests?

hard

A. tests:
- dbt_utils.not_null:
email
- dbt_utils.unique:
email

B. tests:
- dbt_utils.not_null:
columns: ['email']
- dbt_utils.unique:
columns: ['email']

C. tests:
- dbt_utils.not_null:
- email
- dbt_utils.unique:
- email

D. tests:
- dbt_utils.not_null:
column_name: email
- dbt_utils.unique:
column_name: email

Solution

1. Step 1: Recall correct dbt-utils test syntax for columns

   dbt-utils not_null and unique tests expect column_name: email under the test name.

2. Step 2: Evaluate each YAML snippet

   Snippet with column_name: email for both tests correctly uses the required key-value format. The snippet missing colons after tests is invalid YAML. Using a list - email passes incorrect config type. Using columns is invalid key.

3. Final Answer:

   tests: - dbt_utils.not_null: column_name: email - dbt_utils.unique: column_name: email -> Option D

4. Quick Check:

   column_name: [col] under test name [OK]

Hint: Use column_name: email for not_null and unique tests [OK]

Common Mistakes:

• Omitting colon and using scalar email
• Using list - email for single-column tests
• Using wrong key columns instead of column_name