How Snowflake’s RELY Constraint Supercharges Your Star‑Schema Queries

Snowflake’s RELY constraint is a powerful but under‑used hint that tells the optimizer it can trust your primary keys and foreign keys and use them to eliminate unnecessary work—especially redundant joins. In this post, I’ll explain what RELY does, how it helps with join elimination, and walk you through concrete examples you can adapt for your own schemas.

Why this matters to a data engineer?

If you’ve ever used wide, reusable models with hundreds of columns designed as a single source of truth, you know how easily queries can accumulate unnecessary joins. RELY helps here by letting Snowflake eliminate redundant joins when they’re logically not needed, so queries only scan the tables that actually contribute to the result.

In Snowflake, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints on standard tables are not enforced by default; they are mainly metadata for humans and tools. However, Snowflake lets you add a RELY property on those constraints to signal:

I guarantee this constraint is true in my data, and you can use it to rewrite my queries.

You set RELY like this:

ALTER TABLE dim_customer
  ALTER PRIMARY KEY RELY;

ALTER TABLE fact_sales
  ALTER CONSTRAINT fk_customer_id RELY;

When does RELY kick in?

Snowflake can use RELY‑tagged constraints to eliminate unnecessary joins when:

• A table has a PRIMARY KEY or UNIQUE constraint marked RELY.
• A related table has a FOREIGN KEY constraint also marked RELY.
• The query joins on those keys, but doesn’t actually need any columns from one of the joined tables beyond the key itself.

In those situations, Snowflake can realize that the join is “logically redundant” and rewrite the plan to drop the table scan entirely.

This is a cost-saving measure as it rewrites the query to eliminate unnecessary tables, allowing for faster performance. Additionally, may allows us to use a smaller warehouse may help reduce overall costs.

Example 1: Simple join elimination with sample data

CREATE TABLE dim_customer (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR
);

CREATE TABLE fact_sales (
  sale_id INT,
  customer_id INT,
  amount DECIMAL(10,2),
  sale_date DATE,
  CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES dim_customer (customer_id)
);

Sample data:

INSERT INTO dim_customer VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

INSERT INTO fact_sales VALUES
  (101, 1, 100.00, '2026-01-01'),
  (102, 1, 150.00, '2026-01-05'),
  (103, 2, 200.00, '2026-01-03'),
  (104, 3, 300.00, '2026-01-04');

Initial query (no RELY):

SELECT
  f.customer_id,
  SUM(f.amount) AS total_amount
FROM fact_sales f
JOIN dim_customer d ON f.customer_id = d.customer_id
GROUP BY f.customer_id;

Output:

Query Plan:

Now enable RELY:

ALTER TABLE dim_customer
  ALTER PRIMARY KEY RELY;

ALTER TABLE fact_sales
  ALTER CONSTRAINT fk_customer_id RELY;

Under the hood, Snowflake sees:

dim_customer.customer_id is unique and trusted (PRIMARY KEY RELY).
fact_sales.customer_id is a trusted foreign key (fk_customer_id RELY).
The only column used from dim_customer is customer_id, which is already in fact_sales.

So the optimizer eliminates the join to dim_customer and rewrites the query plan like below:

The result is identical, but the execution plan no longer scans dim_customer at all.

Example 2: Join elimination with filters (and when it doesn’t kick in)

SELECT
  f.customer_id,
  SUM(f.amount) AS total_amount
FROM fact_sales f
JOIN dim_customer d ON f.customer_id = d.customer_id
WHERE d.customer_name = 'Alice'
GROUP BY f.customer_id;

Here, dim_customer.customer_name is used in the filter, so the join on dim_customer is not redundant even with RELY. The optimizer must still fetch dimension data to evaluate the WHERE clause, so join elimination does not occur.

Example 3: RELY in a multi‑dimension star schema

CREATE OR REPLACE TABLE dim_customer (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR
);

CREATE OR REPLACE  TABLE dim_channel (
  channel_id INT PRIMARY KEY,
  channel_name VARCHAR
);

CREATE OR REPLACE TABLE fact_sales (
  sale_id INT,
  customer_id INT,
  channel_id INT,
  amount DECIMAL(10,2),
  sale_date DATE,

  CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES dim_customer (customer_id),
    CONSTRAINT fk_channel FOREIGN KEY (channel_id) REFERENCES dim_channel (channel_id)
);

## Sample Data

-- dimensions
INSERT INTO dim_customer VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO dim_channel VALUES (1, 'Online'), (2, 'In‑store');

-- facts
INSERT INTO fact_sales VALUES
  (101, 1, 1, 100.00, '2026-01-01'),
  (102, 1, 2, 200.00, '2026-01-02'),
  (103, 2, 1, 150.00, '2026-01-03');

## Now Enable Rely

ALTER TABLE dim_customer
  ALTER PRIMARY KEY RELY;

ALTER TABLE dim_channel
  ALTER PRIMARY KEY RELY;

ALTER TABLE fact_sales
  ALTER CONSTRAINT fk_customer RELY;

ALTER TABLE fact_sales
  ALTER CONSTRAINT fk_channel RELY;

If you run a query that only aggregates by fact keys:

SELECT
  f.customer_id,
  f.channel_id,
  SUM(f.amount) AS total_amount
FROM fact_sales f
JOIN dim_customer d_c ON f.customer_id = d_c.customer_id
JOIN dim_channel d_ch ON f.channel_id = d_ch.channel_id
GROUP BY f.customer_id, f.channel_id;

Here, dim_channel and dim_customer are only used for their keys, which are already in fact_sales. With RELY, Snowflake can understand that:

• dim_customer.customer_id is unique and trusted.
• dim_channel.channel_id is unique and trusted.
• The join columns are already present in fact_sales.

So it can eliminate both joins, effectively turning the query into:

How RELY helps performance in practice
By enabling RELY on trusted PK/FK constraints, you give the optimizer permission to:

1. Drop unnecessary dimensions from analytical queries that only reference key columns.
2. Short‑circuit joins and reduce the number of operators in the plan, which often lowers merge‑join/hash‑join overhead.
3. Reduce memory and compute usage, especially for large fact tables that are joined repeatedly in BI or ad‑hoc workloads.

Important caveats and best practices

Because RELY is a trust‑me hint, a few caveats matter:

• You are responsible for enforcing UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints. If those constraints are violated, query results with RELY can differ from those with NORELY.
• Always validate data via ETL (e.g., dbt tests) before setting RELY, as violations can lead to suboptimal plans
• Timestamp_tz PKs or hybrid tables limit elimination