DAA-C01 Question 116
Single answerCSVA retail analytics team receives daily CSV files from a third-party system and loads them into a Snowflake stage. Several columns contain commas inside quoted text, such as product descriptions like "Large, blue shirt", and some rows contain embedded line breaks inside quoted customer comments. The team created this file format for use with COPY INTO:
CREATE OR REPLACE FILE FORMAT retail_csv_ff TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1;
When they load the files, records are split incorrectly and some rows are rejected. The team wants to parse the files correctly without changing the source files. Which file format change will best address the issue?
- A
Set FIELD_OPTIONALLY_ENCLOSED_BY = '"' on the file format.
- B
Set ESCAPE_UNENCLOSED_FIELD = NONE on the file format.
- C
Set RECORD_DELIMITER = ',' on the file format.
- D
Set SKIP_BLANK_LINES = TRUE on the file format.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to set FIELD_OPTIONALLY_ENCLOSED_BY = '"'. In Snowflake, CSV parsing depends on accurately defining how fields are delimited and optionally enclosed. When a file contains commas inside text values or newline characters within a field, Snowflake must know that the affected values are enclosed in quotes so that delimiters inside those quotes are not treated as structural separators. This is standard CSV behavior and is supported directly through Snowflake file format options used by COPY INTO and external stages. By contrast, options related to escape handling, blank lines, or record delimiters do not correct the fundamental parsing issue. This aligns with Snowflake documentation for CREATE FILE FORMAT and loading CSV data, which emphasizes using FIELD_OPTIONALLY_ENCLOSED_BY for quoted CSV content and embedded delimiters.
- A. Correct.
Correct. In Snowflake CSV file formats, FIELD_OPTIONALLY_ENCLOSED_BY tells Snowflake that fields may be wrapped in a quote character. This allows commas inside quoted strings to be treated as part of the field rather than as delimiters. It also enables proper handling of embedded line breaks when they occur inside enclosed fields, which is a common requirement for CSV files exported from business systems. In this scenario, the source files already contain quoted text, so adding this setting is the appropriate fix.
- B. Incorrect.
Incorrect. ESCAPE_UNENCLOSED_FIELD controls how escape characters are interpreted in fields that are not enclosed. It does not solve the main problem here, which is that commas and line breaks appear inside quoted fields. A candidate might choose this option if they confuse escaping with field enclosure, but Snowflake needs to know the enclosure character first.
- C. Incorrect.
Incorrect. RECORD_DELIMITER defines the character sequence that separates rows. Setting it to a comma would be invalid for this CSV structure because commas separate fields, not records. This would make parsing worse, not better. This distractor targets confusion between row delimiters and column delimiters.
- D. Incorrect.
Incorrect. SKIP_BLANK_LINES can help ignore empty lines in a file, but it does not address commas inside quoted text or embedded newlines within quoted fields. Someone might select this if they assume the rejected rows are due to spacing or blank-line issues, but the root cause is improper CSV enclosure handling.