Store test failures for analysis in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this SQL query storing test failures?

Consider a dbt test that checks for null values in a column user_id. The following SQL stores failures in a table. What rows will be stored?

dbt

INSERT INTO test_failures (test_name, failed_row_id)
SELECT 'not_null_user_id', id
FROM users
WHERE user_id IS NULL;

A No rows inserted because <code>id</code> is not selected.

B Rows where <code>user_id</code> is NOT NULL, with test name 'not_null_user_id'.

C Rows from <code>users</code> where <code>user_id</code> is NULL, with test name 'not_null_user_id'.

D All rows from <code>users</code> regardless of <code>user_id</code> value.

💡 Hint

Think about the WHERE clause filtering rows with user_id IS NULL.

The query inserts rows where user_id is NULL, labeling them with the test name. This stores only failing rows.

❓ data_output

intermediate

How many rows will be stored after this test failure query?

Given the table orders with 1000 rows, 50 have order_date NULL. The test stores failures with:

INSERT INTO failure_log (test_name, order_id)
SELECT 'not_null_order_date', order_id
FROM orders
WHERE order_date IS NULL;

How many rows will failure_log have after this?

A 1000 rows, all orders regardless of <code>order_date</code>.

B 50 rows, one for each order with NULL <code>order_date</code>.

C 0 rows, because <code>order_date</code> is not selected.

D 950 rows, orders with non-NULL <code>order_date</code>.

💡 Hint

Count rows where order_date IS NULL.

The query inserts one row per order with NULL order_date, so 50 rows are stored.

🔧 Debug

advanced

Why does this query fail to store test failures correctly?

Look at this SQL snippet intended to store failures where email is NULL:

INSERT INTO test_failures (test_name, failed_row_id)
SELECT 'not_null_email', user_id
FROM users
WHERE email = NULL;

Why does it not store any rows?

A Because the <code>SELECT</code> statement is missing a <code>GROUP BY</code> clause.

B Because <code>user_id</code> is not a valid column in <code>users</code>.

C Because the table <code>test_failures</code> does not exist.

D Because <code>email = NULL</code> always returns false; use <code>IS NULL</code> instead.

💡 Hint

Remember how SQL compares NULL values.

In SQL, email = NULL is never true. The correct check is email IS NULL to find NULL values.

🚀 Application

advanced

Which SQL query correctly stores failures for a uniqueness test on customer_id?

You want to store rows where customer_id is duplicated in sales. Which query does this correctly?

A

INSERT INTO failure_log (test_name, failed_customer_id)
SELECT 'unique_customer_id', customer_id
FROM sales
GROUP BY customer_id
HAVING COUNT(*) > 1;

B

INSERT INTO failure_log (test_name, failed_customer_id)
SELECT 'unique_customer_id', customer_id
FROM sales
WHERE customer_id IS NULL;

C

INSERT INTO failure_log (test_name, failed_customer_id)
SELECT 'unique_customer_id', customer_id
FROM sales
WHERE COUNT(customer_id) > 1;

D

INSERT INTO failure_log (test_name, failed_customer_id)
SELECT 'unique_customer_id', customer_id
FROM sales
GROUP BY customer_id
HAVING COUNT(customer_id) = 1;

💡 Hint

Think about how to find duplicates using GROUP BY and HAVING.

Option A groups by customer_id and selects those with count greater than 1, correctly identifying duplicates.

🧠 Conceptual

expert

What is the main benefit of storing test failures in a separate table for analysis?

Why do data teams store test failures in dedicated tables instead of just logging errors?

A It allows easy querying and tracking of failing data rows over time for root cause analysis.

B It reduces the size of the main data tables by removing failed rows permanently.

C It automatically fixes the data errors without manual intervention.

D It prevents the data pipeline from running if any test fails.

💡 Hint

Think about how stored failures help in understanding data quality issues.

Storing failures separately helps teams analyze and track problematic data rows easily, aiding debugging and quality improvement.

Practice

1. What does setting store_failures = true in a dbt test configuration do?

easy

A. It sends an email notification when a test fails.

B. It skips the test execution entirely.

C. It deletes the rows that fail the test from the source table.

