SOL-C01 Question #110: Real Exam Question with Answer & Explanation

Question

You are tasked with loading JSON data containing customer information into Snowflake. The JSON structure is complex and varies across records. You want to optimize query performance on a frequently accessed nested field 'address.city'. Which of the following strategies would BEST improve query performance?

Options

• ALoad the JSON data directly into a VARIANT column without any transformation.
• BCreate a separate table with a VARCHAR column for 'address.city' and use a view to join it with
• CCreate a virtual column on the VARIANT column that extracts 'address.city' and index the virtual
• DFlatten the JSON data during loading using a query that extracts 'address.city' into a separate
• EUse the LATERAL FLATTEN function to create a separate row for each field in the JSON,

Explanation

Flattening the JSON data during loading and creating a standard index on the 'address.city' column provides the best query performance because Snowflake can directly use the index to filter and retrieve data without having to parse the JSON structure at query time. Virtual columns can provide some performance improvement, but they are not as efficient as standard indexes. Creating a separate table or using LATERAL FLATTEN and pivoting adds unnecessary complexity and overhead.

No community discussion yet for this question.