We have used Snowpipe for ~10 months now and we recently ran into a case where part of the files in a stage got uploaded to the corresponding snowflake table and any future files were not detected. Verified that the underlying stage and pipe were in valid states.
Let's assume that the staging location is s3://<some_bucket>/some/path and there are 5 files file1.csv, file2.csv, file3.csv, file4.csv, file5.csv
select metadata$filename, count(*) from @<DB_NAME>.<SCHEMA>.<STAGE_NAME> group by metadata$filename;
The output indicates that all 5 files were detected and the counts align with what's expected. But file4.csv and file5.csv never got ingested.
select * from table(information_schema.copy_history(table_name=>'<TABLE_NAME>', start_time=> dateadd(hours, -1000, current_timestamp())));
does not show the copy history which makes us suspect if the table/pipe is in non-deterministic state and if there's a way out of this.
Note: the copy_history command works for other tables in the database.
