SnowPro Associate: Platform Question 285
Single answer○ File format optionsA data engineering team loads daily CSV files from an external stage into a Snowflake table using COPY INTO. A recent file failed to load correctly because some customer comments contain embedded line breaks and commas inside double quotes, for example: "Preferred contact is email, not phone\nCall after 5 PM". The team wants Snowflake to treat the quoted text as a single field and preserve the embedded newline within that field. Which file format option should be configured to address this issue most directly?
- A
Set FIELD_OPTIONALLY_ENCLOSED_BY='"' in the CSV file format
- B
Set SKIP_HEADER=1 in the CSV file format
- C
Set TRIM_SPACE=TRUE in the CSV file format
- D
Set ESCAPE_UNENCLOSED_FIELD='\' in the CSV file format
Show answer and explanation
Correct answer: A
Explanation
For CSV data that includes delimiters or record separators inside field values, Snowflake best practice is to define the file format so those values can be recognized as enclosed fields. The FIELD_OPTIONALLY_ENCLOSED_BY option is specifically designed for cases where fields may be wrapped in a quote character, such as standard CSV files. When properly configured, Snowflake can parse commas and embedded line breaks within quoted strings as part of the same field. Other file format options like SKIP_HEADER, TRIM_SPACE, and ESCAPE_UNENCLOSED_FIELD serve different purposes and do not directly solve multiline quoted field parsing. This aligns with Snowflake documentation for CREATE FILE FORMAT and COPY INTO behavior for CSV files.
- A. Correct.
Correct. FIELD_OPTIONALLY_ENCLOSED_BY tells Snowflake that fields may be enclosed by a quote character, such as a double quote. This allows commas and line breaks inside properly quoted fields to be interpreted as part of the field value rather than as delimiters or record terminators. In this scenario, that is the key requirement for loading CSV data with embedded commas and newlines in quoted text.
- B. Incorrect.
Incorrect. SKIP_HEADER controls how many header lines Snowflake skips at the start of a file. It does not affect parsing of embedded commas or line breaks inside quoted fields. A candidate might choose this if they associate load failures with CSV formatting generally, but it does not solve the issue described.
- C. Incorrect.
Incorrect. TRIM_SPACE removes leading and trailing spaces around fields during parsing, depending on the file content and enclosure handling. It is useful for cleaning whitespace but does not make Snowflake treat quoted multiline text as a single field. It would not address the root cause of this load problem.
- D. Incorrect.
Incorrect. ESCAPE_UNENCLOSED_FIELD specifies an escape character for field delimiters or special characters in fields that are not enclosed. In this scenario, the problematic values are enclosed in double quotes, so the primary fix is to configure the enclosure character. This option may be relevant in other CSV parsing cases, but it is not the most direct or appropriate solution here.