11. Orphaned Rows: a relationships Test
Difficulty: Medium · Topic: Data tests
Write the generic test has_parent(model, column_name, to, field): every non-null column_name in model must exist as a field value in the to model. This is how dbt's built-in relationships test works.
- name: customer_id
data_tests:
- has_parent:
to: ref('customers')
field: customer_id
Return all columns of every orphaned row in model. A null foreign key is not an orphan. Careful: the parent table can contain a null key too.
Your model is compiled like dbt does and run in DuckDB against 3 test cases, including hidden edge cases.
Sample inputs
{{ ref('orders') }}
| order_id | customer_id |
|---|---|
| 1 | 10 |
| 2 | 11 |
| 3 | 99 |
| 4 | null |
{{ ref('customers') }}
| customer_id | name |
|---|---|
| 10 | Ana |
| 11 | Bo |
{{ ref('payments') }}
| payment_id | order_id |
|---|
Expected output
| order_id | customer_id |
|---|---|
| 3 | 99 |
Hints
Hint 1
Start from the child:select child.* from {{ model }} as child left join {{ to }} as parent on child.{{ column_name }} = parent.{{ field }}, then keep the rows with no parent.Hint 2
where x not in (select field from parent) returns no rows at all as soon as the parent column contains a null: x not in (1, null) is null, never true.Hint 3
Exclude null child keys explicitly:child.{{ column_name }} is not null.