COPY INTO: is there a way to show number of records skipped during loading data into Snowflake?

Question

I am using copy-into-table from an external location and there is an option to continue loading the data in case the row have corrupted data. Is there an option to show how many rows were skipped while loading, like there is an option in Teradata TPT.

Answer 1

Assuming that you are not doing transformations in your COPY INTO command, you can leverage the VALIDATE() function after the load and get the records skipped and the reason why they were not loaded:

https://docs.snowflake.com/en/sql-reference/functions/validate.html

Example where t1 is your table being loaded. You can also specify a specific query_id if you know it:

select * from table(validate(t1, job_id => '_last'));

Answer 2

The COPY INTO outputs the following columns:

ROWS_PARSED: Number of rows parsed from the source file
ROWS_LOADED: Number of rows loaded from the source file
ERROR_LIMIT: If the number of errors reaches this limit, then abort
ERRORS_SEEN: Number of error rows in the source file

The number of rows skipped can be calculated as ROWS_PARSED - ROWS_LOADED. I am using pyodbc the parsing of these columns might differ the way you are scripting.