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.

Practice

1. What is the main purpose of testing model outputs in dbt?

easy

A. To ensure the data is accurate and reliable

B. To speed up the data loading process

C. To create new tables automatically

D. To delete old data from the database

Solution

1. Step 1: Understand the goal of testing in dbt

   Testing checks if the data produced by models is correct and trustworthy.

2. Step 2: Identify the main benefit of testing outputs

   Accurate and reliable data helps users make good decisions and trust reports.

3. Final Answer:

   To ensure the data is accurate and reliable -> Option A

4. Quick Check:

   Testing = Accurate data [OK]

Hint: Testing checks data correctness, not speed or deletion [OK]

Common Mistakes:

• Thinking tests speed up loading
• Confusing testing with table creation
• Assuming tests delete data

2. Which of the following is the correct syntax to define a uniqueness test on a column user_id in a dbt model's schema.yml file?

easy

A. - model: users
columns:
- name: user_id
tests:
- unique

B. - name: users
columns:
- name: user_id
test: unique

C. - name: users
columns:
- user_id
tests:
- unique

D. - name: users
columns:
- name: user_id
tests:
- unique

Solution

1. Step 1: Check the correct key for model name

   The key to specify the model is name, not model.

2. Step 2: Verify column and test syntax

   Each column uses name and tests are listed under tests as a list.

3. Final Answer:

   - name: users columns: - name: user_id tests: - unique -> Option D

4. Quick Check:

   Correct schema.yml syntax = - name: users columns: - name: user_id tests: - unique [OK]

Hint: Use 'name' for model and column, 'tests' as list [OK]

Common Mistakes:

• Using 'model' instead of 'name' for model
• Writing 'test' instead of 'tests'
• Omitting 'name' for column

3. Given this dbt test defined in schema.yml for the model orders:

- name: orders
  columns:
    - name: order_id
      tests:
        - unique
        - not_null

What will happen if the orders table has two rows with the same order_id and one row with order_id as NULL when you run dbt test?

medium

A. The test will fail because of duplicate and NULL values in order_id

B. The test will pass because only one test can fail at a time

C. The test will fail only for duplicate values, NULLs are ignored

D. The test will pass because NULLs are allowed in unique tests

Solution

1. Step 1: Understand the tests applied

   The tests are unique and not_null on order_id.

2. Step 2: Analyze the data issues

   Two rows have the same order_id (violates uniqueness) and one row has NULL order_id (violates not_null).

3. Final Answer:

   The test will fail because of duplicate and NULL values in order_id -> Option A

4. Quick Check:

   Duplicates + NULLs = test fail [OK]

Hint: Both unique and not_null must pass for success [OK]

Common Mistakes:

• Assuming NULLs are allowed in unique tests
• Thinking only one test failure causes pass
• Ignoring NULL violation in not_null test

4. You wrote this test in your schema.yml file:

- name: customers
  columns:
    - name: email
      tests:
        - unique
        - not_null

But when you run dbt test, you get an error saying Invalid test configuration. What is the likely cause?

medium

A. The test name should be unique and not_null without dashes

B. The indentation of the tests list is incorrect

C. The model name should be under models key in schema.yml

D. The email column does not exist in the model

Solution

1. Step 1: Check the structure of schema.yml

   Tests must be defined under the models: key in schema.yml.

2. Step 2: Identify missing models: key

   The snippet misses the models: root key, causing invalid configuration.

3. Final Answer:

   The model name should be under models key in schema.yml -> Option C

4. Quick Check:

   Missing 'models:' key = config error [OK]

Hint: Always start schema.yml tests under 'models:' key [OK]

Common Mistakes:

• Removing dashes from test names
• Incorrect indentation of tests list
• Not placing model under 'models:'

5. You want to test that the status column in your transactions model only contains the values 'pending', 'completed', or 'failed'. Which test definition in schema.yml correctly enforces this?

hard

A. - name: transactions
columns:
- name: status
tests:
- values_in: ['pending', 'completed', 'failed']

B. - name: transactions
columns:
- name: status
tests:
- accepted_values:
values: ['pending', 'completed', 'failed']

C. - name: transactions
columns:
- name: status
tests:
- accepted_values: ['pending', 'completed', 'failed']

D. - name: transactions
columns:
- name: status
tests:
- unique
- not_null

Solution

1. Step 1: Identify the correct test for allowed values

   The accepted_values test checks if column values are in a list.

2. Step 2: Check correct syntax for accepted_values

   The test requires a dictionary with key values listing allowed values.

3. Final Answer:

   - name: transactions columns: - name: status tests: - accepted_values: values: ['pending', 'completed', 'failed'] -> Option B

4. Quick Check:

   accepted_values with 'values' key = - name: transactions columns: - name: status tests: - accepted_values: values: ['pending', 'completed', 'failed'] [OK]

Hint: Use accepted_values with 'values' list for allowed values [OK]

Common Mistakes:

• Using unique or not_null instead of accepted_values
• Omitting 'values:' key under accepted_values
• Using wrong test name like values_in