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

Question

A data engineer is tasked with loading JSON files containing customer reviews from an external stage into a Snowflake table. The JSON files have varying schemas and nested structures. Which of the following methods is the MOST efficient and scalable way to ingest and query this data, minimizing the need for upfront schema definition?

Options

• Arequires defining a schema upfront which is not ideal for varying schemas. External tables
• BCreate a VARIANT column in a Snowflake table. Load the JSON data into the VARIANT column
• CCreate an external table using the CREATE EXTERNAL TABLE command, pointing to the external
• DUse a stored procedure to parse the JSON files and insert the data into a relational table with a
• ELoad the JSON data into a cloud storage location, then use a Spark cluster to process and

Explanation

Using a VARIANT column is the most efficient way to load JSON data with varying schemas because it allows you to load the data as is without defining a rigid schema upfront. Dot notation and the FLATTEN function allow you to query the data flexibly. Creating a relational table (Option (Option C) still require a schema definition. Stored procedures (Option D) can be complex and less scalable. Using Spark (Option E) adds unnecessary complexity and cost for this scenario.

No community discussion yet for this question.