4.3 Relational Constraints & Query Optimizer Integration

4.3 Relational Constraints & Query Optimizer Integration

In traditional relational database management systems (RDBMS) like Oracle, SQL Server, or PostgreSQL, constraints serve two concurrent purposes: they strictly enforce data integrity at write time (rejecting invalid inserts) and they provide structural metadata to the query optimizer. In Snowflake, however, constraint architecture is fundamentally decoupled from write-time validation.

For the SnowPro Advanced: Architect exam, you must master the critical distinction between enforced and informative (declarative) constraints, understand the ENABLE, VALIDATE, and RELY constraint properties, how RELY empowers the optimizer to execute Join Elimination, and recognize the catastrophic risks of silent data corruption when constraints are misused.

Relational Constraints in Snowflake: Enforced vs. Declarative Realities

Snowflake supports standard ANSI SQL constraint declarations, but their enforcement behavior differs radically from traditional transactional databases:

-- Creating a table with primary, unique, foreign key, and not null constraints
CREATE TABLE sales_dw.marts.dim_customer (
    customer_key NUMBER(38,0) NOT NULL,
    customer_id VARCHAR(64) NOT NULL,
    email VARCHAR(255),
    registration_date DATE,
    CONSTRAINT pk_customer PRIMARY KEY (customer_key),
    CONSTRAINT uq_customer_id UNIQUE (customer_id)
);

CREATE TABLE sales_dw.marts.fact_sales (
    sale_id NUMBER(38,0) NOT NULL,
    customer_key NUMBER(38,0) NOT NULL,
    amount NUMBER(12,2) NOT NULL,
    CONSTRAINT pk_sales PRIMARY KEY (sale_id),
    CONSTRAINT fk_sales_customer FOREIGN KEY (customer_key) 
        REFERENCES sales_dw.marts.dim_customer (customer_key)
);

The Core Enforcement Rule

Architectural Note: Snowflake's primary analytical tables are designed for high-concurrency, petabyte-scale distributed ingestion. Enforcing unique or foreign key constraints at insert time across hundreds of compute nodes would require global distributed row locking and continuous network coordination, severely throttling write throughput. Consequently, Snowflake makes primary, unique, and foreign keys informative rather than enforced on standard tables; only NOT NULL and CHECK are enforced.

The ENABLE, VALIDATE, and RELY Constraint Properties

Snowflake accepts the Oracle-style properties ENABLE | DISABLE, VALIDATE | NOVALIDATE, and RELY | NORELY (listed in the blueprint as ENABLE/RELY/VALIDATE). On standard tables these properties exist mainly to ease migrations: they are not enforced or maintained, and changing any default other than RELY causes Snowflake not to create the constraint.

The only property you normally change on standard tables is RELY:

ALTER TABLE sales_dw.marts.dim_customer 
  ADD CONSTRAINT pk_customer PRIMARY KEY (customer_key) RELY;

ALTER TABLE sales_dw.marts.fact_sales 
  ADD CONSTRAINT fk_sales_customer FOREIGN KEY (customer_key) 
  REFERENCES sales_dw.marts.dim_customer (customer_key) RELY;

1. NOVALIDATE vs. VALIDATE

• Default Behavior: Primary and foreign keys default to NOVALIDATE — Snowflake does not scan existing rows to prove the constraint holds.
• On standard tables, requesting VALIDATE (a non-default value) means the constraint is not created, because Snowflake does not maintain that property.
• On hybrid tables, adding a UNIQUE or FOREIGN KEY constraint always validates existing rows, regardless of this property.

2. NORELY vs. RELY (The Optimizer Switch)

-- Modifying existing constraints to enable RELY (set it on both related keys)
ALTER TABLE sales_dw.marts.dim_customer ALTER CONSTRAINT pk_customer RELY;
ALTER TABLE sales_dw.marts.fact_sales ALTER CONSTRAINT fk_sales_customer RELY;

-- Inspecting constraint properties and RELY status
SHOW PRIMARY KEYS IN TABLE sales_dw.marts.dim_customer;
-- Output column 'rely' indicates 'true' or 'false'

Cost-Based Optimizer Integration & Join Elimination Mechanics

Modern Business Intelligence (BI) tools (e.g., Tableau, Looker, Power BI) frequently generate automated SQL queries that join a central fact table to 10 or 15 dimension tables. Often, a specific dashboard widget or report projects columns from only the fact table and one or two dimensions, leaving several joined dimension tables completely unreferenced in the SELECT list and WHERE clauses.

