COF-C03 Question 35
Single answerFile formatsA data engineering team needs to load daily partner files into a Snowflake table. The files are CSV and contain fields enclosed in double quotes. Some rows include embedded commas inside quoted strings, and a few text fields span multiple lines within the quotes. The team wants to use a named file format so the same settings can be reused across stages and COPY commands. Which file format definition will correctly parse these files with the least risk of splitting records incorrectly?
- A
TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1
- B
TYPE = CSV FIELD_DELIMITER = ',' FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1
- C
TYPE = CSV FIELD_DELIMITER = ',' FIELD_OPTIONALLY_ENCLOSED_BY = '"' MULTI_LINE = TRUE SKIP_HEADER = 1
- D
TYPE = CSV RECORD_DELIMITER = '\n' ESCAPE_UNENCLOSED_FIELD = NONE SKIP_HEADER = 1
Show answer and explanation
Correct answer: C
Explanation
For CSV data in Snowflake, the file format must match how the source file is structured. When fields are enclosed in double quotes and may contain commas, FIELD_OPTIONALLY_ENCLOSED_BY must be set so Snowflake recognizes those delimiters as part of the field content rather than separators. When fields can span multiple lines, MULTI_LINE = TRUE is required so embedded line breaks within quoted values do not prematurely terminate the record. This is a common real-world requirement when loading CSV exports from external systems. Using a named file format with these settings is a best practice because it centralizes parsing rules and enables reuse across stages, COPY INTO commands, and external table definitions where applicable. These behaviors are documented in Snowflake's file format options for CSV, particularly FIELD_OPTIONALLY_ENCLOSED_BY and MULTI_LINE.
- A. Incorrect.
Incorrect. This definition handles basic comma-delimited data but does not tell Snowflake that fields may be enclosed in double quotes. Without FIELD_OPTIONALLY_ENCLOSED_BY, embedded commas inside quoted strings can be interpreted as actual field delimiters, causing columns to shift during loading.
- B. Incorrect.
Incorrect. Adding FIELD_OPTIONALLY_ENCLOSED_BY = '"' is necessary for quoted CSV fields and embedded commas, but it is still incomplete for this scenario because some fields contain line breaks inside quoted text. Without MULTI_LINE = TRUE, Snowflake may treat embedded newlines as record boundaries rather than part of the field value.
- C. Correct.
Correct. This definition addresses both key parsing requirements: quoted CSV fields and multi-line values. FIELD_OPTIONALLY_ENCLOSED_BY = '"' ensures commas inside quoted strings are treated as data, and MULTI_LINE = TRUE allows line breaks within enclosed fields to be parsed as part of the same record. SKIP_HEADER = 1 is also appropriate when the first row is a header.
- D. Incorrect.
Incorrect. Setting RECORD_DELIMITER = '\n' does not solve the problem of commas and line breaks inside quoted fields. In fact, explicitly relying on newline-delimited records is risky when the source contains multi-line field values. ESCAPE_UNENCLOSED_FIELD = NONE is unrelated to correctly parsing quoted multi-line CSV content in this scenario.