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.