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.