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.

Practice

1. What is the main purpose of unit testing in dbt models?

easy

A. To check data models for correctness and quality

B. To create new data models from scratch

C. To deploy dbt projects to production

D. To write SQL queries faster

Solution

1. Step 1: Understand unit testing in dbt

   Unit testing in dbt is designed to verify that data models behave as expected and contain correct data.

2. Step 2: Identify the main goal

   The main goal is to check correctness and quality of data models, not to create or deploy them.

3. Final Answer:

   To check data models for correctness and quality -> Option A

4. Quick Check:

   Unit testing = correctness and quality check [OK]

Hint: Unit tests check data correctness, not creation or deployment [OK]

Common Mistakes:

• Confusing testing with model creation
• Thinking tests deploy models
• Assuming tests speed up SQL writing

2. Which of the following is the correct syntax to define a unique test on a column in a dbt YAML file?

easy

A. tests:
- unique: column_name

B. tests: unique: column_name

C. tests: - unique: column_name

D. tests:
unique: - column_name

Solution

1. Step 1: Recall YAML test syntax in dbt

   Tests are listed as a YAML list under the 'tests' key, each with a dash and test name.

2. Step 2: Match correct indentation and list format

   tests: - unique: column_name shows 'tests:' followed by a list item '- unique: column_name', which is correct YAML syntax for dbt tests.

3. Final Answer:

   tests: - unique: column_name -> Option A

4. Quick Check:

   YAML lists use dash (-) before items [OK]

Hint: YAML lists need dash (-) before each test [OK]

Common Mistakes:

• Missing dash (-) before test name
• Incorrect indentation
• Placing test name and column incorrectly

3. Given this YAML snippet for a dbt test:

columns:
  - name: user_id
    tests:
      - unique
      - not_null

What will dbt test check for the user_id column?

medium

A. It checks that user_id values are duplicated and null

B. It checks that user_id values are unique and not null

C. It only checks if user_id is unique

D. It only checks if user_id is not null

Solution

1. Step 1: Read the tests listed for user_id

   The YAML shows two tests: 'unique' and 'not_null' for the user_id column.

2. Step 2: Understand what each test does

   'unique' ensures no duplicate values; 'not_null' ensures no missing values.

3. Final Answer:

   It checks that user_id values are unique and not null -> Option B

4. Quick Check:

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

Hint: Multiple tests run together on the same column [OK]

Common Mistakes:

• Thinking tests check opposite conditions
• Assuming only one test runs
• Confusing unique with not_null

4. You wrote this test in your YAML file:

columns:
  - name: order_id
    tests:
      unique
      not_null

But when you run dbt test, it fails with a syntax error. What is the likely cause?

medium

A. Using 'tests' instead of 'test' key

B. Incorrect indentation of 'name' key

C. Missing quotes around test names

D. Missing dash (-) before 'unique' and 'not_null' tests

Solution

1. Step 1: Check YAML list syntax for tests

   Each test under 'tests' must be a list item starting with a dash (-).

2. Step 2: Identify missing dashes

   The snippet shows 'unique' and 'not_null' without dashes, causing YAML syntax error.

3. Final Answer:

   Missing dash (-) before 'unique' and 'not_null' tests -> Option D

4. Quick Check:

   YAML lists need dash (-) before each item [OK]

Hint: Always put dash (-) before each test in YAML list [OK]

Common Mistakes:

• Omitting dash (-) before tests
• Wrong indentation levels
• Confusing 'tests' with 'test' key

5. You want to ensure that the email column in your users model is always unique and never empty. Which YAML test configuration correctly applies these tests and how would you run them?

hard

A. Define in YAML:columns:
- email:
tests:
- unique
- not_nullThen run dbt test

B. Define in YAML:columns:
- name: email
tests:
unique
not_nullThen run dbt run

C. Define in YAML:columns:
- name: email
tests:
- unique
- not_nullThen run dbt test

D. Define in YAML:columns:
- name: email
tests:
- unique
- not_nullThen run dbt run

Solution

1. Step 1: Write correct YAML test syntax

   The tests must be listed as a YAML list with dashes under 'tests' for the 'email' column, as shown in Define in YAML:
   

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

   
   Then run dbt test.

2. Step 2: Choose the correct dbt command to run tests

   To execute tests, you must run dbt test, not dbt run. So this configuration with dbt test is correct.

3. Final Answer:

   Define tests with dashes and run dbt test -> Option C

4. Quick Check:

   YAML list + dbt test command = correct testing [OK]

Hint: Use dashes for tests and run 'dbt test' to execute [OK]

Common Mistakes:

• Running 'dbt run' instead of 'dbt test'
• Incorrect YAML list syntax
• Misnaming column key