Partial Loading Due to " in data - Snowflake Issue

Question

I haven't been able to find anything that describes this issue I am having, although, I am sure many have had this problem. It may be as simple as forcing pre-processing in Python before loading the data in.

I am trying to load data from S3 into Snowflake tables. I am seeing errors such as:

• Numeric value '' is not recognized
• Timestamp '' is not recognized

In the table definitions, these columns are set to DEFAULT NULL, so if there are NULL values here it should be able to handle them. I opened the files in Python to check on these columns and sure enough some of the rows (the exact number throwing an error in Snowflake) are NaN's.

Is there a way to correct for this in Snowflake?

Answer 1

Good chance you need to add something to your COPY INTO statement to get this to execute correctly. Try this parameter in your format options:

NULL_IF = ('NaN')

If you have more than just NaN values (like actual strings of 'NULL'), then you can add those to the list in the () above.

Answer 2

If you are having issues loading data into tables (from any source) and are experiencing a similar issue to the one described above, such that the error is telling you *datatype* '' is not recognized then you will need to follow these instructions:

• Go into the FILE_FORMAT you are using through the DATABASES tab
• Select the FILE_FORMAT and click EDIT in the tool bar
• Click on Show SQL on the bottom left window that appears, copy the statement
• Paste the statement into a worksheet and alter the NULL_IF statement as follows
• NULL_IF = ('\\N','');

Snowflake doesn't seem to recognize a completely empty value by default, so you need to add it as an option!