COF-C03 Question 211
Single answerFile formatsA data engineering team needs to load semi-structured clickstream data from an external stage into a Snowflake table. The source files are newline-delimited JSON, and each file contains one complete JSON object per line. During testing, the team notices that when they use the default JSON file format, each entire file is loaded as a single row instead of one row per JSON object. Which file format setting should they configure to load each line as a separate JSON document?
- A
Set STRIP_OUTER_ARRAY = TRUE in the JSON file format
- B
Set MULTI_LINE = FALSE in the JSON file format
- C
Set COMPRESSION = AUTO in the JSON file format
- D
Set SKIP_HEADER = 1 in the JSON file format
Show answer and explanation
Correct answer: B
Explanation
Snowflake JSON file formats support different parsing behaviors depending on how the JSON is structured. When files contain one JSON object per line, the correct approach is to set MULTI_LINE = FALSE so each line is interpreted as an individual JSON document during loading. By contrast, STRIP_OUTER_ARRAY is intended for JSON files with a top-level array. COMPRESSION is unrelated to row parsing, and SKIP_HEADER is used with delimited files rather than JSON. This aligns with Snowflake documentation for CREATE FILE FORMAT and JSON format options, where MULTI_LINE determines whether JSON records may span multiple lines or should be treated as line-delimited.
- A. Incorrect.
Incorrect. STRIP_OUTER_ARRAY is used when a JSON file contains a single top-level array and you want Snowflake to treat each array element as a separate row. In this scenario, the files are newline-delimited JSON with one object per line, not a single outer array.
- B. Correct.
Correct. For newline-delimited JSON, setting MULTI_LINE = FALSE tells Snowflake to treat each line as a separate JSON object. If MULTI_LINE remains TRUE, Snowflake can treat the entire file as a single JSON document, which matches the behavior seen in the test.
- C. Incorrect.
Incorrect. COMPRESSION controls how Snowflake handles compressed input files, such as gzip or bz2. It does not affect how JSON records are split into rows.
- D. Incorrect.
Incorrect. SKIP_HEADER applies to delimited text formats such as CSV to skip initial header lines. It is not the setting used to parse newline-delimited JSON into separate rows.