ARA-C01 Question 127
Single answerUse of key/column constraints (ENABLE/RELY/VALIDATE)A data architect is modeling a sales star schema in Snowflake. The FACT_SALES table is loaded from multiple upstream systems, and the DIM_CUSTOMER table is maintained through a curated MDM process. BI queries frequently join FACT_SALES to DIM_CUSTOMER on CUSTOMER_ID. The architect wants the optimizer to be able to use the declared relationship for query rewrite opportunities, but the team does not want Snowflake to spend time scanning historical data to verify the constraint during deployment. Which action best meets these requirements?
- A
Create the foreign key on FACT_SALES(CUSTOMER_ID) referencing DIM_CUSTOMER(CUSTOMER_ID) with RELY so Snowflake can assume the relationship for optimization without validating existing data.
- B
Create the foreign key with ENABLE VALIDATE so Snowflake enforces referential integrity on all future DML and avoids deployment-time validation scans.
- C
Create the foreign key with ENABLE NOVALIDATE so Snowflake skips validation but still enforces the relationship on subsequent inserts and updates.
- D
Create the foreign key without RELY because Snowflake uses all declared constraints for optimization automatically, even when they are not trusted.
Show answer and explanation
Correct answer: A
Explanation
For Snowflake Architect scenarios, it is important to distinguish between declared constraints used as metadata and actually enforced constraints. On standard Snowflake tables, primary key, unique key, and foreign key constraints are typically informational and are not enforced like in many traditional OLTP databases. The RELY property is significant because it tells Snowflake that the optimizer may trust the constraint for query optimization and rewrite decisions. VALIDATE implies checking data against the constraint, which the scenario explicitly wants to avoid due to deployment overhead on historical data. A common misconception is to assume ENABLE or NOVALIDATE gives Oracle-like runtime enforcement behavior in Snowflake; that is not the practical design pattern for standard table constraints. Best practice is to use RELY only when the data pipeline or governance process ensures the relationship is actually true, since incorrect trusted constraints can lead to wrong optimization assumptions. This aligns with Snowflake documentation on constraint properties and optimizer use of trusted constraints.
- A. Correct.
Correct. In Snowflake, standard key constraints on regular tables are informational rather than enforced. Setting a constraint to RELY indicates that Snowflake may rely on the constraint for query rewrite/optimization purposes. This is appropriate when the data team trusts the relationship but does not want Snowflake to validate existing rows during deployment. This matches the requirement to help the optimizer without incurring validation overhead.
- B. Incorrect.
Incorrect. This reflects a common assumption from traditional RDBMS platforms. In Snowflake, key constraints on standard tables are generally not enforced for DML in the way ENABLE VALIDATE implies in other systems. Also, VALIDATE would contradict the requirement to avoid scanning historical data during deployment.
- C. Incorrect.
Incorrect. This is another plausible distractor based on Oracle-style semantics. In Snowflake, ENABLE/NOVALIDATE is not used to provide ongoing referential integrity enforcement for standard table key constraints. The requirement is specifically about optimizer trust without validation, which is addressed by RELY, not by assuming future DML enforcement.
- D. Incorrect.
Incorrect. Snowflake does not simply treat every declared constraint as trusted for optimization. RELY is used to indicate that the optimizer can trust the constraint. Omitting RELY means the architect is not explicitly marking the relationship as usable for those rewrite opportunities.