Step 4. Resolve Data Load Errors Related to Data Issues¶
Use the VALIDATE function to validate the data files you loaded and return all errors encountered during the load.
Validate Errors¶
The following process returns errors by query ID and saves the results to a table for future reference.
You can view the query ID for the COPY job on the History 
page of the web interface:
Log into the Snowflake web interface.
Change to the role you have been using to run the tutorial SQL statements.
Click History
.Click the Query ID column link for the COPY INTO command. The Details panel opens.
In the command line interface (e.g., SnowSQL), execute the following command. Replace
<query_id>with the Query ID value.CREATE OR REPLACE TABLE save_copy_errors AS SELECT * FROM TABLE(VALIDATE(mycsvtable, JOB_ID=>'<query_id>'));
Query the results table:
SELECT * FROM SAVE_COPY_ERRORS;
Snowflake returns the following results:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | ERROR | FILE | LINE | CHARACTER | BYTE_OFFSET | CATEGORY | CODE | SQL_STATE | COLUMN_NAME | ROW_NUMBER | ROW_START_LINE | REJECTED_RECORD | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------| | Number of columns in file (11) does not match that of the corresponding table (10), use file format option error_on_column_count_mismatch=false to ignore this error | tutorials/dataloading/contacts3.csv | 3 | 1 | 234 | parsing | 100080 | 22000 | "MYCSVTABLE"[11] | 1 | 2 | 11|Ishmael|Burnett|Dolor Elit Pellentesque Ltd|vitae.erat@necmollisvitae.ca|1-872|600-7301|1-513-592-6779|P.O. Box 975, 553 Odio, Road|Hulste|63345 | | Field delimiter '|' found while expecting record delimiter '\n' | tutorials/dataloading/contacts3.csv | 5 | 125 | 625 | parsing | 100016 | 22000 | "MYCSVTABLE"["POSTALCODE":10] | 4 | 5 | 14|Sophia|Christian|Turpis Ltd|lectus.pede@non.ca|1-962-503-3253|1-157-|850-3602|P.O. Box 824, 7971 Sagittis Rd.|Chattanooga|56188 | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
Snowflake encountered two data errors in tutorials/dataloading/contacts3.csv:
Number of columns in file (11) does not match that of the corresponding table (10)In Row 1, a hyphen was mistakenly replaced with the pipe (
|) character, the data file delimiter, effectively creating an additional column in the record.
Field delimiter '|' found while expecting record delimiter '\n'In Row 5, an additional pipe (
|) character was introduced after a hyphen, breaking the record.
Fix Errors and Load Again¶
In regular use, you would fix the problematic records manually and write them to a new data file. Alternatively, you could regenerate a new data file from the data source containing only the records that did not load.
You would then stage the fixed data files to the S3 bucket and attempt to reload the data from the files.