Unit testing dbt models - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a dbt test SQL query

Given this dbt test SQL model, what will be the output if the test fails?

select id from {{ ref('orders') }} where order_date < '2020-01-01'

dbt

select id from {{ ref('orders') }} where order_date < '2020-01-01'

A A count of rows instead of the actual rows

B An empty table because dbt tests always return no rows

C A syntax error because of incorrect use of ref function

D A table with all order ids where order_date is before 2020-01-01

💡 Hint

Think about what a failing test returns in dbt.

In dbt, tests return rows that fail the condition. Here, it returns all orders before 2020-01-01, so the output is those rows.

🧠 Conceptual

intermediate

Purpose of schema tests in dbt

What is the main purpose of schema tests in dbt?

A To schedule dbt runs automatically

B To check data quality by validating column values and constraints

C To create new tables in the database

D To run complex data transformations

💡 Hint

Think about what schema tests validate in your data.

Schema tests in dbt check for data quality issues like uniqueness, null values, and referential integrity on columns.

🔧 Debug

advanced

Identify the error in this dbt test SQL

This dbt test SQL is intended to check for nulls in the user_id column. What error will it raise?

select * from {{ ref('users') }} where user_id = null

A Runtime error because user_id column does not exist

B SyntaxError due to incorrect use of ref function

C No error, but returns no rows because 'user_id = null' is always false

D Returns all rows because null comparison is ignored

💡 Hint

Remember how SQL treats comparisons with null.

In SQL, 'user_id = null' always evaluates to false, so the query returns no rows but does not raise an error.

❓ data_output

advanced

Result of a custom dbt test with aggregation

Consider this custom dbt test SQL:

select customer_id, count(*) as order_count from {{ ref('orders') }} group by customer_id having count(*) < 2

What does this test output?

A Customers with fewer than 2 orders

B All customers with their total order counts

C An error because aggregation is not allowed in tests

D Customers with exactly 2 orders

💡 Hint

Look at the HAVING clause and what it filters.

The test returns customers who have less than 2 orders, showing those that fail the minimum order count condition.

🚀 Application

expert

Choosing the best test for referential integrity in dbt

You want to ensure that every order in the 'orders' model has a matching customer in the 'customers' model. Which dbt test should you use?

A A relationships test from orders.customer_id to customers.id

B A not_null test on orders.customer_id

C A uniqueness test on orders.customer_id

D A custom SQL test counting orders without customers

💡 Hint

Think about how dbt tests foreign key relationships.

The relationships test checks that every value in orders.customer_id exists in customers.id, enforcing referential integrity.

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