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.

Practice

1. Why is advanced testing important in dbt for data quality?

easy

A. It makes the data load faster.

B. It reduces the size of the database.

C. It finds subtle data issues that simple tests might miss.

D. It automatically fixes all data errors.

Solution

1. Step 1: Understand the role of advanced testing

   Advanced testing is designed to catch hidden or subtle data problems that basic tests do not detect.

2. Step 2: Compare with other options

   The other options describe unrelated or incorrect effects of advanced testing.

3. Final Answer:

   It finds subtle data issues that simple tests might miss. -> Option C

4. Quick Check:

   Advanced testing = finds subtle issues [OK]

Hint: Advanced tests catch hidden problems, not speed or size [OK]

Common Mistakes:

• Thinking advanced tests speed up data loading
• Assuming advanced tests reduce database size
• Believing advanced tests fix errors automatically

2. Which of the following is the correct syntax to define a custom test in dbt?

easy

A. tests:
unique_test:
test: unique
column_name: id

B. tests:
- unique_test:
column_name: id

C. tests:
- name: unique_test
test: unique
column_name: id

D. tests:
- name: unique_test
test: unique
column_name: id
severity: error

Solution

1. Step 1: Review dbt test YAML syntax

   Custom tests in dbt are defined with a name, test type, column, and optional severity in a list format.

2. Step 2: Identify correct structure

   tests: - name: unique_test test: unique column_name: id severity: error correctly uses a list item with name, test, column_name, and severity keys.

3. Final Answer:

   tests:\n - name: unique_test\n test: unique\n column_name: id\n severity: error -> Option D

4. Quick Check:

   Correct YAML list with keys = tests: - name: unique_test test: unique column_name: id severity: error [OK]

Hint: Custom tests need name, test, column, and optional severity [OK]

Common Mistakes:

• Using incorrect YAML indentation
• Missing the 'name' key for the test
• Not using a list for multiple tests

3. Given this dbt test SQL snippet, what will be the output if the table has duplicate user_ids?

SELECT user_id, COUNT(*)
FROM {{ ref('users') }}
GROUP BY user_id
HAVING COUNT(*) > 1

medium

A. A list of user_ids that appear more than once

B. An error because COUNT(*) cannot be used with GROUP BY

C. An empty result if no duplicates exist

D. A count of all user_ids in the table

Solution

1. Step 1: Understand the SQL query

   The query groups rows by user_id and selects those with count greater than 1, meaning duplicates.

2. Step 2: Determine output when duplicates exist

   If duplicates exist, the query returns user_ids with more than one occurrence.

3. Final Answer:

   A list of user_ids that appear more than once -> Option A

4. Quick Check:

   GROUP BY + HAVING COUNT(*) > 1 = duplicates list [OK]

Hint: HAVING COUNT(*) > 1 finds duplicates [OK]

Common Mistakes:

• Thinking COUNT(*) can't be used with GROUP BY
• Expecting total count instead of filtered duplicates
• Confusing empty result with error

4. You wrote a dbt test to check for nulls in the email column, but it always passes even when nulls exist. What is the likely issue?

medium

A. The test is running on the wrong model reference.

B. The test is using COUNT(email) instead of COUNT(*)

C. The test is written as a schema test instead of data test.

D. The test SQL is missing a WHERE clause to filter nulls.

Solution

1. Step 1: Analyze why nulls are not detected

   COUNT(email) only counts non-null values, so tests relying on it will miss nulls and pass incorrectly.

2. Step 2: Check other options

   The other options cause different issues, like running on wrong data or incorrect test type.

3. Final Answer:

   The test is using COUNT(email) instead of COUNT(*) -> Option B

4. Quick Check:

   COUNT(email) ignores nulls [OK]

Hint: COUNT(column) ignores nulls [OK]

Common Mistakes:

• Using COUNT(column) which ignores nulls
• Confusing schema and data tests
• Referencing wrong model in test

5. You want to create an advanced dbt test that checks if the order_date is always before the ship_date. Which approach best catches subtle data issues?

hard

A. Write a SQL test that selects rows where order_date >= ship_date.

B. Use a built-in unique test on order_date.

C. Create a schema test to check order_date is not null.

D. Add a test that counts total rows in the orders table.

Solution

1. Step 1: Define the data rule to test

   The rule is order_date must be before ship_date, so invalid rows have order_date >= ship_date.

2. Step 2: Choose the test that finds violations

   A SQL test selecting rows where order_date >= ship_date will catch subtle errors where dates are reversed or equal.

3. Final Answer:

   Write a SQL test that selects rows where order_date >= ship_date. -> Option A

4. Quick Check:

   Test selects invalid date order rows [OK]

Hint: Test selects rows violating the date order rule [OK]

Common Mistakes:

• Using unique test which doesn't check date order
• Checking only for nulls misses order errors
• Counting rows doesn't validate date logic