DAA-C01 Question 7
Single answerStructured (CSV)A retail analytics team receives daily CSV files from a third-party logistics provider in an internal stage. The files contain a header row, fields may be enclosed in double quotes, embedded commas can appear inside quoted text, and some rows end with an extra trailing comma because the provider sometimes exports an empty final column. The team wants to load the files into a Snowflake table while minimizing load failures and ensuring the header is not ingested as data. Which file format configuration is the best choice for this scenario?
- A
Create a CSV file format with FIELD_OPTIONALLY_ENCLOSED_BY='"', SKIP_HEADER=1, and ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE
- B
Create a CSV file format with FIELD_DELIMITER='|', SKIP_HEADER=1, and ERROR_ON_COLUMN_COUNT_MISMATCH=TRUE
- C
Create a CSV file format with FIELD_ENCLOSED_BY='"', PARSE_HEADER=TRUE, and SKIP_BLANK_LINES=TRUE
- D
Create a CSV file format with ESCAPE_UNENCLOSED_FIELD='\', SKIP_HEADER=0, and TRIM_SPACE=TRUE
Show answer and explanation
Correct answer: A
Explanation
For CSV files in Snowflake, the most important settings must match the actual characteristics of the source data. When fields may be quoted and those quoted fields can contain commas, FIELD_OPTIONALLY_ENCLOSED_BY='"' is the standard choice because it allows Snowflake to treat commas inside quoted strings as part of the field value rather than as delimiters. To avoid ingesting the header row, SKIP_HEADER=1 is the correct file format property for COPY-based loads. In scenarios where the source occasionally emits irregular rows, such as an extra trailing comma that creates a column-count mismatch, setting ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE can make ingestion more resilient. This aligns with Snowflake best practices for structured file loading: define an explicit file format, validate against real source characteristics, and use tolerant settings only when they are justified by known source-system behavior. Relevant Snowflake documentation includes the CREATE FILE FORMAT reference for CSV options and COPY INTO
guidance for structured data loading.- A. Correct.
Correct. FIELD_OPTIONALLY_ENCLOSED_BY='"' is appropriate when CSV fields may or may not be wrapped in double quotes, including cases where embedded commas appear inside quoted values. SKIP_HEADER=1 prevents the first header row from loading into the target table. ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE is useful here because some rows may include an extra trailing delimiter representing an empty final field; this setting makes loading more tolerant instead of failing on occasional column-count inconsistencies. This is the most practical configuration for the stated file characteristics.
- B. Incorrect.
Incorrect. Using FIELD_DELIMITER='|' would misparse the files because the scenario explicitly describes CSV input, which is comma-delimited. Even though SKIP_HEADER=1 is appropriate, ERROR_ON_COLUMN_COUNT_MISMATCH=TRUE would increase load failures when rows contain the occasional extra trailing comma. This option reflects a common mistake of changing delimiters without matching the actual source format.
- C. Incorrect.
Incorrect. Snowflake supports FIELD_OPTIONALLY_ENCLOSED_BY for standard CSV scenarios where only some fields are quoted; FIELD_ENCLOSED_BY is not the correct file format option for staged CSV loading. In addition, PARSE_HEADER is not the right choice for simply preventing the header row from loading into a table in a COPY workflow; SKIP_HEADER is the relevant setting. SKIP_BLANK_LINES may be useful in some situations, but it does not address the main parsing requirements in this scenario.
- D. Incorrect.
Incorrect. ESCAPE_UNENCLOSED_FIELD can help with special characters in unquoted fields, but it does not solve the core issue of quoted fields containing commas. SKIP_HEADER=0 would load the header row as data, which the team explicitly wants to avoid. TRIM_SPACE can be useful for cleaning whitespace, but it is not sufficient to correctly handle embedded commas within quoted CSV fields.