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

Question

A data engineer is tasked with loading JSON data representing customer interactions into Snowflake. The JSON files contain varying schemas and nested arrays. To optimize query performance and minimize storage costs, which approach is MOST appropriate for handling the semi-structured data during loading, considering efficient data access patterns?

Options

• ALoad the JSON data directly into a VARIANT column without any transformations and use
• BCreate a relational schema based on the most common JSON structure and load only those fields
• CUse a CREATE VIEW statement to flatten and transform the VARIANT column into a relational
• DUtilize Snowflake's schema detection feature during loading to automatically create a relational
• EParse and pre-process the JSON data outside Snowflake to create a consistent relational structure

Explanation

Snowflake's schema detection during loading automatically creates a relational table based on the JSON data's structure, assigning appropriate data types. This avoids the overhead of manual schema definition and data transformation. While VARIANT can be used initially, schema detection provides a structured approach for querying semi-structured data. Choosing a relational schema upfront and discarding extra fields (B) leads to data loss. Using a view on a VARIANT column adds query overhead. Pre-processing outside Snowflake adds complexity.

No community discussion yet for this question.