dbt test: orphan payments against orders

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Reconciliation. About 12 minutes. Part of the Pro drill bank.

dbt is a tool that runs tests on data tables. One common test is relationships: it checks that every value in one table exists in another, such as every payment pointing at a real order. A payment with no order is called an orphan. Finance wants proof that the warehouse has none.

Write the relationships test as a SQL query. Return payment_id and order_id for every row in payments whose order_id does not exist in orders. Order by payment_id. In this warehouse the data is clean, so the query should return no rows. A test that passes with an empty result is how dbt works: any row returned is a failure. Do not use LIMIT to hide anything. If there were orphans, the query would show all of them.

Requirements

  • Find payments whose order_id has no match in orders.
  • Use an anti-join: LEFT JOIN ... WHERE o.order_id IS NULL, or NOT EXISTS.
  • Return payment_id and order_id, ordered by payment_id.
  • Do not use LIMIT.

Constraints

  • An empty result means the test passes.
  • Every violator must be shown, so there is no LIMIT.
  • The check is from payments to orders, not the other way round.

Topics: dbt, relationships, referential-integrity.

More SQL interview questions · All interview problems · Learn data engineering

intermediate

dbt test: orphan payments against orders

Production ticket: The relationships test for payments.order_id -> orders.order_id. Empty is a pass, not a shortcut.

Finance wants proof, not a promise: does every payment point at a real order? Write the dbt `relationships` test as SQL. Return `payment_id`, `order_id` for every row in `payments` whose `order_id` has no matching row in `orders`. Order by `payment_id`. This warehouse should pass (empty result). Do not LIMIT the query to hide a failure - an anti-join that is actually wrong will return real rows.