Writing a custom dbt test

None of the four built-in tests catches a completed order with a negative total. Finance just found one.

IntermediateRead 4 minDoing: a couple of hours to write and wire it up, then a short wait on the data owner's fix before the build runs clean7 steps

▶ Start

What you'll have at the end

A custom dbt test that fails the build the moment a completed order's total goes negative

Before you start

You need

A dbt project with a fact model that has a status column and more than one numeric amount column, plus a target you can run dbt test against without touching production.

Not covered

Deciding whether a check like this belongs in your own project or in a shared, ready-made test package your whole team pulls in.

Leans on

7 steps

Where to go next

Turn repeated SQL into a dbt macro

for the general mechanics of writing and debugging a macro, if the {{ }} templating in the generic test is unfamiliar.

Catch a broken foreign key with dbt's relationships test

for starting a new test at a gentler severity and promoting it later, if you'd rather roll this out gradually than fix the known bad rows first.

Checked 25 Aug 2026

Part of the dbt (Data Build Tool) cookbook