Declaring a Primary Key RELY turned my dbt unique Test Green on Duplicate Data

ivanvyd — Fri, 25 Sep 2026 18:30:18 GMT

An open dbt-databricks feature request, #1271, asks for native RELY support on primary keys and suggests that rely: true "implicitly create the not_null and unique constraints" as "a safeguard against incorrect query results". Databricks does not enforce UNIQUE constraints, so only a uniqueness test would check the data. I tested whether dbt's unique test still works once the key says RELY.

It did not. I built two dbt models from the same source, which contains one duplicate order. Their contracts differ only in the primary key option. dbt's unique test failed on the NORELY model and passed on the RELY model.

Figure 1. The same unique test on two models built from the same rows.

WHAT THE DOCS SAY
"If RELY, Databricks may exploit the constraint to rewrite and optimize queries. It is the user's responsibility to ensure the constraint is satisfied. Relying on a constraint that is not satisfied may lead to incorrect query results."
CONSTRAINT clause, Databricks SQL reference

The setup

The source holds six orders, and order 3 appears twice with different amounts, as a replayed event would. Until #1271 lands, expression does the job: dbt-databricks appends it to the primary key DDL, so this contract creates PRIMARY KEY (order_id) RELY.

models:
  - name: orders_rely
    config:
      contract: {enforced: true}
    constraints:
      - type: primary_key
        columns: [order_id]
        expression: RELY          # the control model uses NORELY, the default
    columns:
      - name: order_id
        data_type: bigint
        constraints: [{type: not_null}]
        data_tests: [unique]
      # customer and amount columns omitted

Both models keep the duplicate: filtering on order_id = 3 returns two rows from each. I ran everything on a serverless SQL warehouse (Photon, DBSQL 2026.36) with dbt-core 1.12.3 and dbt-databricks 1.12.5. The docs say these rewrites need Photon; I did not test other compute.

Why the test passes

dbt's unique test groups by the column and returns the groups where count(*) > 1. If order_id is unique, each group holds one row and count(*) > 1 cannot be true. With RELY, the optimizer takes the key at its word and replaces the query with an empty result. The physical plans, trimmed:

-- PRIMARY KEY NORELY
PhotonFilter (n_records > 1)
+- PhotonGroupingAgg(keys=[order_id], functions=[count(1)])
   +- PhotonScan parquet workspace.rely_lab_dbt.orders_norely[order_id]

-- PRIMARY KEY RELY
LocalTableScan <empty>, [unique_field, n_records]

A larger table behaved the same way. On 1,000,010 rows in 16 files with 10 duplicated keys, the NORELY run found all 10 and read every row. The RELY run found none and read no rows, and COUNT(DISTINCT order_id) on the same table returned 1,000,010 instead of 1,000,000.

Figure 2. The same duplicate-key query in system.query.history. The RELY run opened no files.

Which checks go blind

I loaded the same six rows into tables with no key, a NORELY key and a RELY key. The first two matched in every check.

Check	No key or NORELY	RELY
*dbt unique (GROUP BY … HAVING COUNT() > 1)**	1 duplicate key	0
PySpark groupBy("order_id").count().filter("count > 1"), serverless job	1	0
COUNT(DISTINCT order_id)	5	6
LEFT JOIN to a customer table with a duplicate key, row count	8	6
*COUNT() OVER (PARTITION BY order_id) > 1**	2 rows	2 rows
DQX 0.16.0 is_unique	2 rows flagged	2 rows flagged

The fan-out check missed because the optimizer dropped the join: with a unique customer key, a LEFT JOIN cannot add rows.

Figure 3. One query over three tables with identical rows. The RELY row reports six distinct keys, no duplicates and no join fan-out.

What to do about it

Find your RELY keys. information_schema.table_constraints returns the same row for a RELY and a NORELY key. SHOW CREATE TABLE and DESCRIBE TABLE EXTENDED show the option.
Validate before you declare RELY. Check uniqueness on the staging table or incoming batch, before rows reach a RELY table, and declare RELY only where the writer guarantees uniqueness. Ingestion that can replay events does not.
Count with a window in dbt. A databricks__test_unique macro in your project replaces the built-in test on Databricks. With this version, the RELY model's test failed with 2 rows, one per duplicated row. Measure its runtime on your largest models before you switch.

{% macro databricks__test_unique(model, column_name) %}
select unique_field, n_records
from (
    select {{ column_name }} as unique_field,
           count(*) over (partition by {{ column_name }}) as n_records
    from {{ model }}
    where {{ column_name }} is not null
) counted
where n_records > 1
{% endmacro %}

DQX caught it. is_unique in DQX 0.16.0 counts with a window function and flagged both rows here and all 20 in the million-row table.
Check the check. If EXPLAIN shows no scan of the table, or query history shows 0 rows read, the check tested nothing.

The window-based checks held in these runs. I found no documentation promising they will keep working, so re-test after upgrades.

Conclusion