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 ex

Question

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

• B
  6% (1)
• C
  72% (13)
• D
  17% (3)
• E
  6% (1)

Explanation

Snowflake's COPY INTO statement supports inline transformations using JSON path expressions (e.g., $1:review_id::string, $1:customer_id::string, $1:review_text::string) to extract specific fields from semi-structured JSON data during the load itself. This approach is efficient because the transformation happens at load time in a single pass without requiring a separate processing step. It handles varying schemas gracefully by extracting only the target fields. Option A (loading into VARIANT first) requires a two-step process with a subsequent INSERT. Option B (separate tables per schema variation) is operationally complex and does not scale. Option D (Python UDF) adds overhead. Option E cannot work reliably when the schema varies.

No community discussion yet for this question.