Which syntax correctly extracts the city field as a string in a query using a lateral flatten on a VARIANT column?

• A. TEMPVAL:city::STRING AS city
• B. TEMPVAL.city::STRING AS city
• C. TEMPVAL:city->STRING AS city
• D. TEMPVAL:CITY::STRING AS city

Answer: (A)

In Snowflake, when you’ve flattened a VARIANT column, you access fields inside each JSON-like element using the colon path syntax and then cast to the desired type. The correct pattern pulls the city field from the current element with TEMPVAL:city and converts it to a string with ::STRING, giving a clean text column named city. This colon-based path is how Snowflake drills into semi-structured data; it treats the keys inside the VARIANT as path components.

Why other forms don’t fit: using a dot notation like TEMPVAL.city tries to treat the VARIANT as a structured OBJECT and access a field directly, which isn’t the standard way to navigate a VARIANT in this context. The arrow-based form isn’t the right combination of path extraction and type casting here. And spelling the key as CITY (uppercase) would look for a different key that likely doesn’t exist, resulting in null.