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

Question

A data engineer needs to load JSON files containing customer reviews stored in an external stage 's3://my-bucket/reviews/'. The JSON structure varies significantly between files, but the goal is to extract 'review_id', 'customer_id', and 'review text' into a Snowflake table 'CUSTOMER REVIEWS'. The engineer is using a COPY INTO statement with a transform. Which of the following is the MOST efficient and correct way to achieve this?

Options

• AUse a VARIANT column in 'CUSTOMER_REVIEWS' and load the entire JSON structure. Then,
• BCreate separate tables for each JSON structure variation and then use a UNION ALL view to
• CUse a COPY INTO statement with a JSON path expression to extract the required fields during the
• DWrite a Python UDF to parse the JSON, extract the required fields, and then use the UDF in a
• ECreate a custom file format that defines the expected JSON structure, even though it varies, and

Explanation

Option C is the most efficient. Using a JSON path expression in the COPY INTO statement allows extracting the desired fields directly during the load, avoiding the need to load the entire JSON and then process it. 'NULLIF or similar functions can be used to handle cases where a field might be missing in some JSON documents. Options A, B, D, and E are less efficient or impractical. Loading the entire JSON (A) requires post-processing. Creating separate tables (B) is complex and doesn't scale well. Python UDF (D) introduces overhead and performance limitations. Relying on a custom format without explicitly handling variations (E) will lead to errors.

No community discussion yet for this question.