Question 460
A Snowflake platform team receives the following requirement: A JSON document stored in a VARIANT column contains customer.address.city, but an analyst treats it as a flat VARCHAR cell. Which condition explains why the current approach fails?
Explanation
Correct Answer
The described symptoms indicate that a semi-structured VARIANT value requires JSON path navigation to access nested members. The appropriate resolution is: Select the required nested attribute through Snowflake's supported path syntax and cast appropriately. Confirm with: A sample nested city value is returned as the expected text without rewriting raw JSON.
Incorrect Answers
UNION ALL retains duplicate rows instead of applying distinct set semantics. “Choose appropriate UNION or explicit deduplication based on the transaction identity rule” would address that alternative cause, not the condition in this question: a semi-structured VARIANT value requires JSON path navigation to access nested members.
“Convert source times using appropriate timestamp types and a documented common time zone” responds to this separate finding: the transformation has not normalized time zone semantics before comparison. This question instead needs “Select the required nested attribute through Snowflake's supported path syntax and cast appropriately”, so the alternative does not meet the stated condition.
The MERGE matching condition does not identify one logical business entity reliably. “Use an approved stable unique key and correct matched and unmatched MERGE actions” applies to that separate problem. The observed case is a JSON document stored in a VARIANT column contains customer.address.city, but an analyst treats it as a flat VARCHAR cell; choosing this alternative would address the wrong failure mode.