Snowflake Constraints: ENABLE, VALIDATE and RELY

1.Which constraints Snowflake actually enforces

Snowflake supports the five ANSI constraint types: PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL and CHECK. A table can have several unique keys and foreign keys but only one primary key. Whether a constraint is enforced depends on the table type, not on how you declare it.

On standard tables, NOT NULL and CHECK are the only constraints Snowflake enforces, and that holds whatever the ENFORCED property says. A duplicate primary key value or an orphaned foreign key loads without any error. On hybrid tables you can't set NOT ENFORCED on PRIMARY KEY, FOREIGN KEY or UNIQUE constraints.

CHECK constraints are checked during INSERT, UPDATE, MERGE and CREATE TABLE … AS SELECT. If the condition evaluates to TRUE or NULL, the operation goes ahead. If it evaluates to FALSE, the operation fails. This matters for ingestion design: a COPY INTO a table with CHECK constraints fails, and so does creating a pipe that targets one.

Sources12

2.ENABLE, VALIDATE and RELY: Snowflake's extension properties

Key constraints accept a set of properties: [NOT] ENFORCED, [NOT] DEFERRABLE, INITIALLY {DEFERRED | IMMEDIATE}, ENABLE | DISABLE, VALIDATE | NOVALIDATE, and RELY | NORELY. The last three are Snowflake extensions, not ANSI properties. On standard tables, these properties are there to make migrating from other databases easier, and Snowflake neither enforces nor maintains them. You can change their defaults, but if you do, Snowflake doesn't create the constraint. The one exception is RELY.

CHECK constraints use ENABLE with either VALIDATE or NOVALIDATE. ENABLE VALIDATE is the default. It applies the check to existing rows and to every later insert or update, but you can only use it on new tables. ENABLE NOVALIDATE applies the check only to rows inserted or updated after you run the command. If you later switch a CHECK from NOVALIDATE to VALIDATE, Snowflake checks all existing rows before making the change.

After a constraint exists, you can change some properties, such as RELY, with ALTER TABLE. Others, such as DEFERRABLE, can't be changed: you have to drop the constraint and create it again.

ALTER TABLE <table_name>
  { ALTER | MODIFY } {
      CONSTRAINT <name>
    | PRIMARY KEY
    | { UNIQUE | FOREIGN KEY } (<column_name>, [ ... ] )
  }
  { [ [ NOT ] ENFORCED ] [ VALIDATE | NOVALIDATE ] [ RELY | NORELY ] };

Sources213

3.RELY and join elimination

SELECT f.*
FROM fact_sales f
LEFT OUTER JOIN dim_products p
ON f.product_id = p.product_id;

The join is unnecessary because the query doesn't use any dim_products column apart from the join key. If dim_products.product_id carries a UNIQUE or PRIMARY KEY constraint with RELY, Snowflake drops the reference to dim_products. The documentation shows the same optimization for an unnecessary self-join and for an unnecessary join on a primary key and foreign key. When a primary key and a foreign key are related, set RELY on both of them. Client tools can benefit too: Tableau, for example, uses constraints for join culling.

RELY doesn't check anything. It tells the optimizer to trust that the data meets the constraint, and making sure that's true is your job. If the data breaks the constraint, a query can return different results with RELY than with NORELY. It's worse for writes: DML and CTAS statements may insert incorrect data. The safe pattern is to verify integrity in the pipeline, set RELY, and keep verifying.

Sources425

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Declaring a PRIMARY KEY or FOREIGN KEY on a standard table stops duplicate or orphaned rows from being loaded.Why is that wrong?

   On standard tables, only NOT NULL and CHECK are enforced. Key constraints are informational, and only hybrid tables enforce them.

   Covered in Which constraints Snowflake actually enforces

2. 2.Setting RELY makes Snowflake validate the data against the constraint.Why is that wrong?

   RELY only lets the optimizer take the constraint into account when it rewrites queries. Making sure the data complies is still your responsibility.

   Covered in RELY and join elimination

3. 3.You can add a CHECK constraint with ENABLE VALIDATE to an existing table to check the rows already in it.Why is that wrong?

   ENABLE VALIDATE is only supported for new tables. On an existing table, ENABLE NOVALIDATE checks only rows inserted or updated afterwards.

   Covered in ENABLE, VALIDATE and RELY: Snowflake's extension properties

Practise it for real

Show that key constraints on standard tables are informational metadata, then mark them for the optimizer to use with RELY

1. 1.Run the CREATE OR REPLACE TABLE statements for salespeople and salesorders from Snowflake's table design documentation

   Why: These give you a primary key and an out-of-line foreign key between two standard tables

   You should see: Both tables are created, with constraints PK_SP_ID, PK_ORDER_ID and FK_SP_ID

2. 2.Insert a row into salesorders whose sp_id has no matching row in salespeople

   Why: This shows that FOREIGN KEY constraints are not enforced on standard tables

   You should see: The insert succeeds without an error

3. 3.Run SELECT GET_DDL('TABLE', 'mydb.public.salesorders'); using your own database name

   Why: GET_DDL returns the constraints currently defined on the table

   You should see: Key constraints appear as out-of-line clauses. The unnamed unique constraint is shown without its system-generated name.

4. 4.Query INFORMATION_SCHEMA.TABLE_CONSTRAINTS for the schema, filtering on constraint_schema = 'PUBLIC'

   Why: This lists every table constraint in the schema, which is useful for auditing a model

   You should see: A row for each constraint, including SYS_CONSTRAINT_ names for the unnamed UNIQUE constraints

5. 5.Delete the orphaned row, then use ALTER TABLE … ALTER CONSTRAINT … RELY on both pk_sp_id and fk_sp_id

   Why: Set RELY only after the data complies, and set it on both related constraints

   You should see: Both constraints now have the RELY property. No data is checked when you do this.