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.