Query optimization using primary key constraints

Primary key constraints, which capture relationships between fields in tables, can help users and tools understand relationships in your data. This article contains examples that show how you can use primary keys with the RELY option to optimize some common types of queries.

Add primary key constraints

You can add a primary key constraint in your table creation statement, as in the following example, or add a constraint to a table using the ADD CONSTRAINT clause.

CREATE TABLE customer (
  c_customer_sk int,
  PRIMARY KEY (c_customer_sk)
  ...
  )

In this example, c_customer_sk is the customer ID key. The primary key constraint specifies that each customer ID value should be unique in the table. Databricks does not enforce key constraints. They can be validated through your existing data pipeline or ETL. See Manage data quality with Delta Live Tables to learn about working expectations on streaming tables and materialized views. See Constraints on Databricks to learn about working with constraints on Delta tables.

Note

It is the user’s responsibility to check whether a constraint is satisfied. Relying on a constraint that is not satisfied can lead to incorrect query results.

Use RELY to enable optimizations

When you know that a primary key constraint is valid, you can enable optimizations based on the constraint by specifying it with the RELY option. See ADD CONSTRAINT clause for the complete syntax.

The RELY option allows Databricks to exploit the constraint to rewrite queries. The following optimizations can only be performed if the RELY option is specified in an ADD CONSTRAINT clause or ALTER TABLE statement.

Using ALTER TABLE, you can modify a table’s primary key to include the RELY option, as shown in the following example.

ALTER TABLE
  customer DROP PRIMARY KEY;
ALTER TABLE
  customer
ADD
  PRIMARY KEY (c_customer_sk) RELY;

Optimization examples

The following examples extend the previous example that creates a customer table where c_customer_sk is a verified unique identifier named as a PRIMARY KEY with the RELY option specified.

Example 1: Eliminate unnecessary aggregations

The following shows a query that applies a DISTINCT operation to a primary key.

SELECT
  DISTINCT c_customer_sk
FROM
  customer;

Because the c_customer_sk column is a verified PRIMARY KEY constraint, all values in the column are unique. With the RELY option is specified, Databricks can optimize the query by not performing the DISTINCT operation.

Example 2: Eliminate unnecessary joins

The following example shows a query where Databricks can eliminate an unnecessary join.

The query joins a fact table, store_sales with a dimension table, customer. It performs a left outer join, so the query result includes all records from the store_sales table and matched records from the customer table. If there is no matching record in the customer table, the query result shows a NULL value for the c_customer_sk column.

SELECT
  SUM(ss_quantity)
FROM
  store_sales ss
  LEFT JOIN customer c ON ss.customer_sk = c.c_customer_sk;

To understand why this join is unnecessary, consider the query statement. It requires only the ss_quantity column from the store_sales table. The customer table is joined on its primary key, so each row of store_sales matches at most one row in customer. Because the operation is an outer join, all records from the store_sales table are preserved, so the join does not change any data from that table. The SUM aggregation is the same whether or not these tables are joined.

Using the primary key constraint with RELY gives the query optimizer the information it needs to eliminate the join. The optimized query looks more like this:

SELECT
  SUM(ss_quantity)
FROM
  store_sales ss

Next steps

See View the Entity Relationship Diagram to learn how to explore primary key and foreign key relationships in the Catalog Explorer UI.