SnowPro Advanced: Architect free question 4 / 10 · Domain 2: Snowflake Architecture
In a star schema built on standard tables, PRIMARY KEY is defined on dim_products.product_id and a FOREIGN KEY on fact_sales.product_id references dim_products; both constraints are set to RELY. Because of a load mistake, two rows with the same product_id are later inserted into dim_products. The query SELECT p.product_id, f.units_sold FROM fact_sales f, dim_products p WHERE f.product_id = p.product_id is then run. Which statement is consistent with the official documentation?
- The duplicate row is accepted, and the query result differs from the result of the same query run with NORELY because the join is removed
- The duplicate row is accepted, but the primary key is VALIDATE by default, so the duplicate is detected before the rewrite and the join is kept
- The INSERT that adds the duplicate row fails because it violates the RELY primary key, so the table keeps its original number of rows
- The duplicate row is accepted and the query returns the same result as with NORELY; the difference appears in the data that DML and CTAS write
Show the answer and explanation
Answer: A
Why this is correct
On standard tables Snowflake does not enforce UNIQUE, PRIMARY KEY, or FOREIGN KEY constraints, so the duplicate product_id row is simply stored. RELY tells the optimizer that it may use a constraint in the NOVALIDATE state to rewrite queries. Because both the primary key and the foreign key are RELY and the query uses no column of dim_products other than the key, the join to dim_products is eliminated, as in example 3 of the documentation. The documentation says that keeping the data consistent with the constraint is the user's responsibility, and that if it is not, a query with RELY can return a different result from the same query with NORELY. Here the duplicate would multiply rows in the join under NORELY, but with RELY the join is removed and the rows are not multiplied, so the results differ.
Why the other options are wrong
"The INSERT that adds the duplicate row fails because it violates the RELY primary key, so the table keeps its original number of rows" … On standard tables a primary key is not enforced even when it is RELY. Enforcement of primary keys belongs to hybrid tables; on a standard table the duplicate row is inserted without error.
"The duplicate row is accepted, but the primary key is VALIDATE by default, so the duplicate is detected before the rewrite and the join is kept" … In the documentation's table of constraint properties, the default for primary and foreign keys is NOVALIDATE; VALIDATE is the default only for CHECK constraints. RELY decides whether a NOVALIDATE constraint may be used for rewrites. It is not a mechanism that examines existing rows before the rewrite.
"The duplicate row is accepted and the query returns the same result as with NORELY; the difference appears in the data that DML and CTAS write" … The documentation does say that DML and CTAS can load wrong data when a RELY constraint is not honored, but it also says that query results can differ from the NORELY results. The difference shows up in SELECT results as well.
Key point
Add RELY only after you have confirmed that the data follows the constraint. When a primary key and a foreign key work as a pair, the documentation's examples set RELY on both constraints.