What causes Test Failure: unique?

The source table contains duplicate records (e.g., a CDC process delivered the same event twice). A JOIN in the model produces fan-out, creating multiple output rows per source row. An incremental model is not deduplicating correctly during merge or insert operations. A grain mismatch — the model is at a lower grain than intended, so what looks like a unique key has duplicates at the actual grain. A dbt seed file was updated with duplicate rows

How do I fix Test Failure: unique?

Run `dbt test --select ` and check the test output — it shows the number of failing rows and their values.. Query the model directly: `SELECT , COUNT(*) FROM GROUP BY HAVING COUNT(*) > 1`.. Add a `DISTINCT` or deduplication step (e.g., `ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1`) to the model SQL.. If the source contains duplicates, add a staging model that deduplicates before the downstream model reads from it.. For incremental models, review the unique_key and merge logic to ensure existing rows are updated rather than inserted again.

Medium severitydbt

Power BI Refresh Error:
Test Failure: unique

What does this error mean?

dbt's built-in unique test found duplicate values in a column that is expected to contain only unique values.

Common causes

1The source table contains duplicate records (e.g., a CDC process delivered the same event twice)
2A JOIN in the model produces fan-out, creating multiple output rows per source row
3An incremental model is not deduplicating correctly during merge or insert operations
4A grain mismatch — the model is at a lower grain than intended, so what looks like a unique key has duplicates at the actual grain
5A dbt seed file was updated with duplicate rows

How to fix it

1Run `dbt test --select <model_name>` and check the test output — it shows the number of failing rows and their values.
2Query the model directly: `SELECT <column>, COUNT(*) FROM <model> GROUP BY <column> HAVING COUNT(*) > 1`.
3Add a `DISTINCT` or deduplication step (e.g., `ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1`) to the model SQL.
4If the source contains duplicates, add a staging model that deduplicates before the downstream model reads from it.
5For incremental models, review the unique_key and merge logic to ensure existing rows are updated rather than inserted again.

Frequently asked questions

Should I set dbt tests to 'warn' instead of 'error' to avoid blocking the run?

That depends on the severity. For primary key uniqueness, 'error' is appropriate — allowing duplicate PKs to flow into Power BI causes silent data corruption. Use 'warn' only for informational checks that don't affect correctness.

Official documentation: dbt-docs