The Join Elimination Optimization

Consider the following analytical query:

SELECT 
    f.order_date,
    SUM(f.sales_amount) AS daily_revenue
FROM sales_dw.marts.fact_sales f
LEFT JOIN sales_dw.marts.dim_customer c 
    ON f.customer_key = c.customer_key
GROUP BY f.order_date;

Notice that the query joins dim_customer, but zero columns from dim_customer are projected in the SELECT list or filtered in a WHERE clause.

What Happens Under NORELY (Default):

1. The optimizer does not know whether customer_key in dim_customer is unique.
2. If dim_customer contains duplicate customer_key values, the LEFT JOIN would duplicate rows from fact_sales (a cartesian fan-out), altering the sum of sales_amount.
3. Because the optimizer cannot guarantee that dim_customer is unique, it must physically execute the join. It scans dim_customer, builds a hash table in memory, and scans fact_sales, burning unnecessary virtual warehouse credits and compute time.

What Happens Under RELY:

1. The architect has marked both pk_customer and fk_sales_customer with RELY.
2. The optimizer verifies that:
   • dim_customer.customer_key is unique (guaranteed by RELY on the Primary Key).
   • Every row in fact_sales joins to at most one row in dim_customer.
   • No columns from dim_customer are required in the output.
3. The optimizer performs Join Elimination: it completely excises dim_customer from the physical execution plan!
4. In the Snowflake Query Profile, the table scan for dim_customer, the hash table build operator, and the join operator completely disappear. The query runs as a simple, lightning-fast scan against fact_sales alone.

Production Governance, Silent Data Corruption Risks, and Exam Traps

While RELY delivers immense performance benefits for enterprise BI queries, it introduces the most dangerous trap in Snowflake data architecture: silent data corruption.

The Catastrophic Risk of Erroneous RELY Configurations

If an architect enables RELY on a table where the upstream ETL/ELT pipeline fails to enforce referential integrity or uniqueness, the optimizer will rewrite queries under false assumptions, producing mathematically incorrect results without throwing any error.

Failure Scenario 1: Orphan Fact Records (Referential Integrity Violation)

Suppose fact_sales contains rows where customer_key = 9999, but key 9999 does not exist in dim_customer. Both tables have constraints configured with RELY.

• Query A (Join Eliminated):

  SELECT SUM(f.sales_amount) 
  FROM fact_sales f 
  JOIN dim_customer c ON f.customer_key = c.customer_key;
  

  Because no columns from c are queried, the optimizer eliminates the JOIN. It calculates the sum across all fact rows, including the orphan rows with customer_key = 9999. Revenue reported: $10,000,000.
• Query B (Join Forced):

  SELECT c.region, SUM(f.sales_amount) 
  FROM fact_sales f 
  JOIN dim_customer c ON f.customer_key = c.customer_key
  GROUP BY c.region;
  

  Because c.region is selected, the optimizer cannot eliminate the join. The INNER JOIN executes physically and filters out the orphan rows. Total revenue summed across all regions: $9,500,000.
• The Disaster: Two queries that should logically calculate the same total return contradictory figures! Neither query errored out, leading to silent executive reporting discrepancies.

Failure Scenario 2: Duplicate Keys in Dimension (Uniqueness Violation)

If duplicate customer keys exist in dim_customer, Query A (join eliminated) returns unmultiplied sums, while Query B (join executed) multiplies rows, producing severe data inflation.

Architectural Best Practices for Constraints

1. Enforce Upstream First: Never set RELY on Snowflake tables unless upstream data pipelines (e.g., dbt tests, automated staging validation, or Great Expectations) strictly assert primary key uniqueness and foreign key integrity before publishing data to production tables.
2. Emergency Mitigation: If an integrity violation is detected in production, immediately downgrade constraints to NORELY to force physical joins until the data is remediated:

   ALTER TABLE sales_dw.marts.dim_customer ALTER CONSTRAINT pk_customer NORELY;
   

3. Third-Party Tool Metadata: Informative constraints (even with NORELY) are invaluable for data modeling tools, data catalogs (e.g., Alation, Collibra), and BI semantic layers (e.g., Looker LookML, Power BI DirectQuery) to infer correct relationship topologies automatically.