Testing · rule model-grain-not-asserted
dbt unique_combination_of_columns: how to use it
dbt_utils.unique_combination_of_columns is a generic test from the dbt_utils package. It passes when no two rows share the same values across the columns you list, even when each column repeats on its own.
A Metacenta review checks this under the rule Tested models assert their grain (a uniqueness test). Everything below applies whether or not you ever commission one.
What the test checks
Take an orders table loaded from several countries. order_id repeats across countries and country_code repeats across orders, so unique on either column fails. The pair is unique, and this test asserts exactly that.
How to set it up
Install dbt_utils through packages.yml and run dbt deps.
packages:
- package: dbt-labs/dbt_utils
version: 1.4.1
Then add the test at model level, not under a column, and list the columns under combination_of_columns.
models:
- name: fct_orders
data_tests:
- dbt_utils.unique_combination_of_columns:
arguments:
combination_of_columns:
- country_code
- order_id
The arguments: block is available from dbt 1.10.5. Older versions set combination_of_columns directly under the test name. The optional quote_columns argument defaults to false; set it to true when a column name needs quoting.
Other ways to test a composite key
dbt's docs give two more options. You can build a surrogate key column in the model and test it with unique. Or you can pass unique an expression through column_name.
models:
- name: fct_orders
data_tests:
- unique:
arguments:
column_name: "(country_code || '-' || order_id)"
Both work, but dbt recommends unique_combination_of_columns on large datasets, where it performs better.
What to test alongside it
The test checks that the combination is unique, not that each column is populated. Add not_null to each column in the key, which we cover in dbt key columns: no not_null test.
On an incremental model, list the same columns as its unique_key; see dbt incremental models: how unique_key works. A model with no tests at all is covered by dbt models with no tests.
See dbt's FAQ on testing two columns for the full examples.
What this rule checks
Among models that have tests, this rule flags those where nothing asserts one row per key. unique, dbt_utils.unique_combination_of_columns and the dbt_expectations uniqueness tests count. A uniqueness test limited by where or row_condition does not, because it covers only some rows.
Why it matters
A duplicate row doubles every sum built on it, and nothing fails when it happens. It's the most common reason a metric can't be reconciled.
How to fix it
Test that no key appears twice in any table. Add unique on the key column. For a grain defined by several columns, use dbt_utils.unique_combination_of_columns over exactly those columns.
Before:
models:
- name: fct_order_lines
columns:
- name: order_id
data_tests:
- not_null
After:
models:
- name: fct_order_lines
data_tests:
- dbt_utils.unique_combination_of_columns:
arguments:
combination_of_columns:
- order_id
- line_number
When it is fine to leave
Some models have no grain by design, such as an append-only event log where two identical rows are two real events. Say so in the model's description. Models with no tests at all are not judged here; the rule on untested models covers them.
What we need to check it
manifest.json alone. We name any uniqueness test that was left out because of a where filter, so you can see why it did not count.
Published rules it corresponds to
dbt_project_evaluator, rule fct_missing_primary_key_tests. Ours checks a subset of what it flags; theirs is stricter. dbt Labs' rule requires a not_null test as well as a uniqueness one; that half is model-no-not-null-test here, so their single rule is these two checks together.
This means our check corresponds to their rule. It does not mean the publisher reviewed or endorses it.