Testing model outputs in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a simple aggregation test in dbt

Given a dbt model that calculates total sales per region, what will be the output of this test query?

dbt

select region, sum(sales) as total_sales from sales_data group by region order by region;

A [{"region": "East", "total_sales": 1500}, {"region": "West", "total_sales": 2000}]

B [{"region": "East", "total_sales": 3500}, {"region": "West", "total_sales": 3500}]

C [{"region": "East", "total_sales": 2000}, {"region": "West", "total_sales": 1500}]

D [{"region": "East", "total_sales": null}, {"region": "West", "total_sales": 2000}]

💡 Hint

Sum sales grouped by region and order by region alphabetically.

The query sums sales for each region and orders results alphabetically by region. The correct totals are 1500 for East and 2000 for West.

❓ data_output

intermediate

Result of a null value test in a dbt model

What will be the output of this test that checks for null values in the 'customer_id' column?

dbt

select count(*) as null_count from customers where customer_id is null;

A [{"null_count": 0}]

B [{"null_count": 5}]

C [{"null_count": null}]

D SyntaxError

💡 Hint

Count rows where customer_id is null.

The query counts how many rows have null customer_id. The correct count is zero, meaning no nulls.

🔧 Debug

advanced

Identify the error in this dbt test SQL

What error will this dbt test SQL raise when run?

dbt

select customer_id, count(*) from orders group by customer_id having count(*) >;

A No error, returns rows

B TypeError

C SyntaxError

D RuntimeError

💡 Hint

Check the HAVING clause syntax.

The HAVING clause is incomplete and missing a value after the > operator, causing a syntax error.

🚀 Application

advanced

Interpreting test results for data freshness in dbt

A dbt test checks if the 'last_updated' timestamp in a model is within the last 24 hours. The test returns 3 rows. What does this mean?

A The test failed due to syntax error

B 3 rows have last_updated within the last 24 hours, data is fresh

C 3 rows have null last_updated values

D 3 rows have last_updated older than 24 hours, indicating stale data

💡 Hint

Tests usually return rows that fail the condition.

The test returns rows that violate the freshness condition, so 3 rows are stale.

🧠 Conceptual

expert

Understanding the impact of test failures on dbt runs

If a dbt test on a model fails during a run, what is the default behavior of dbt regarding the run status?

A The dbt run fails and stops execution immediately

B The dbt run completes successfully but marks the test as failed

C The dbt run ignores test failures and continues silently

D The dbt run retries the test automatically until it passes

💡 Hint

Tests are separate from model builds in dbt.

By default, dbt runs models and tests separately; test failures do not stop the run but are reported.