To capture all errors from a failed COPY statement:
1. Query the LOAD_HISTORY information schema view. Include the target table name and a time period:
SELECT * FROM information_schema.load_history WHERE schema_name=current_schema() AND table_name='MYTABLE' AND last_load_time > 'Wed, 21 Sep 2016 16:00:00 -0800' AND STATUS='LOAD_FAILED';
The output identifies the data files that did not load successfully.
2. Re-run the COPY statement, including the 'VALIDATION_MODE' option. Identify the data files that did not load successfully:
COPY INTO MYTABLE VALIDATION_MODE = 'RETURN_ERRORS' FILES=('bad.csv');
Fix any issues with the data files before attempting to load the data again.
Note that running COPY statements in VALIDATION_MODE executes in approximately the same amount of time as running the statement to actually load the data.
All Snowflake clients