Why Unit testing dbt models? - Purpose & Use Cases

The Big Idea

What if you could catch data errors automatically before they cause big problems?

The Scenario

Imagine you build a complex data report by writing many SQL queries manually. Every time you change one query, you have to run the whole report and check if the numbers still make sense by hand.

This takes a lot of time and you might miss errors hidden deep in the data.

The Problem

Manually checking data results is slow and tiring. It's easy to overlook mistakes or introduce errors without noticing.

When your data grows or changes, you must repeat the whole process, which wastes time and causes frustration.

The Solution

Unit testing dbt models means writing small automated checks that run every time you build your data models.

These tests quickly catch errors and ensure your data is correct without manual effort.

Before vs After

✗ Before

SELECT * FROM sales WHERE date = '2023-01-01'; -- Manually check if results look right

✓ After

tests:
  - unique:
      column_name: id
  - not_null:
      column_name: sales_amount

What It Enables

Unit testing dbt models lets you trust your data pipelines and confidently make changes without breaking reports.

Real Life Example

A company uses dbt to transform sales data. With unit tests, they catch missing sales amounts or duplicate IDs early, avoiding wrong business decisions.

Key Takeaways

Manual data checks are slow and error-prone.

Unit tests automate data quality checks in dbt models.

This saves time and improves trust in data results.

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