Databricks can now optimize with UNIQUE constraints—but RELY turns data quality into a correctness dependency

Databricks has put UNIQUE constraints into Public Preview for Unity Catalog Delta tables on Databricks SQL and Databricks Runtime 18.2 and above. The feature fills a real modeling gap: a table can still have one non-nullable primary key, while recording multiple additional uniqueness guarantees, including composite keys and nullable columns. Foreign keys may reference those unique keys. Databricks

The important operating detail is that these constraints are informational. Declaring one does not reject duplicate writes. A new constraint starts as NORELY, which lets people and tools inspect the intended schema without letting the optimizer make correctness-dependent rewrites. Teams can recreate a validated constraint with RELY; that is the point where Databricks may assume the guarantee is true. Databricks

The speedup is real—and conditional

Databricks reports two TPC-DS SF10000 tests on Photon-enabled compute. In a join between the 28.8-billion-row store_sales table and the 65-million-row customer table, a relied-upon unique key plus foreign key allowed the optimizer to eliminate the join, remove more than 99.99% of shuffle and reduce median engine time from 23.6 seconds to 3.0 seconds. In a second test, a composite unique constraint let it remove a DISTINCT aggregation, again cutting shuffle by more than 99.99% and reducing median time from 33.7 seconds to 2.8 seconds. These are vendor-run examples, not a promise that arbitrary workloads will see the same gains. Databricks

Eligibility depends on the complete proof. An inner join needs both an at-most-one guarantee from the unique key and an existence guarantee from the foreign key. Nullability also matters: a nullable unique column may contain multiple nulls, so DISTINCT cannot always disappear unless the query filters nulls. Databricks also warns that foreign-key column order must match the referenced composite unique constraint or join elimination can silently fail to fire. Databricks

Treat RELY as a production control

The safest rollout is staged:

1. Declare existing guarantees as NORELY so they become discoverable metadata.
2. Check duplicate non-null values and orphaned child rows.
3. Make the ingestion or data-quality pipeline continuously enforce those conditions.
4. Enable RELY only for constraints with an accountable owner and a monitored validation job.
5. Compare plans with EXPLAIN and retain a fast way to remove trust if validation fails. Databricks

That last control matters because RELY validates neither historical data nor future writes. Databricks says a violated relied-upon constraint can make a rewrite return incorrect results. The feature is therefore not merely schema documentation or a performance switch: it moves a data-quality assertion into the query engine’s correctness assumptions. Databricks