DAA-C01 Question 8
Single answerStructured (CSV)A retail analytics team receives a daily CSV extract from an ERP system in an external stage. The files use a pipe (|) delimiter, include a header row, and sometimes contain embedded line breaks inside quoted product descriptions. Occasionally, the ERP exports malformed rows with an extra trailing field. The team wants to load the data into a Snowflake table while preserving valid rows, skipping the header, correctly handling multiline quoted fields, and avoiding load failures caused by the malformed rows. Which approach best meets these requirements?
- A
Create a CSV file format with FIELD_DELIMITER='|', SKIP_HEADER=1, FIELD_OPTIONALLY_ENCLOSED_BY='"', MULTI_LINE=TRUE, and ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE; then use COPY INTO the target table.
- B
Create a CSV file format with FIELD_DELIMITER='|', PARSE_HEADER=TRUE, MULTI_LINE=FALSE, and ERROR_ON_COLUMN_COUNT_MISMATCH=TRUE; then use COPY INTO the target table.
- C
Use COPY INTO with the default CSV file format and set ON_ERROR='CONTINUE' so malformed rows and multiline records are handled automatically.
- D
Load the files into a VARIANT column first because Snowflake can only handle embedded line breaks reliably when CSV data is staged as semi-structured data.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to define a CSV file format that matches the source file characteristics and then use COPY INTO with that file format. For structured CSV ingestion in Snowflake, accurate file format settings are critical. FIELD_DELIMITER must match the actual separator, SKIP_HEADER is commonly used to ignore column-name rows, and FIELD_OPTIONALLY_ENCLOSED_BY enables proper handling of quoted text. When quoted fields can contain embedded newline characters, MULTI_LINE=TRUE is necessary so Snowflake treats the quoted content as part of a single logical record rather than a row break. To prevent malformed records with extra columns from failing the load, ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE is the appropriate setting. This aligns with Snowflake best practices for CSV file formats and COPY INTO behavior: use file format options to describe the source correctly, and use error-handling settings judiciously rather than expecting COPY to infer structure automatically.
- A. Correct.
Correct. This configuration addresses each requirement directly for structured CSV loading. FIELD_DELIMITER='|' matches the source format. SKIP_HEADER=1 skips the single header row. FIELD_OPTIONALLY_ENCLOSED_BY='"' allows quoted fields, including those that contain delimiters or embedded newlines. MULTI_LINE=TRUE is required so quoted fields can span multiple lines. ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE prevents the load from failing when a row has an unexpected extra trailing column; Snowflake can ignore the mismatch instead of aborting the load. This is the most appropriate file format configuration for loading valid rows from imperfect CSV files into a relational table.
- B. Incorrect.
Incorrect. PARSE_HEADER is not the right choice for this loading pattern into a target table via standard COPY INTO, and MULTI_LINE=FALSE conflicts with the requirement to correctly load embedded line breaks within quoted descriptions. In addition, ERROR_ON_COLUMN_COUNT_MISMATCH=TRUE would cause the load to fail on malformed rows with extra fields, which is the opposite of the stated requirement.
- C. Incorrect.
Incorrect. ON_ERROR='CONTINUE' can skip rows that cause parsing or conversion errors, but it does not automatically infer the correct delimiter, handle headers, or properly parse multiline quoted fields without an appropriate file format. The default CSV file format uses commas, not pipes, and would not reliably parse these files. Relying only on ON_ERROR is a common misconception when the actual issue is incorrect file format definition.
- D. Incorrect.
Incorrect. Snowflake can load structured CSV data directly into standard relational columns, including files with embedded newlines in quoted fields, when the CSV file format is configured correctly. Loading into VARIANT is unnecessary for this scenario and does not solve the underlying CSV parsing requirements better than a proper structured load.