ARA-C01 Question 126
Single answerUse of key/column constraints (ENABLE/RELY/VALIDATE)A retail company is migrating a star-schema analytics workload from an on-premises database to Snowflake. The BI team runs many join-heavy queries between SALES_FACT and DIM_CUSTOMER. During migration testing, the architect discovers that duplicate CUSTOMER_ID values exist in DIM_CUSTOMER because upstream cleansing is still in progress. The team wants to preserve data loading performance, avoid immediate full data validation, and only allow the optimizer to use the key relationship after the duplicates are fixed and the data can be trusted. Which action should the architect take?
- A
Create the PRIMARY KEY on DIM_CUSTOMER(CUSTOMER_ID) and FOREIGN KEY on SALES_FACT(CUSTOMER_ID) with RELY so Snowflake can use the constraints for optimization immediately, even though duplicates currently exist.
- B
Create the PRIMARY KEY and FOREIGN KEY with ENABLE and VALIDATE so Snowflake enforces the relationship during DML without requiring trust in the data.
- C
Create the PRIMARY KEY and FOREIGN KEY as metadata-only constraints without RELY while data issues are being remediated, and set RELY only after the data has been verified to satisfy the key relationship.
- D
Create UNIQUE and FOREIGN KEY constraints with NOVALIDATE so Snowflake will partially enforce new rows only, while ignoring existing duplicates until cleanup is complete.
Show answer and explanation
Correct answer: C
Explanation
In Snowflake, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints on standard tables are primarily metadata and are not enforced in the same way as in many traditional databases. This is important in migrations, because teams often assume ENABLE or VALIDATE will cause Snowflake to check existing and incoming rows for violations. For query optimization, RELY is the key property: it indicates that Snowflake can trust the constraint for optimizations such as join elimination. Because RELY can affect optimizer behavior, it should only be set when the underlying data actually conforms to the declared relationship. In this scenario, duplicates already exist in the dimension table, so the architect should avoid RELY until remediation and verification are complete. This aligns with Snowflake best practices for using constraints safely in analytics schemas: declare constraints for modeling clarity, but use RELY only when data quality supports it.
- A. Incorrect.
Incorrect. RELY tells Snowflake that the constraint can be relied on for query rewrite/optimization. If duplicate CUSTOMER_ID values currently exist, marking the key as RELY can lead the optimizer to make assumptions that are not valid for the actual data, potentially producing incorrect query results in join elimination scenarios. RELY should only be used when the data truly satisfies the declared constraint.
- B. Incorrect.
Incorrect. In standard Snowflake tables, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints are generally informational and not enforced. ENABLE and VALIDATE do not provide the same enforcement behavior that some traditional RDBMS platforms offer for these key constraints on standard tables. Choosing this option reflects a common migration misconception that Snowflake will actively enforce these relational constraints during DML.
- C. Correct.
Correct. For standard Snowflake tables, key constraints are typically metadata-only unless specifically relied upon by the optimizer. If the data is not yet clean, the architect should declare the constraints for documentation/modeling purposes but avoid RELY until the data has been remediated and verified. After the duplicates are fixed and trust is established, setting RELY allows the optimizer to use the declared relationship safely.
- D. Incorrect.
Incorrect. NOVALIDATE-style behavior for partially enforcing only future rows is a concept from some other database systems, not the practical behavior to depend on for Snowflake standard table key constraints. This option mixes familiar RDBMS terminology with an enforcement model Snowflake does not use for standard PRIMARY KEY/UNIQUE/FOREIGN KEY constraints.