dbt test: unique on the order_items grain

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

dbt has a unique test for 1 column, but order_items is not unique on any single column. The real rule is that one order should have one line for each product. If a customer adds the same product twice, the quantity should go up on one line, not a second line. Duplicates break revenue totals.

Write the uniqueness test on the (order_id, product_id) pair as a SQL query. Return order_id, product_id and n, the number of rows for that pair, for every pair that appears more than once. Order by order_id, then product_id. This warehouse does have real violators, so the result is not empty. Each row you return is a duplicated line.

Requirements

  • Group by both order_id and product_id.
  • Count the rows in each group as n.
  • Keep only the groups where n is greater than 1.
  • Order by order_id, then product_id.

Constraints

  • The grain is the pair (order_id, product_id). Grouping by only 1 column checks the wrong rule.
  • n is COUNT(*).
  • Use HAVING COUNT(*) > 1 to filter the groups.

Topics: dbt, unique, composite-key.

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

intermediate

dbt test: unique on the order_items grain

Production ticket: unique on a list of columns, not just one - the real grain of a line-item table.

`order_items` should have at most 1 row per (`order_id`, `product_id`) - a customer adding the same SKU twice should bump `quantity` on one line, not create a second line. dbt's `unique` test on a single column can't check this; it needs the composite key. Return `order_id`, `product_id`, and `n` (COUNT(*)) for every (order_id, product_id) pair that appears more than once. Order by `order_id`, `product_id`. This warehouse has real violators - this is not an always-empty check.