Why advanced testing catches subtle data issues in dbt - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a basic test that checks if a column has any null values.

dbt

version: 2
models:
  - name: customers
    columns:
      - name: email
        tests:
          - [1]

Drag options to blanks, or click blank then click option'

Aunique

Bnot_null

Caccepted_values

Drelationships

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'unique' instead of 'not_null' when checking for missing values.

Confusing 'accepted_values' with null checks.

The not_null test checks if the column contains any null values, which is a basic data quality check.

2fill in blank

medium

Complete the code to add an advanced test that checks if values in a column belong to a specific set.

dbt

version: 2
models:
  - name: orders
    columns:
      - name: status
        tests:
          - accepted_values:
              values: [1]

Drag options to blanks, or click blank then click option'

A['pending', 'shipped', 'cancelled']

B['yes', 'no']

C['open', 'closed']

D['active', 'inactive']

Attempts:

3 left

💡 Hint

Common Mistakes

Using unrelated value lists that don't match order statuses.

Forgetting to format the list as a Python list.

The accepted_values test checks if the column values are within the specified list. Here, order statuses are 'pending', 'shipped', or 'cancelled'.

3fill in blank

hard

Fix the error in the test configuration that causes the test to fail due to incorrect syntax.

dbt

version: 2
models:
  - name: products
    columns:
      - name: price
        tests:
          - [1]: {min: 0}

Drag options to blanks, or click blank then click option'

Aunique

Baccepted_values

Crelationships

Dnot_null

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'accepted_values' with numeric range parameters.

Confusing test types and their parameters.

The not_null test supports no parameters, but the test that supports 'min' parameter is a custom or specific test. The original 'accepted_values' with 'min' is incorrect. Here, 'not_null' is the closest correct standard test, but to check minimum value, a custom test is needed.

4fill in blank

hard

Fill both blanks to create a custom test that checks if the average value of a column is above a threshold.

dbt

version: 2
models:
  - name: sales
    tests:
      - custom_test:
          sql: "SELECT COUNT(*) FROM [1] WHERE [2] <= 100"

Drag options to blanks, or click blank then click option'

Aref('sales')

Bavg_amount

Camount

Dorders

Attempts:

3 left

💡 Hint

Common Mistakes

Using table names directly instead of ref() function.

Using incorrect column names.

The ref('sales') function references the sales model, and amount is the column checked to ensure values are above 100.

5fill in blank

hard

Fill all three blanks to write a test that ensures no duplicate user IDs exist and all IDs are positive.

dbt

version: 2
models:
  - name: users
    columns:
      - name: user_id
        tests:
          - [1]
          - [2]
          - custom_test:
              sql: "SELECT COUNT(*) FROM [3] WHERE user_id <= 0"

Drag options to blanks, or click blank then click option'

Aunique

Bnot_null

Cref('users')

Daccepted_values

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up test names or using incorrect model references.

Omitting the not_null test.

The unique test checks for duplicates, not_null ensures no missing IDs, and ref('users') references the users model in the custom test.

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