dbt test failed: unique test flagged duplicates from null values

TL;DR

On most warehouses the built-in unique test counts repeated nulls as duplicates, so a nullable column with many nulls fails even when every real value is unique. Filter nulls out with a where config on the test, or switch to a singular test that excludes nulls. Keep genuine duplicate detection intact for non-null values.

dbt test failed: unique test flagged duplicates from null values

Use this when

• dbt test unique fails and the failing rows are all or mostly null in the tested column
• the column is nullable by design (optional foreign key, optional code)
• non-null values pass a manual distinct check in the warehouse

Not for this skill when

• non-null values really are duplicated, dedupe the model instead
• the column must never be null, add a not_null test and fix the upstream data rather than filtering
• you need uniqueness across a column pair, use a combined unique test instead

Steps

1. Run the failing unique test with store_failures on (or query the failures table) and inspect the returned values: confirm the duplicates are nulls, and count duplicates among non-null values separately. Verify: counts show zero non-null duplicates and the failure is driven by nulls.

2. Decide the contract: if nulls are allowed and mean not applicable, keep unique but scope it. Add a where argument to the test in schema.yml, for example unique with config where excluding null values for that column, using your adapter syntax for the is not null condition. Verify: schema.yml scopes the unique test to non-null rows.

3. If your dbt version or style prefers explicit SQL, replace the generic test with a singular test that selects the column, groups by it, filters nulls in a where clause, and keeps groups with count above one. Verify: the singular test file exists and the generic unique entry is removed for that column.

4. Run dbt test --select on the model. Then insert or identify a true duplicate non-null value in dev and rerun to prove the test still catches real duplicates. Verify: the test passes on current data and fails on a planted real duplicate.

Variant phrasings

uniquecombinationof_columns with nulls

Combined keys with a nullable member hit the same grouping behavior. Apply the where filter on the nullable member, or coalesce it deliberately and document the semantics.

null handling differs by warehouse

Some adapters treat nulls distinctly in group by. A where filter makes the test portable and the intent explicit on every adapter.

Compatibility: dbt-core generic unique test 1.x on Postgres, Snowflake, BigQuery, and Redshift. The where test argument requires dbt 1.3 or later style test configs.

Why it happens

The generic unique test groups by the column and counts groups larger than one. Standard SQL grouping puts all nulls in one group, so two or more null rows form a failing group even though null means unknown, not a repeated value. The test is working as written; the contract it encodes is stricter than the one you want for a nullable column.

Edge cases / pitfalls

• Filtering nulls hides a second problem if nulls spike unexpectedly; pair the scoped unique test with a separate accepted-range or volume check if null rate matters.
• where strings are adapter SQL; a filter copied between warehouses may need syntax tweaks.
• Do not fix this by making the column not null in a downstream model with coalesce to a sentinel unless sentinel semantics are documented; sentinels create fake duplicates.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_awoWno6AHLRO57Uts98WSg