Copied to Clipboard
),
(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:
- Drop unnecessary dimensions from analytical queries that only reference key columns.
- Short‐circuit joins and reduce the number of operators in the plan, which often lowers merge‐join/hash‐join overhead.
- 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