How to test a combination of columns is unique in dbt (dbt_utils)

In an order lines table, order_id repeats once per line and line_number repeats in every order. Neither is unique on its own; the pair is. The built-in unique test checks one column, so a key made of several columns needs unique_combination_of_columns from the dbt_utils package.

Copy and paste

1. Add the package, in packages.yml at the root of the project:

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.4.0", "<2.0.0"]

dbt deps

2. Add the test to the model, at model level rather than under a column:

# models/marts/_marts.yml
version: 2
models:
  - name: order_lines
    data_tests:
      - dbt_utils.unique_combination_of_columns:
          arguments:
            combination_of_columns: [order_id, line_number]

dbt test -s order_lines

Without a package

unique on an expression. A model-level unique test accepts an expression as column_name:

models:
  - name: order_lines
    data_tests:
      - unique:
          arguments:
            column_name: "(order_id || '-' || line_number)"

The separator matters: without it, order 1 line 12 and order 11 line 2 both become 112.

A singular test, in tests/assert_order_lines_unique.sql:

select order_id, line_number, count(*) as copies
from {{ ref('order_lines') }}
group by order_id, line_number
having count(*) > 1

The test fails if the query returns any row; see how to write a custom SQL test.

What the test catches

In the example project two orders share order_id 5 but have different customers. The tests on stg_orders give:

Both are right: order_id alone repeats, the pair does not. Which one you need depends on what the table’s grain is supposed to be.

Tested with dbt Core 1.12 and dbt_utils 1.4.1.