How it Works

The relationships test is one of dbt™'s four built-in generic tests. It validates referential integrity by checking that every non-null value in a foreign key column exists in the referenced column of another model. This is the data layer equivalent of a foreign key constraint.

Steps and Conditions

1. Column Selection: Identify the foreign key column to test.

2. Define Reference: Specify the target model and its primary key column using to and field.

3. Execution: Each non-null foreign key value is checked against the reference model.

4. Outcome: Pass if all foreign keys resolve to valid reference values; fail if any orphaned records exist.

Example Usage: E-commerce

An e-commerce platform wants to ensure that every order in the orders table references a valid customer in the customers table.

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

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

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

Any order referencing a non-existent customer will fail the test, protecting the integrity of customer-order analytics.