dbt-utils package tests - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of dbt_utils.equality test on two tables

Given two tables table_a and table_b with identical columns and data, what will be the result of running the dbt_utils.equality test comparing these tables?

dbt

select * from {{ dbt_utils.equality('table_a', 'table_b') }}

A Test passes with no failures

B Test fails showing all rows as mismatched

C Syntax error due to missing parameters

D Test returns an empty result set but marked as failed

💡 Hint

Think about what happens when two tables have exactly the same data.

The dbt_utils.equality test compares two tables row by row. If they are identical, the test passes with no failures.

❓ data_output

intermediate

Result of dbt_utils.unique_combination_of_columns test

You run the dbt_utils.unique_combination_of_columns test on a table with columns user_id and order_id. The table has duplicate rows where user_id and order_id repeat. What will the test output show?

dbt

select * from {{ dbt_utils.unique_combination_of_columns('orders', ['user_id', 'order_id']) }}

A Only unique rows with no duplicates

B Rows that have duplicate combinations of user_id and order_id

C All rows in the table

D An error because the columns list is not a string

💡 Hint

The test finds duplicates in the specified columns.

The test returns rows where the combination of specified columns is not unique, showing duplicates.

🔧 Debug

advanced

Identify the error in dbt_utils.not_null test usage

What error will occur when running this test?

select * from {{ dbt_utils.not_null('customers', 'customer_id, email') }}

dbt

select * from {{ dbt_utils.not_null('customers', 'customer_id, email') }}

A Test returns rows with nulls in customer_id only

B Test passes with no errors

C Runtime error because table does not exist

D Compilation error due to passing columns as a single string instead of a list

💡 Hint

Check the expected data type for the columns parameter.

The columns parameter must be a list of strings, not a single string with commas.

🚀 Application

advanced

Using dbt_utils.expression_is_true to test a condition

You want to test that all rows in the sales table have amount > 0. Which is the correct way to write this test using dbt_utils.expression_is_true?

A select * from {{ dbt_utils.expression_is_true('sales', amount > 0) }}

B select * from {{ dbt_utils.expression_is_true('sales', 'amount >= 0') }}

C select * from {{ dbt_utils.expression_is_true('sales', 'amount > 0') }}

D select * from {{ dbt_utils.expression_is_true('sales', ['amount > 0']) }}

💡 Hint

The expression parameter must be a string representing the condition.

The expression must be passed as a string. Option C correctly passes 'amount > 0' as a string.

🧠 Conceptual

expert

Understanding the output of dbt_utils.surrogate_key test

You use dbt_utils.surrogate_key macro to generate a surrogate key from columns first_name and last_name. What is the expected output type and behavior of this macro?

A It returns a hashed string combining the specified columns, suitable as a unique key

B It returns a numeric auto-incrementing ID for each row

C It returns a concatenated string of the columns separated by underscores

D It returns a boolean indicating if the columns are unique

💡 Hint

Think about what a surrogate key is and how hashing helps.

The surrogate_key macro creates a hash of the specified columns to produce a unique string key.