Built-in tests (unique, not_null, accepted_values, relationships) in dbt - Step-by-Step Execution

Concept Flow - Built-in tests (unique, not_null, accepted_values, relationships)

Start with dbt model data

↓

Apply unique test on column

↓

Apply not_null test on column

↓

Apply accepted_values test on column

↓

Apply relationships test between tables

↓

Collect test results

↓

Pass or Fail report

This flow shows how dbt runs built-in tests on data columns and relationships, then reports if data passes or fails.

Execution Sample

dbt

version: 2
models:
  - name: customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['active', 'inactive', 'pending']

This dbt YAML config runs unique and not_null tests on customer_id and accepted_values test on status.

Execution Table

Step	Test Type	Column/Table	Condition Checked	Result	Action
1	unique	customer_id	All values are unique?	True	Pass
2	not_null	customer_id	No null values?	True	Pass
3	accepted_values	status	Values in ['active','inactive','pending']?	False	Fail - found 'deleted'
4	relationships	orders.customer_id -> customers.customer_id	All foreign keys exist in parent?	True	Pass
5	Summary	-	All tests checked	1 Fail, 3 Pass	Report results

💡 All tests run; one accepted_values test failed due to unexpected value.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
unique_test_customer_id	Not run	True	True	True	True	True
not_null_test_customer_id	Not run	Not run	True	True	True	True
accepted_values_test_status	Not run	Not run	Not run	False	False	False
relationships_test_orders_customer_id	Not run	Not run	Not run	Not run	True	True
test_summary	Empty	Pass	Pass	Fail	Fail	1 Fail, 3 Pass

Key Moments - 3 Insights

Why did the accepted_values test fail even though most values looked correct?

The accepted_values test checks every value strictly against the allowed list. The failure happened because one value ('deleted') was not in the allowed list ['active', 'inactive', 'pending'], as shown in step 3 of the execution_table.

Does the unique test check for null values?

No, the unique test only checks if all values are distinct. Null values are handled separately by the not_null test, as seen in steps 1 and 2.

What does the relationships test verify?

It verifies that all foreign key values in the child table exist in the parent table. Step 4 shows it passed because all orders.customer_id values matched customers.customer_id.

Visual Quiz - 3 Questions

Look at the execution_table at step 3. What caused the accepted_values test to fail?

A Duplicate values were found

B There were null values in the column

C A value outside the allowed list was found

D Foreign key mismatch occurred

💡 Hint

Check the 'Result' and 'Action' columns in step 3 of the execution_table.

According to variable_tracker, what is the final result of the relationships test?

A False

B True

C Not run

D Partial

💡 Hint

Look at the 'relationships_test_orders_customer_id' row in variable_tracker under 'Final' column.

If the not_null test on customer_id failed, which step in the execution_table would show that?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Refer to the 'Test Type' column to find where not_null test is executed.

Concept Snapshot

dbt built-in tests check data quality:
- unique: all values distinct
- not_null: no nulls allowed
- accepted_values: values in allowed list
- relationships: foreign keys exist in parent
Tests run on columns or tables and report pass/fail.

Full Transcript

This visual execution shows how dbt runs built-in tests on data columns and relationships. First, it checks if values in a column are unique, then if there are no null values. Next, it verifies if column values are within an accepted list. Finally, it checks relationships between tables to ensure foreign keys exist in the parent table. The execution table traces each test step, showing conditions checked and results. Variable tracker records test outcomes after each step. Key moments clarify common confusions like why accepted_values test fails or what relationships test means. The quiz asks questions about specific steps and results to reinforce learning. The snapshot summarizes the main tests and their purpose.

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