SnowPro Associate: Platform Question 284
Single answer○ File format optionsA data engineering team loads daily CSV files from an external stage into a Snowflake table using COPY INTO. The source system recently started enclosing some text fields in double quotes because the fields can contain commas, line breaks, and leading/trailing spaces. After the change, some rows are split incorrectly and some values lose their surrounding spaces. The team wants to fix the load by updating the file format rather than changing the source files. Which file format option should they configure to correctly handle the quoted fields while preserving the spaces inside the quotes?
- A
Set FIELD_OPTIONALLY_ENCLOSED_BY = '"'
- B
Set SKIP_HEADER = 1
- C
Set TRIM_SPACE = TRUE
- D
Set ESCAPE_UNENCLOSED_FIELD = '\'
Show answer and explanation
Correct answer: A
Explanation
For CSV loading in Snowflake, FIELD_OPTIONALLY_ENCLOSED_BY is the primary file format option used when fields may be wrapped in quotes. This is a common real-world requirement when source files include commas, record delimiters, or spaces as part of the actual field value. When configured correctly, Snowflake recognizes the enclosure character and parses embedded delimiters and line breaks inside quoted fields as data rather than structure. In contrast, SKIP_HEADER only ignores header rows, TRIM_SPACE can unintentionally remove meaningful spaces, and ESCAPE_UNENCLOSED_FIELD applies to a different parsing scenario. This aligns with Snowflake documentation and best practices for defining CSV file formats for staged data loads using COPY INTO.
- A. Correct.
Correct. FIELD_OPTIONALLY_ENCLOSED_BY tells Snowflake that fields may be enclosed in a specific quote character, such as double quotes. For CSV files, this allows commas and line breaks inside quoted fields to be treated as part of the field value instead of as delimiters or record terminators. It also preserves spaces inside the quoted field content rather than treating them as extraneous separators. This is the key file format option for CSV files that contain embedded delimiters or newlines within quoted values.
- B. Incorrect.
Incorrect. SKIP_HEADER controls how many initial lines Snowflake ignores before loading data. It is useful when CSV files contain a header row, but it does not address commas or line breaks inside quoted fields, nor does it preserve spaces within values.
- C. Incorrect.
Incorrect. TRIM_SPACE = TRUE removes leading and trailing spaces from fields. In this scenario, the requirement is to preserve spaces inside quoted values, so enabling TRIM_SPACE would work against that goal. It also does not solve the core parsing issue caused by commas and line breaks within quoted fields.
- D. Incorrect.
Incorrect. ESCAPE_UNENCLOSED_FIELD defines an escape character for fields that are not enclosed. This can help interpret special characters in unquoted data, but it does not tell Snowflake to treat double-quoted fields as enclosed values. Therefore, it does not reliably fix parsing when commas or newlines appear inside quoted text.