DEA-C02 · Question #37
A Data Engineer is trying to load the following rows from a CSV file into a table in Snowflake with the following structure: The engineer is using the following COPY INTO statement: However, the…
The correct answer is D. FIELD_OPTIONALLY_ENCLOSED_BY = '"'. Option D is correct because the root cause of the mismatch isn't a structural problem with the table - it's that the CSV contains fields enclosed in double quotes that themselves contain commas (e.g., "123 Main St, Apt 4"). Without FIELD_OPTIONALLY_ENCLOSED_BY = '"', Snowflake…
Question
A Data Engineer is trying to load the following rows from a CSV file into a table in Snowflake with the following structure:
The engineer is using the following COPY INTO statement:
However, the following error is received:
Number of columns in file (6) does not match that of the corresponding table (3), use file format option error_on_column_count_mismatch=false to ignore this error File 'address.csv.gz', line 3, character 1 Row 1 starts at line 2, column "STGCUSTOMER"[6] If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. Which file format option should be used to resolve the error and successfully load all the data into the table?
Exhibit
Options
- AESCAPE_UNENCLOSED FIELD = '\'
- BERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
- CFIELD_DELIMITER = ','
- DFIELD_OPTIONALLY_ENCLOSED_BY = '"'
How the community answered
(39 responses)- A8% (3)
- B13% (5)
- C3% (1)
- D77% (30)
Explanation
Option D is correct because the root cause of the mismatch isn't a structural problem with the table - it's that the CSV contains fields enclosed in double quotes that themselves contain commas (e.g., "123 Main St, Apt 4"). Without FIELD_OPTIONALLY_ENCLOSED_BY = '"', Snowflake treats every comma as a delimiter, splitting one quoted field into multiple columns and inflating the count from 3 to 6. Setting this option tells Snowflake to treat quoted strings as single fields, fixing the parse entirely.
Option B (ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE) is a trap - the error message itself suggests it, but it only suppresses the error without fixing the underlying misparse, meaning data would load into the wrong columns. Option C (FIELD_DELIMITER = ',') is already the default for CSV and changes nothing. Option A (ESCAPE_UNENCLOSED_FIELD = '\\') handles escape characters for fields that aren't enclosed in quotes - the opposite of this scenario.
Memory tip: When a CSV has fewer logical columns than Snowflake counts, suspect unrecognized quotes hiding internal commas - the fix is to teach Snowflake about the quotes (FIELD_OPTIONALLY_ENCLOSED_BY), not to ignore the mismatch.
Topics
Community Discussion
No community discussion yet for this question.