Snowflake COPY INTO fails on double quote value in a data value

Question

COPY INTO is failing due to a double quote being found within the data value.
The two double quotes around C causes the failure -> NE 845 "C" Street.

The exact error message is:

Found character 'H' instead of field delimiter '|~' File '@~/FolderX/datafile.dat.gz', line 1, character 107 Row 274598, column "MY_TABLE"["BADCOLUMN":20] 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. For more information on loading options, please run 'info loading_data' in a SQL client.

The Format File is defined as:

CREATE FILE FORMAT "DW"."STG".FMT_FILE 
COMPRESSION = 'GZIP' 
FIELD_DELIMITER = '|~' 
RECORD_DELIMITER = '\n' 
SKIP_HEADER = 0 
FIELD_OPTIONALLY_ENCLOSED_BY = '\042' 
TRIM_SPACE = TRUE 
ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE 
ESCAPE = '#' 
ESCAPE_UNENCLOSED_FIELD = 'NONE' 
DATE_FORMAT = 'AUTO' 
TIMESTAMP_FORMAT = 'AUTO' 
ENCODING = 'WINDOWS1253'
NULL_IF = ('\\N');

Why does it care if double quote exists within any value when the data is:

123|~NE 845 "C" Street|~PULLMAN

There should be no need, given the definition of the format file, add an escape within any string. This failure seems like a bug.

Answer 1

It's not a bug. You've explicitly asked Snowflake to recognize a double-quote as a way of enclosing values within your file when you used this statement FIELD_OPTIONALLY_ENCLOSED_BY = '\042'. If you remove that (or change to NONE) from your file format, you shouldn't receive the error that you are receiving. Snowflake is behaving as expected, though.