Question 250
During a Snowflake architecture review, the team finds: A daily customer ingest inserts duplicate records because the target matching rule uses mutable display name rather than stable customer key. Which condition explains why the current approach fails?
Explanation
Correct Answer
The described symptoms indicate that the MERGE matching condition does not identify one logical business entity reliably. The appropriate resolution is: Use an approved stable unique key and correct matched and unmatched MERGE actions. Confirm with: Replaying the same source batch does not duplicate customers and updates target the intended records.
Incorrect Answers
A semi-structured VARIANT value requires JSON path navigation to access nested members. “Select the required nested attribute through Snowflake's supported path syntax and cast appropriately” would address that alternative cause, not the condition in this question: the MERGE matching condition does not identify one logical business entity reliably.
“Choose appropriate UNION or explicit deduplication based on the transaction identity rule” responds to this separate finding: uNION ALL retains duplicate rows instead of applying distinct set semantics. This question instead needs “Use an approved stable unique key and correct matched and unmatched MERGE actions”, so the alternative does not meet the stated condition.
Array elements need explicit flattening to become relational rows. “Use LATERAL FLATTEN on the VARIANT array and select required element fields” applies to that separate problem. The observed case is a daily customer ingest inserts duplicate records because the target matching rule uses mutable display name rather than stable customer key; choosing this alternative would address the wrong failure mode.