COF-C03 Question 36
Single answerFile formatsA data engineering team loads daily partner files from an external stage into a Snowflake table. The partner recently switched from CSV to JSON and now sends one JSON document per file. Each document contains a top-level array named "orders" with thousands of order objects. The current COPY INTO command loads 0 rows without errors when using a JSON file format. The team wants each order object to be loaded as a separate row into a VARIANT column with minimal changes to the ingestion process. What should they do?
- A
Set the JSON file format option STRIP_OUTER_ARRAY = TRUE and use COPY INTO to load the files
- B
Set the JSON file format option MULTI_LINE = FALSE so Snowflake splits the array elements into separate rows
- C
Change the file format to AVRO because Snowflake can automatically flatten top-level JSON arrays only for semi-structured binary formats
- D
Keep the current JSON file format and add ON_ERROR = CONTINUE so Snowflake skips the top-level array wrapper
Show answer and explanation
Correct answer: A
Explanation
When loading JSON into Snowflake, file format options determine how the parser interprets the document structure. If a file contains a top-level array and the goal is to load each array element as a separate row, the JSON file format should use STRIP_OUTER_ARRAY = TRUE. This is a common ingestion pattern for semi-structured data loaded into a VARIANT column. MULTI_LINE addresses line formatting, not array expansion, and ON_ERROR governs error handling rather than parsing valid JSON into rows. Snowflake documentation for CREATE FILE FORMAT and COPY INTO describes JSON file format options such as STRIP_OUTER_ARRAY and MULTI_LINE, which are key to choosing the correct behavior during staged file ingestion.
- A. Correct.
Correct. For JSON loads, STRIP_OUTER_ARRAY = TRUE tells Snowflake to remove the outer array and load each element of that array as a separate row. In this scenario, that allows each order object inside the top-level "orders" array to be ingested as an individual row in a VARIANT column with minimal process changes. This is the standard approach when JSON files contain a single top-level array that should be split into multiple rows during loading.
- B. Incorrect.
Incorrect. MULTI_LINE for JSON controls whether records can span multiple lines in the file, not whether array elements are split into separate rows. A top-level array remains a single JSON structure unless STRIP_OUTER_ARRAY is used. Someone might choose this option because the file contains large JSON content across lines, but line breaks are not the issue here.
- C. Incorrect.
Incorrect. There is no requirement to convert the source to AVRO for this use case. Snowflake supports loading JSON directly into VARIANT, and STRIP_OUTER_ARRAY is specifically available for JSON file formats. This option reflects a misconception that changing to another semi-structured format is necessary to handle arrays.
- D. Incorrect.
Incorrect. ON_ERROR = CONTINUE only affects how COPY handles malformed or problematic records. It does not alter valid JSON structure or remove a top-level array wrapper. Because the file is valid JSON, this setting would not cause Snowflake to split the array into separate rows. This is a common misunderstanding between error-handling options and file-format parsing options.