Question 210
An analytics engineering team reports this behavior: A temperature feed stores numeric readings and string labels in the same VARIANT field, causing arithmetic on a string payload to fail. Which underlying issue should be investigated first?
Explanation
Correct Answer
The described symptoms indicate that the query assumes one stable scalar type across heterogeneous JSON values. The appropriate resolution is: Inspect TYPEOF and use validated casts or conditional logic for supported numeric values. Confirm with: Numeric readings calculate correctly while nonnumeric cases are handled without an untracked error.
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 query assumes one stable scalar type across heterogeneous JSON values.
“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 “Inspect TYPEOF and use validated casts or conditional logic for supported numeric values”, 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 temperature feed stores numeric readings and string labels in the same VARIANT field, causing arithmetic on a string payload to fail; choosing this alternative would address the wrong failure mode.