Running tests with dbt test - Step-by-Step Execution

Concept Flow - Running tests with dbt test

Write tests in schema.yml

↓

Run 'dbt test' command

↓

dbt executes tests on data

↓

Tests pass?

No→Show errors and failing rows

Yes↓

All tests pass

↓

End

You write tests in your dbt project, run 'dbt test', and dbt checks your data, showing results or errors.

Execution Sample

dbt

dbt test
-- runs all tests defined in schema.yml
-- checks data quality and constraints

This command runs all tests defined in your dbt project to validate your data.

Execution Table

Step	Action	Test Type	Test Target	Result	Output
1	Start dbt test	N/A	N/A	Running	Starting tests...
2	Run unique test	unique	column: user_id	Pass	No duplicates found
3	Run not_null test	not_null	column: email	Fail	Found 3 null values
4	Run accepted_values test	accepted_values	column: status	Pass	All values valid
5	Summary	N/A	N/A	Fail	1 test failed: not_null on email
6	End	N/A	N/A	Stopped	Fix errors and rerun

💡 Test run ends because one or more tests failed, showing errors for correction.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Tests Run	0	1	2	3	3
Tests Passed	0	1	1	2	2
Tests Failed	0	0	1	1	1
Error Messages	[]	[]	["Found 3 null values in email"]	["Found 3 null values in email"]	["Found 3 null values in email"]

Key Moments - 2 Insights

Why does dbt stop the test run after a failure?

dbt runs all tests but reports failures at the end; the process shows all results but highlights failures for you to fix, as seen in step 5 of the execution_table.

What does a 'not_null' test check?

It checks that no values in the specified column are null. In the execution_table step 3, the test failed because 3 null values were found in the email column.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what was the test result for the 'not_null' test on 'email'?

A Fail

B Skipped

C Pass

D Running

💡 Hint

Check the 'Result' column in row 3 of the execution_table.

At which step does dbt report the summary of all tests?

A Step 2

B Step 3

C Step 5

D Step 6

💡 Hint

Look for the row with 'Summary' in the 'Action' column in the execution_table.

If the 'not_null' test on 'email' passed, how would the 'Tests Failed' variable change after step 3?

A It would increase by 1

B It would remain 0

C It would remain 1

D It would decrease by 1

💡 Hint

Check the variable_tracker for 'Tests Failed' after step 3 and imagine no failures occurred.

Concept Snapshot

dbt test runs data quality checks defined in schema.yml
Common tests: unique, not_null, accepted_values
Run with 'dbt test' command
Reports pass/fail for each test
Fix failures and rerun tests

Full Transcript

Running tests with dbt test involves writing tests in your dbt project files, then executing the 'dbt test' command. This command runs all defined tests such as uniqueness, not-null constraints, and accepted values on your data. The process outputs results step-by-step, showing which tests pass or fail. If any test fails, dbt reports the errors so you can fix them. After fixing, you rerun the tests to ensure data quality. This visual trace shows the flow from starting tests, running each test, reporting results, and ending with a summary.

Practice

1. What is the main purpose of running dbt test in a dbt project?

easy

A. To deploy the dbt project to production

B. To build new data models from raw data

C. To check data quality and find errors in your data models

D. To generate documentation for your data models

Solution

1. Step 1: Understand the role of dbt test

   dbt test runs tests defined in your project to check data quality and catch errors.

2. Step 2: Differentiate from other dbt commands

   Building models is done with dbt run, deployment is outside dbt test, and documentation is generated with dbt docs.

3. Final Answer:

   To check data quality and find errors in your data models -> Option C

4. Quick Check:

   dbt test = data quality checks [OK]

Hint: Remember: test = check data quality, run = build models [OK]

Common Mistakes:

• Confusing dbt test with dbt run
• Thinking dbt test deploys models
• Assuming dbt test generates docs

2. Which of the following is the correct command to run tests only on a specific model named customers?

easy

A. dbt test --select customers

B. dbt run --models customers

