dbt test failed: relationships test on orders"

TL;DR

The relationships test found foreign key values in orders with no matching row in the parent table. Store the failures to list the orphan keys, then either fix the source data or add the missing parent rows. Rerun the test to confirm.

Error

"dbt test failed: relationships test on orders"

Steps

1. Re-run with failures stored: dbt test --select relationships_orders_customer_id__customer_id --store-failures (use the actual test name from the failure output). Expected: dbt writes the orphan rows to a failures table.
2. Query the failures table to list the distinct orphan key values. Expected: a concrete list of keys with no parent match.
3. Check whether the parent table is stale: rebuild it with dbt run --select [PARENT MODEL] and rerun the test. Expected: if the parent was stale, the test now passes.
4. If orphans are real, fix them: correct the source data, or filter the bad rows in the model with a documented rule. Expected: no orphan keys remain.
5. Re-run the relationships test. Expected: it passes with zero failing rows.

When to use

• A relationships generic test fails on any model.
• Orphan keys appeared after a source refresh or a parent model change.

When not to use

• The test fails at parse time (a YAML config problem, not a data problem).
• Orphan keys are expected (then the test is wrong for this column; remove or scope it).

Tool compatibility

• dbt Core 1.0 and later, all adapters. The test SQL is standard SQL.

Variant phrasings

relationships test failed on the parent side after a full refresh

The parent rebuilt but the child still references old keys; rebuild the child too.

Got N results for relationships test, configured to fail if != 0

The long form with the orphan count; the fix is identical.

Why it happens

relationships compiles to an anti-join: child rows whose foreign key matches nothing in the parent. Late-arriving dimensions, deleted parent rows, or bad source keys all produce orphans.

Edge cases

• Soft-deleted parent rows still fail the test; decide whether the test should exclude them with a where config.
• Case or type mismatches between the key columns (string vs integer) silently produce orphans; align the types.
• The test checks the built parent table, so test order matters: build the parent before testing the child.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_Zou5FPZtaJIyIHni19hOrw