During the Previous post, we talked about VALIDATION_MODE options of COPY command. In other words, Validate mode does not load the data into Table. To check Snowpipe status or view the error encountered during the Snowpipe COPY command we can use VALIDATION_MODE set to RETURN_ALL_ERRORS.
Step 3: Validate the Data Files using VALIDATE_PIPE_LOAD.
One of the function to determine Snowpipe status, This table function can be used to validate data files processed by Snowpipe within a specified time range. The function returns details about any errors encountered during an attempted data load into Snowflake tables.
This function returns pipe activity within the last 14 days.
Note: During the discussion of these troubleshooting approaches we have seen only those instances where the load was successfully complet or there was no issue encounter during the Snowpipe data ingestion process, In addition to it, it would be good if we discuss some negative scenario where we see the load gets failure (File format issue or Data nullable issue) either of any reason.
To get the negative result:
Consider the scenario where a non null column is defined inside the table and you are getting the file where the column is nullable inside the File itself.
create or replace table demodata (
Firstname varchar(255) not null,
lastname varchar(255) null,
email varchar(255) null,
address varchar(255) null,
city varchar(255) null
Please note : Firstname is define as not null in the table.
Consider the case for Copy history,
If the incoming File attributes are differ or does not match that of the corresponding table and have not used error_on_column_count_mismatch=false at file format.
To get more details on Approach 1 and Approach 2, Please click here.