How it Works

The relationships_where test from dbt-utils™ extends the standard relationships test by letting you apply filter conditions to both the source and target tables before checking referential integrity. This is particularly handy when certain rows—such as test accounts or recently ingested records—are legitimately expected to break referential integrity.

Steps and Conditions

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

2. Define Referential Target: Specify the target model and its primary key column.

3. Specify Conditions: Use from_condition and to_condition to pre-filter rows in each table.

4. Execution: Filtered rows are checked for referential integrity.

5. Outcome: Pass if all filtered foreign keys resolve to valid primary keys; fail otherwise.

Example Usage: Healthcare

A healthcare platform needs to ensure every appointment record links to a valid patient, but wants to exclude internal test appointments and only consider patients who registered after the system launch.

models:
  - name: appointments
    columns:
      - name: patient_id
        tests:
          - dbt_utils.relationships_where:
              to: ref('patients')
              field: id
              from_condition: "appointment_type != 'internal_test'"
              to_condition: "registered_at >= '2022-01-01'"

models:
  - name: appointments
    columns:
      - name: patient_id
        tests:
          - dbt_utils.relationships_where:
              to: ref('patients')
              field: id
              from_condition: "appointment_type != 'internal_test'"
              to_condition: "registered_at >= '2022-01-01'"

models:
  - name: appointments
    columns:
      - name: patient_id
        tests:
          - dbt_utils.relationships_where:
              to: ref('patients')
              field: id
              from_condition: "appointment_type != 'internal_test'"
              to_condition: "registered_at >= '2022-01-01'"

This ensures referential integrity between appointments and patients while excluding internal test records and patients pre-dating the system launch.