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.