Snowflake How to get Records failed in Copy command

Question

is it possible to get records which failed during Copy command in Snowflake from internal stage to snowflake table?

I am trying to load error recrods in a error table during Copy command execution . Copy Command used:

Copy into table ( col1, col2,col3,col4) from ( select $1,$2,$3,56 from @%table) ON_ERROR=CONTINUE

score 1 · Answer 1 · answered Oct 23 '20 at 18:50

1

To get all the bad records, you can run the copy with VALIDATION_MODE = 'RETURN ERRORS'. Then use the RESULT_SCAN from the validation in an insert statement.

answered Oct 23 '20 at 18:50

Mike Gohl

627
4
7

I think this does not work if my Copy command has column names, I am using Copy into table ( col1, col2,col3,col4) from ( select $1,$2,$3,56 from @%table) ON_ERROR=CONTINUE. – PythonDeveloper Oct 23 '20 at 19:25

score 0 · Answer 2 · answered Oct 24 '20 at 11:22

0

If one of your columns is unique (i.e. col1), maybe you can compare rows in the table with the rows in the stage:

select $1 from @%table
MINUS 
select col1 from table;

answered Oct 24 '20 at 11:22

Gokhan Atil

9,278
2
11
24

select $1 from @%table does not work because my table has different number of columns then file which is in stage. it throws error number of columns in file does not match with the table. And i might not have unique values always so not planning to use this solution. – PythonDeveloper Nov 03 '20 at 16:03

PIG · Answer 3 · 2020-10-29T06:28:41.090

0

Please check below select statement after copy command

select rejected_record  from table(validate(test_copy , job_id => '_last')) ;

edited Oct 29 '20 at 06:28

answered Oct 29 '20 at 06:18

PIG

599
3
13

Snowflake How to get Records failed in Copy command

3 Answers3