Why advanced testing catches subtle data issues in dbt - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why do advanced tests catch subtle data issues?

In data projects, simple tests check for obvious problems like missing values. Advanced tests go deeper. Why do advanced tests catch subtle data issues better?

A Because they check complex patterns and relationships in data beyond basic errors.

B Because they only check if data files exist and have correct names.

C Because they ignore data quality and focus on performance speed.

D Because they only verify the number of rows without checking content.

💡 Hint

Think about what subtle data issues might look like beyond missing or null values.

Advanced tests look for complex data patterns, inconsistencies, or unexpected changes that simple tests miss. This helps catch subtle issues early.

❓ Predict Output

intermediate

Output of a dbt test on unique user IDs

Given this dbt test SQL snippet, what is the output if the 'user_id' column has duplicates?

dbt

select user_id, count(*) as count from {{ ref('users') }} group by user_id having count > 1

A A syntax error due to missing parentheses.

B A list of user_id values that appear more than once.

C An empty result set because duplicates are ignored.

D A count of all user_id values including unique ones.

💡 Hint

Look at the GROUP BY and HAVING clause to understand what rows are returned.

The query groups by user_id and returns only those with count greater than 1, so duplicates are listed.

❓ data_output

advanced

Result of an advanced dbt test for data drift detection

Consider a dbt test that compares the average order amount this month to last month and flags if the difference is over 20%. What would the test output be if the average changed from 100 to 130?

dbt

with last_month as (select avg(order_amount) as avg_last from orders where order_date between '2024-04-01' and '2024-04-30'), this_month as (select avg(order_amount) as avg_this from orders where order_date between '2024-05-01' and '2024-05-31') select case when abs(avg_this - avg_last)/avg_last > 0.2 then 'fail' else 'pass' end as test_result from last_month, this_month

A An error because the date ranges overlap.

B 'pass' because the average increased, which is good.

C 'fail' because the average decreased by 30%.

D 'fail' because the increase is 30%, which is more than 20%.

💡 Hint

Calculate the percentage change between 100 and 130.

The change is (130-100)/100 = 0.3 or 30%, which is greater than 20%, so the test fails.

🔧 Debug

advanced

Identify the error in this dbt test SQL

Find the error in this dbt test SQL that checks for nulls in the 'email' column:

select * from {{ ref('customers') }} where email = NULL

A The test will return no rows because 'email = NULL' is always false; use 'email IS NULL' instead.

B The test will return all rows because NULL matches everything.

C The test has a syntax error due to missing quotes around NULL.

D The test will run correctly and find null emails.

💡 Hint

Remember how SQL treats NULL comparisons.

In SQL, 'email = NULL' is never true. To check for nulls, use 'email IS NULL'.

🚀 Application

expert

Choosing the best advanced test for subtle data issues

You want to detect subtle data issues like unexpected shifts in customer demographics over time. Which advanced dbt test approach is best?

A Implement a test that counts total rows in the customer table each day.

B Implement a test that only checks for missing values in demographic fields.

C Implement a test that compares statistical distributions (e.g., mean, median) of demographic fields month-over-month.

D Implement a test that verifies the presence of primary key constraints.

💡 Hint

Think about how to detect subtle changes, not just obvious errors.

Comparing statistical distributions over time helps catch subtle shifts in data that simple tests miss.