D. It saves the rows that fail the test for later analysis.

Solution

1. Step 1: Understand the purpose of store_failures

   This setting controls whether dbt saves the rows that fail a test.

2. Step 2: Identify the effect of setting it to true

   When set to true, dbt stores failing rows in a table for analysis.

3. Final Answer:

   It saves the rows that fail the test for later analysis. -> Option D

4. Quick Check:

   store_failures = true means save failing rows [OK]

Hint: Remember: store_failures saves failing rows for review [OK]

Common Mistakes:

• Thinking it skips tests instead of saving failures
• Assuming it deletes data on failure
• Confusing it with notification settings

2. Which of the following is the correct way to enable storing failures in a dbt test config?

easy

A. config(store_failures == true)

B. config(store_failures: true)

C. config(store_failures = true)

D. config(store_failures = "true")

Solution

1. Step 1: Recall dbt config syntax

   dbt uses Python-like syntax with keyword arguments inside config().

2. Step 2: Identify the correct assignment

   The correct syntax uses equals sign and boolean true without quotes.

3. Final Answer:

   config(store_failures = true) -> Option C

4. Quick Check:

   Use equals and boolean true without quotes [OK]

Hint: Use equals sign and true without quotes in config() [OK]

Common Mistakes:

• Using colon instead of equals sign
• Using double equals (==) instead of single equals
• Putting true in quotes making it a string

3. After running a dbt test with store_failures = true, which SQL query will show the failing rows for a model named orders and test unique?

medium

A. SELECT * FROM dbt_test__orders__unique

B. SELECT * FROM orders WHERE test = 'unique' AND status = 'fail'

C. SELECT * FROM dbt_failures.orders_unique

D. SELECT * FROM test_failures.orders_unique

Solution

1. Step 1: Recall naming convention for failure tables

   dbt stores failures in tables named dbt_test__model__test.

2. Step 2: Apply model and test names

   For model 'orders' and test 'unique', the table is dbt_test__orders__unique.

3. Final Answer:

   SELECT * FROM dbt_test__orders__unique -> Option A

4. Quick Check:

   Failure table = dbt_test__model__test [OK]

Hint: Use dbt_test__model__test naming to find failure tables [OK]

Common Mistakes:

• Assuming failure data is in original model table
• Using incorrect table prefixes
• Trying to filter failures in source tables

4. You set store_failures = true in your test config, but after running tests, no failure tables appear. What is the most likely cause?

medium

A. The test did not find any failing rows, so no failure table was created.

B. You must manually create failure tables before running tests.

C. The store_failures setting only works for schema tests, not data tests.

D. Failure tables are stored in a different schema and need to be queried there.

Solution

1. Step 1: Understand when failure tables are created

   Failure tables are only created if the test finds failing rows.

2. Step 2: Check if test found failures

   If no failures, no failure table is generated.

3. Final Answer:

   The test did not find any failing rows, so no failure table was created. -> Option A

4. Quick Check:

   No failures means no failure table [OK]

Hint: No failure rows means no failure table created [OK]

Common Mistakes:

• Thinking failure tables must be pre-created
• Assuming store_failures only works for some test types
• Looking in wrong schema without verifying failures

5. You want to analyze failing rows from multiple tests on the customers model. Which approach best helps you combine all failure data for analysis?

hard

A. Manually export failure tables and merge them outside the database.

B. Create a union query combining all dbt_test__customers__* failure tables.

C. Run each test again with store_failures = false to avoid duplicates.

D. Use the original customers table and filter rows with errors.

Solution

1. Step 1: Identify failure tables for each test

   Each test creates a failure table named dbt_test__customers__testname.

2. Step 2: Combine failure tables using SQL UNION

   Use a UNION query to combine all failure rows from these tables into one result set.

3. Final Answer:

   Create a union query combining all dbt_test__customers__* failure tables. -> Option B

4. Quick Check:

   Union failure tables to analyze all failures together [OK]

Hint: Use UNION on all failure tables for combined analysis [OK]

Common Mistakes:

• Disabling store_failures loses failure data
• Merging outside database is inefficient
• Filtering original table misses failure details