SnowPro Associate: Platform Question 287
Single answer○ File format optionsA data engineering team loads vendor-delivered CSV files from an internal stage into a Snowflake table each night. Recently, loads started failing because some text fields now contain embedded commas, are enclosed in double quotes, and sometimes span multiple lines within a single field. The files also include a header row that should not be loaded. The team wants to fix the issue by updating the file format used by COPY INTO while minimizing changes to the ingestion process. Which file format configuration is the best choice?
- A
Set FIELD_OPTIONALLY_ENCLOSED_BY='"', SKIP_HEADER=1, and MULTI_LINE=TRUE in the CSV file format.
- B
Set FIELD_DELIMITER='|', SKIP_HEADER=1, and COMPRESSION=AUTO in the CSV file format.
- C
Set ESCAPE='\', ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE, and PARSE_HEADER=TRUE in the CSV file format.
- D
Set RECORD_DELIMITER='\n', TRIM_SPACE=TRUE, and SKIP_BLANK_LINES=TRUE in the CSV file format.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to configure the CSV file format so Snowflake can correctly interpret standard CSV quoting rules. In Snowflake, FIELD_OPTIONALLY_ENCLOSED_BY identifies the quote character used around fields, which allows embedded delimiters such as commas to be treated as part of the field value instead of as separators. When quoted fields can contain embedded newline characters, MULTI_LINE=TRUE is needed so a logical record can span multiple physical lines in the file. Because the source files contain a header row, SKIP_HEADER=1 prevents that row from being loaded. This reflects common Snowflake file format best practices for CSV ingestion with COPY INTO. Relevant Snowflake documentation includes file format options for CSV such as FIELD_OPTIONALLY_ENCLOSED_BY, MULTI_LINE, and SKIP_HEADER.
- A. Correct.
Correct. For CSV data that contains embedded commas inside quoted text, Snowflake should be told that fields may be enclosed in double quotes by setting FIELD_OPTIONALLY_ENCLOSED_BY='"'. Because some values span multiple lines inside those quoted fields, MULTI_LINE=TRUE allows records to continue across line breaks within properly enclosed fields. SKIP_HEADER=1 correctly ignores the first header row. This combination directly addresses the scenario without requiring broader process changes.
- B. Incorrect.
Incorrect. Changing FIELD_DELIMITER to '|' would only be appropriate if the file were pipe-delimited, but the scenario states the files are CSV. COMPRESSION=AUTO can help detect compression type, but it does nothing to correctly parse embedded commas, quoted values, or multiline fields. SKIP_HEADER=1 is useful, but the rest of the configuration does not solve the core parsing problem.
- C. Incorrect.
Incorrect. ESCAPE can be relevant in some parsing scenarios, but it is not the primary fix for standard CSV files with quoted fields and embedded commas. ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE may suppress some load errors, but it risks loading malformed data rather than correctly parsing it. PARSE_HEADER is not the right setting for this use case in a CSV COPY load; the requirement is simply to skip the header row, which is done with SKIP_HEADER.
- D. Incorrect.
Incorrect. RECORD_DELIMITER='\n' is already the normal line delimiter for many text files and does not by itself enable proper handling of newline characters embedded within quoted fields. TRIM_SPACE and SKIP_BLANK_LINES may be useful cleanup settings in some cases, but they do not address the need to recognize quoted fields containing commas and line breaks.