How to Use Relationships Test in dbt for Data Integrity

In dbt, use the relationships test to check that foreign key columns in one table correctly reference primary keys in another table. Define this test in your schema.yml by specifying the column and the to table and column it should relate to.

Syntax

The relationships test in dbt verifies that values in a column exist in a related table's column, ensuring foreign key integrity.

Key parts:

• column: The column in your current model to test.
• to: Specifies the related model and column that the current column should reference.

yaml

tests:
  - relationships:
      column: foreign_key_column
      to: ref('parent_model').column_name

Example

This example shows how to add a relationships test to ensure customer_id in the orders table exists in the customers table.

yaml

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

Output

Running with dbt=1.4.6
Found 1 model, 1 test, 0 snapshots, 0 analyses, 0 macros, 0 operations, 0 seed files, 0 sources

14:22:10 | Concurrency: 1 threads
14:22:10 | 
14:22:10 | 1 of 1 START test relationships_orders_customer_id__to_customers_id................ [RUN]
14:22:11 | 1 of 1 PASS relationships_orders_customer_id__to_customers_id................ [PASS in 0.45s]

Completed successfully

Common Pitfalls

Common mistakes when using the relationships test include:

• Referencing the wrong column or model in the to field.
• Not using ref() to refer to the related model, which breaks dependency tracking.
• Testing columns that are nullable without considering null values, which can cause false failures.

Always ensure the foreign key column and the referenced column have matching data types.

yaml

tests:
  - relationships:
      column: customer_id
      to: customers.id  # Incorrect: missing ref()

# Correct usage:
tests:
  - relationships:
      column: customer_id
      to: ref('customers').id

Quick Reference

Field	Description	Example
column	Column in current model to test	customer_id
to	Reference to related model and column using ref()	ref('customers').id
severity (optional)	Set test failure severity (e.g., warn, error)	warn

Key Takeaways

Use the relationships test to ensure foreign key columns reference valid rows in related tables.

Always use ref() to specify the related model and column in the test.

Check that data types match between the foreign key and referenced columns.

Handle nullable foreign key columns carefully to avoid false test failures.

Add relationships tests in your schema.yml under the model's columns section.