Catch a broken foreign key with dbt's relationships test

A join between orders and customers is dropping rows, and nobody noticed until finance asked why the numbers didn't add up.

Beginner to intermediateRead 4 minDoing: an hour to set up, plus a couple of days watching it before you flip it to error6 steps

▶ Start

What you'll have at the end

A relationships test on the customer id column that fails the build the moment an order points at a customer that doesn't exist.

Before you start

You need

A dbt project with a fact model whose foreign key column, such as a customer id on an orders model, should always match a row in a dimension model, plus a target you can run tests against without touching anyone else's data.

Not covered

Fixing why customer records sometimes land after the orders that reference them, or building a placeholder dimension row so a late order always has something valid to point at.

Leans on

6 steps

Where to go next

Handle a late arriving dimension without breaking the fact table

For a permanent fix that gives a fact table something valid to point at the moment an order lands, instead of catching the gap after it's already there.

Checked 25 Aug 2026

Part of the dbt (Data Build Tool) cookbook