DAA-C01 Question 115
Single answerCSVA retail analytics team receives daily CSV exports from a third-party system and loads them into Snowflake. Recently, some rows have been rejected because the CUSTOMER_NOTES column contains embedded commas, double quotes, and line breaks. The source system encloses text fields in double quotes and escapes embedded double quotes by doubling them. The team wants to load the files correctly without changing the source extract process. Which file format definition is the best choice for this scenario?
- A
CREATE FILE FORMAT ff TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"' ESCAPE_UNENCLOSED_FIELD = NONE;
- B
CREATE FILE FORMAT ff TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"' ESCAPE = '\';
- C
CREATE FILE FORMAT ff TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1 RECORD_DELIMITER = '\n' TRIM_SPACE = TRUE;
- D
CREATE FILE FORMAT ff TYPE = CSV FIELD_DELIMITER = '|' SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = NONE ESCAPE = '"';
Show answer and explanation
Correct answer: B
Explanation
For CSV ingestion in Snowflake, the critical settings are the delimiter, header handling, and how quoted fields are parsed. When text fields can contain commas or line breaks, Snowflake must be told that fields may be enclosed, typically with FIELD_OPTIONALLY_ENCLOSED_BY = '"'. For enclosed fields, the ESCAPE parameter is used to interpret escape characters within those fields. This is why option 2 is the best fit among the choices. Options that only trim spaces, change the record delimiter, or alter the field delimiter do not address the actual parsing issue. In practice, Snowflake documentation on CREATE FILE FORMAT and CSV file format options should be referenced to validate how FIELD_OPTIONALLY_ENCLOSED_BY, ESCAPE, and ESCAPE_UNENCLOSED_FIELD behave during COPY INTO operations.
- A. Incorrect.
Incorrect. FIELD_OPTIONALLY_ENCLOSED_BY = '"' is appropriate for CSV files where fields may be wrapped in double quotes, but setting ESCAPE_UNENCLOSED_FIELD = NONE only affects unquoted fields. It does not address escaped quote characters inside enclosed fields. In this scenario, the source escapes embedded double quotes inside quoted fields, so the file format must support parsing those embedded quotes correctly.
- B. Correct.
Correct. This matches the stated CSV behavior: comma-delimited data, one header row, optional double-quote enclosure, and backslash escaping configured through ESCAPE. In Snowflake CSV file formats, FIELD_OPTIONALLY_ENCLOSED_BY handles fields containing delimiters or line breaks when enclosed, and ESCAPE applies to enclosed fields, which is what is needed when special characters appear inside quoted text. This is the best available option for correctly handling quoted CSV content with embedded commas, quotes, and newlines.
- C. Incorrect.
Incorrect. RECORD_DELIMITER = '\n' is common, but by itself it does not solve the core issue. When a CSV field contains embedded line breaks, Snowflake can still parse it correctly only when the field is properly enclosed and the file format supports that enclosure. TRIM_SPACE removes surrounding spaces but does not handle embedded quotes or multiline text in quoted CSV fields.
- D. Incorrect.
Incorrect. Changing FIELD_DELIMITER to '|' contradicts the source format, which is comma-delimited. Disabling field enclosure also prevents correct handling of commas and line breaks inside text fields. Setting ESCAPE = '"' is not an appropriate fix here because the file already relies on quoted CSV semantics and comma delimiters.