ARA-C01 Question 222
Single answerFile formatsA retail company is migrating a partner feed into Snowflake. The partner delivers large CSV files to an external stage every hour. The files contain fields enclosed in double quotes, embedded commas inside quoted text, occasional blank lines, and the literal string NULL to represent missing values. The architects want to minimize load errors while ensuring that missing values are stored as SQL NULLs and quoted text is parsed correctly. Which file format definition best meets these requirements for use with COPY INTO?
- A
TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_BLANK_LINES = TRUE NULL_IF = ('NULL')
- B
TYPE = CSV FIELD_DELIMITER = ',' ESCAPE_UNENCLOSED_FIELD = NONE REPLACE_INVALID_CHARACTERS = TRUE
- C
TYPE = CSV FIELD_ENCLOSED_BY = '"' EMPTY_FIELD_AS_NULL = FALSE ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
- D
TYPE = CSV PARSE_HEADER = TRUE STRIP_OUTER_ARRAY = TRUE NULL_IF = ('NULL')
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1 because it aligns with Snowflake CSV file format best practices for real-world ingestion. When CSV fields may be wrapped in quotes and contain delimiters inside the quoted text, FIELD_OPTIONALLY_ENCLOSED_BY is the key parameter that allows Snowflake to parse those values correctly. SKIP_BLANK_LINES helps avoid failures or unnecessary rejected rows when source systems insert empty lines. NULL_IF maps specified string literals, such as 'NULL', to SQL NULL values during loading.
This scenario reflects common architecture decisions around robust batch ingestion using COPY INTO with named or inline file formats. Snowflake documentation for CREATE FILE FORMAT and COPY INTO emphasizes using CSV enclosure settings for quoted fields, and NULL_IF for custom null-string handling. STRIP_OUTER_ARRAY is for JSON, not CSV, and parameters related to invalid character replacement or relaxed column mismatch handling do not solve the primary parsing requirement here.
- A. Correct.
Correct. For CSV data where some fields are quoted and may contain embedded commas, FIELD_OPTIONALLY_ENCLOSED_BY = '"' is the appropriate setting. SKIP_BLANK_LINES = TRUE helps avoid unnecessary load issues from occasional empty lines. NULL_IF = ('NULL') ensures the literal string NULL is interpreted as a SQL NULL during loading. This combination directly addresses all stated requirements.
- B. Incorrect.
Incorrect. FIELD_DELIMITER = ',' is reasonable for CSV, but this option does not handle quoted fields containing embedded commas because it omits FIELD_OPTIONALLY_ENCLOSED_BY or equivalent quote-handling. REPLACE_INVALID_CHARACTERS = TRUE addresses encoding issues, not CSV quoting or NULL handling. ESCAPE_UNENCLOSED_FIELD = NONE also does not solve the core parsing requirement.
- C. Incorrect.
Incorrect. Snowflake uses FIELD_OPTIONALLY_ENCLOSED_BY for common CSV quote handling; FIELD_ENCLOSED_BY is not the appropriate CSV file format parameter for this use case. Also, EMPTY_FIELD_AS_NULL = FALSE would prevent empty fields from being treated as NULLs, which is usually contrary to resilient ingestion patterns when dealing with missing values. ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE can suppress certain row-level issues but does not address the required quoted-comma parsing or literal NULL conversion.
- D. Incorrect.
Incorrect. PARSE_HEADER = TRUE can be useful when files include a header row for inferencing or specific operations, but it does not address embedded commas in quoted fields. STRIP_OUTER_ARRAY applies to semi-structured formats such as JSON, not CSV. Although NULL_IF = ('NULL') is relevant, the rest of the definition is not appropriate for this file type.