Unit testing dbt models

Introduction

Testing dbt models helps you check if your data transformations work correctly. It finds mistakes early so your data is reliable.

When you create a new dbt model and want to make sure it returns correct data.

When you change an existing model and want to confirm it still works as expected.

When you want to catch data quality issues before they affect reports or dashboards.

When you want to automate checks to save time and avoid manual errors.

Syntax

dbt

version: 2
models:
  - name: your_model_name
    tests:
      - unique:
          column_name: id
      - not_null:
          column_name: id
      - relationships:
          to: ref('other_model')
          field: id

This syntax is written in YAML inside your dbt project under a schema.yml file.

Tests like unique and not_null check basic data quality.

Examples

This example tests that the customers model has unique and non-null values in its primary key.

dbt

models:
  - name: customers
    tests:
      - unique:
          column_name: customer_id
      - not_null:
          column_name: customer_id

This test checks that every customer_id in orders exists in the customers model.

dbt

models:
  - name: orders
    tests:
      - relationships:
          to: ref('customers')
          field: customer_id

This test ensures the category column in products only contains allowed values.

dbt

models:
  - name: products
    tests:
      - accepted_values:
          column_name: category
          values: ['electronics', 'furniture', 'clothing']

Sample Program

This schema.yml file defines tests for the sales model. It checks that sale_id is unique and not null, and that customer_id matches an existing customer.

dbt

version: 2
models:
  - name: sales
    columns:
      - name: sale_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - relationships:
              to: ref('customers')
              field: customer_id

Important Notes

dbt tests run automatically with dbt test command.

You can write custom tests if built-in ones don't cover your needs.

Keep tests small and focused on one thing for easier debugging.

Summary

Testing in dbt checks your data models for correctness and quality.

Tests are defined in YAML files using simple syntax like unique and not_null.

Running dbt test helps catch errors early and keeps data trustworthy.

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