C. dbt test --models customers

D. dbt test --only customers

Solution

1. Step 1: Identify the correct flag for running tests on specific models

   The flag --select is used with dbt test to specify which models to test.

2. Step 2: Check other options for correctness

   dbt run builds models, not tests. --models is not a valid flag for dbt test. --only is not a valid flag.

3. Final Answer:

   dbt test --select customers -> Option A

4. Quick Check:

   Use --select to target tests [OK]

Hint: Use --select flag to run tests on specific models [OK]

Common Mistakes:

• Using dbt run instead of dbt test
• Using invalid flags like --models or --only
• Confusing --select with other flags

3. Given this schema.yml test definition:

models:
  - name: orders
    tests:
      - unique:
          column_name: order_id
      - not_null:
          column_name: order_date

What will dbt test check for the orders model?

medium

A. It checks that order_id is unique and order_date has no null values

B. It checks that order_id has no null values and order_date is unique

C. It checks that both order_id and order_date are unique

D. It checks that both order_id and order_date have no null values

Solution

1. Step 1: Read the test types for each column

   The test unique applies to order_id, ensuring no duplicates. The test not_null applies to order_date, ensuring no missing values.

2. Step 2: Match tests to their meaning

   unique means no duplicates; not_null means no nulls. So the checks are: order_id is unique and order_date has no null values.

3. Final Answer:

   It checks that order_id is unique and order_date has no null values -> Option A

4. Quick Check:

   unique = no duplicates, not_null = no nulls [OK]

Hint: unique = no duplicates, not_null = no nulls [OK]

Common Mistakes:

• Mixing up unique and not_null tests
• Assuming both columns have the same test
• Ignoring the column_name key in test definitions

4. You run dbt test but get an error: Compilation Error: Could not find test 'uniquee'. What is the likely cause?

medium

A. The test passed with no errors

B. The model name is incorrect

C. The database connection is missing

D. A typo in the test name in schema.yml

Solution

1. Step 1: Analyze the error message

   The error says it cannot find test 'uniquee', which looks like a misspelled test name.

2. Step 2: Identify common causes of compilation errors

   Typos in test names in schema.yml cause dbt to fail to find the test. Model name or connection errors produce different messages.

3. Final Answer:

   A typo in the test name in schema.yml -> Option D

4. Quick Check:

   Compilation errors often mean typos [OK]

Hint: Check spelling of test names in schema.yml [OK]

Common Mistakes:

• Ignoring typo errors and rerunning blindly
• Assuming connection issues cause compilation errors
• Confusing model name errors with test name errors

5. You want to ensure that the email column in your users model is unique and not null. You also want to run tests only on this model. Which schema.yml snippet and command combination is correct?

hard

A. models:
- name: users
tests:
- unique
- not_nullCommand: dbt test --models users

B. models:
- name: users
columns:
- name: email
tests:
- unique
- not_nullCommand: dbt test --select users

C. models:
- name: users
columns:
- name: email
tests:
- unique
- not_nullCommand: dbt run --models users

D. models:
- name: users
tests:
- unique
- not_nullCommand: dbt test --select users

Solution

1. Step 1: Identify correct test syntax in schema.yml

   Tests on columns are defined under columns with name and tests list.

   models:
     - name: users
       columns:
         - name: email
           tests:
             - unique
             - not_null

   
   Command: dbt test --select users shows the correct format.

2. Step 2: Choose the correct command to run tests on the users model

   dbt test --select users runs tests only on the users model.

   models:
     - name: users
       columns:
         - name: email
           tests:
             - unique
             - not_null

   
   Command: dbt test --select users uses this command correctly.

3. Final Answer:

   Option B with column-level tests and dbt test --select users -> Option B

4. Quick Check:

   Column tests + --select flag = correct [OK]

Hint: Define tests under columns, run with --select flag [OK]

Common Mistakes:

• Defining tests directly under model without column_name (invalid syntax)
• Using dbt run instead of dbt test
• Using incorrect flags like --models