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.

Practice

1. What does the built-in unique test in dbt check for in a column?

easy

A. It checks that the column has no missing (null) values.

B. It checks that the column values exist in another table's column.

C. It checks that the column values match a predefined list of accepted values.

D. It checks that all values in the column are different with no duplicates.

Solution

1. Step 1: Understand the purpose of the unique test

   The unique test ensures that each value in the specified column appears only once, meaning no duplicates.

2. Step 2: Compare with other test types

   Other tests like not_null check for missing values, accepted_values check for allowed values, and relationships check for foreign key matches.

3. Final Answer:

   It checks that all values in the column are different with no duplicates. -> Option D

4. Quick Check:

   unique test = no duplicates [OK]

Hint: Unique means no duplicates allowed in the column [OK]

Common Mistakes:

• Confusing unique with not_null test
• Thinking unique checks accepted values
• Mixing unique with relationships test

2. Which of the following is the correct syntax to add a not_null test on the column user_id in a dbt model YAML file?

easy

A. columns:
- name: user_id
tests:
- not_null

B. columns:
- user_id:
tests:
- not_null

C. tests:
- not_null: user_id

D. columns:
- name: user_id
test: not_null

Solution

1. Step 1: Recall YAML structure for dbt tests

   Tests are added under the columns list, each column has a name and a tests list with test names.

2. Step 2: Identify correct indentation and keys

   columns: - name: user_id tests: - not_null correctly uses 'name' for the column and 'tests' as a list with '- not_null'. Other options have wrong keys or structure.

3. Final Answer:

   columns: - name: user_id tests: - not_null -> Option A

4. Quick Check:

   YAML tests under columns with name and tests list [OK]

Hint: Use 'name' and 'tests' keys with proper indentation [OK]

Common Mistakes:

• Using 'test' instead of 'tests'
• Incorrect indentation breaking YAML
• Placing tests outside columns section

3. Given this YAML snippet in a dbt model:

columns:
  - name: status
    tests:
      - accepted_values:
          values: ['active', 'inactive', 'pending']

What happens if the status column contains the value 'deleted' when you run dbt test?

medium

A. The test passes because 'deleted' is a valid string.

B. The test fails because 'deleted' is not in the accepted values list.

C. The test is skipped because accepted_values only checks for nulls.

D. The test throws a syntax error due to incorrect YAML.

Solution

1. Step 1: Understand accepted_values test behavior

   The accepted_values test checks if all column values are within the specified list.

2. Step 2: Check if 'deleted' is in the list

   'deleted' is not in ['active', 'inactive', 'pending'], so the test will fail.

3. Final Answer:

   The test fails because 'deleted' is not in the accepted values list. -> Option B

4. Quick Check:

   accepted_values rejects values outside list [OK]

Hint: Accepted_values fails if any value is outside the list [OK]

Common Mistakes:

• Assuming test passes if value is a string
• Confusing accepted_values with not_null
• Thinking test skips unknown values

4. You wrote this test in your dbt model YAML:

columns:
  - name: order_id
    tests:
      - relationships:
          to: ref('orders')

But running dbt test gives an error. What is the most likely cause?

medium

A. The 'field' key is missing in the relationships test.

B. The 'to' value should be a string, not a ref function.

C. The relationships test requires the 'field' to be the same as the column name.

D. The 'to' value must be a table name string, not a ref function.

Solution

1. Step 1: Understand relationships test syntax

   The relationships test requires both 'to' (target table) and 'field' (target column).

2. Step 2: Identify the error cause

   The YAML is missing the 'field' key, causing a configuration error when running dbt test.

3. Final Answer:

   The 'field' key is missing in the relationships test. -> Option A

4. Quick Check:

   relationships 'to' + 'field' required [OK]

Hint: relationships test requires 'to' and 'field' keys [OK]

Common Mistakes:

• Using ref() in YAML instead of table name string
• Omitting the 'field' key
• Assuming 'field' must match column name

5. You want to ensure that the customer_id column in your orders model is unique, not null, and only contains values that exist in the customers table's id column. Which combination of built-in tests should you add in your YAML?

hard

A. - not_null
- accepted_values:
values: [unique]
- relationships:
to: customers
field: id

B. - unique
- accepted_values:
values: [not null]
- relationships:
to: customers
field: id

C. - unique
- not_null
- relationships:
to: customers
field: id

D. - unique
- not_null
- accepted_values:
values: [customer_id]

Solution

1. Step 1: Identify tests for uniqueness and non-null

   Use 'unique' to ensure no duplicates and 'not_null' to prevent missing values.

2. Step 2: Ensure foreign key relationship

   Use 'relationships' test with 'to' as 'customers' table and 'field' as 'id' to check existence.

3. Step 3: Verify other options

   Options B, C, and D misuse accepted_values or mix concepts incorrectly.

4. Final Answer:

   - unique - not_null - relationships: to: customers field: id -> Option C

5. Quick Check:

   unique + not_null + relationships = correct tests [OK]

Hint: Combine unique, not_null, and relationships for full check [OK]

Common Mistakes:

• Using accepted_values to check null or uniqueness
• Misconfiguring relationships test
• Missing one of the required tests