Built-in tests (unique, not_null, accepted_values, relationships) in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a unique test on a column with duplicates

Given a dbt model with the following data in the users table:

id | name
---|------
1  | Alice
2  | Bob
2  | Charlie
3  | David

What will be the result of running a unique test on the id column?

dbt

version: 2
models:
  - name: users
    columns:
      - name: id
        tests:
          - unique

A The test will fail because the <code>id</code> value 2 appears twice.

B The test will pass because all <code>id</code> values are integers.

C The test will fail because the <code>name</code> column has duplicates.

D The test will pass because there are no null values in <code>id</code>.

💡 Hint

Think about what the unique test checks for in a column.

The unique test checks if all values in the column are distinct. Since id has duplicate value 2, the test fails.

❓ data_output

intermediate

Result of a not_null test on a column with null values

Consider a products table with this data:

product_id | price
----------|-------
101       | 9.99
102       | NULL
103       | 15.00

What will be the result of running a not_null test on the price column?

dbt

version: 2
models:
  - name: products
    columns:
      - name: price
        tests:
          - not_null

A The test will pass because most values in <code>price</code> are numbers.

B The test will fail because there is a NULL value in <code>price</code>.

C The test will pass because <code>product_id</code> is unique.

D The test will fail because <code>price</code> contains decimal numbers.

💡 Hint

Remember what the not_null test checks for.

The not_null test fails if any value in the column is NULL. Here, price has a NULL value, so the test fails.

🧠 Conceptual

advanced

Purpose of accepted_values test in dbt

What is the main purpose of the accepted_values test in dbt?

A To ensure that no values in a column are NULL.

B To check if all values in a column are unique.

C To verify that all values in a column belong to a predefined list of allowed values.

D To confirm that foreign key relationships exist between tables.

💡 Hint

Think about validating categorical or enumerated data.

The accepted_values test checks if all values in a column are within a specified set of allowed values.

❓ Predict Output

advanced

Output of a relationships test with missing foreign key

Given two tables:

orders:
order_id | customer_id
---------|------------
1        | 10
2        | 20
3        | 30

customers:
customer_id
-----------
10
20

What will be the result of running a relationships test on orders.customer_id referencing customers.customer_id?

dbt

version: 2
models:
  - name: orders
    columns:
      - name: customer_id
        tests:
          - relationships:
              to: ref('customers')
              field: customer_id

A The test will fail because <code>customer_id</code> 30 in <code>orders</code> does not exist in <code>customers</code>.

B The test will fail because <code>orders</code> has duplicate <code>order_id</code> values.

C The test will pass because all <code>customer_id</code> values exist in <code>customers</code>.

D The test will pass because <code>customers</code> has no NULL values.

💡 Hint

Check if all foreign keys in orders exist in customers.

The relationships test checks if every foreign key value exists in the referenced table. Since 30 is missing in customers, the test fails.

🔧 Debug

expert

Identify the error in this dbt test configuration

Look at this dbt test configuration snippet:

version: 2
models:
  - name: sales
    columns:
      - name: region
        tests:
          - accepted_values:
              values: ["North", "South", "East", "West"]
          - relationships:
              to: ref('regions')
              field: region_id

What is the main issue that will cause a test failure or error?

A The <code>accepted_values</code> test is missing the <code>field</code> key.

B The <code>ref</code> function cannot be used inside tests.

C The <code>values</code> list in <code>accepted_values</code> contains invalid strings.

D The <code>relationships</code> test references <code>region_id</code> but the column tested is <code>region</code>.

💡 Hint

Check if the columns referenced in tests match the column names defined.

The relationships test references a different column (region_id) than the one defined (region), causing a mismatch and test failure.