Snowpipe not working after upload same file twice

Question

Just playing around with Snowpipe. I had it working. I would drop a file onto S3 and Snowpipe loaded the data into a Snowflake table.

However when I copied the same file twice into the S3 bucket, Snowpipe didnt pick it up or any subsequent files where were not duplicate.

To illustrate:

    Uploaded file1.txt into the S3 bucket - success 
    Uploaded file2.txt into the S3 bucket - success 
    Uploaded file3.txt into the S3 bucket - success 
    Re-Uploaded file1.txt into the S3 bucket - no result - table was not updated 
    Uploaded file4.txt into the S3 bucket - no result - table was not updated

How do I go about troubleshooting this? or fixing this issue.

Thanks

I believe re-uploading a file is considered a new file to a snowpipe so it should have been picked up, as well as subsequent files. What troubleshooting steps have you tried? Recreating the pipe? Running the same copy statements with "Copy Into" manually? — David Garrison
I haven't tried anything. But looks like I need to make sure that file names are unique. support.snowflake.net/s/question/0D50Z00008FR1LmSAL/… — learningturtle
I haven't seen issues like that with filenames, but I use Azure, so that might be slightly different. Those troubleshooting steps of recreating your stages, pipes, AWS queue and updating your question with the results would at least help narrow down what's causing the problem. — David Garrison
"Re-Uploaded file1.txt into the S3 bucket - no result - table was not updated" is expected. But "Uploaded file4.txt into the S3 bucket - no result - table was not updated" should have result. You may want to take a look at the pipe_status? docs.snowflake.net/manuals/sql-reference/functions/… — Jiaxing Liang
I tried recreating the pipe but that is not working. SQS ARN remains the same. I also tried creating a new pipe - however interestingly the SQS ARN for the second pipe is also same as the first one. I dropped a couple more files into the S3 bucket but still no luck. Pipe status says it is running. I did monitor the pipe status and it is running. After dropping the file into the S3 bucket the pending file count goes up to 1 else it stays 0. So pipe does appear to be working. — learningturtle

Dinesh Dinesh · Accepted Answer · 2020-01-15T23:29:01

A few clarifications:

Yes, Snowpipe will not load a file again. If there is an error in the file and you need to modify it, you will need to rename it (e.g. file1v2.txt)
The behavior you noticed regarding the next file not being loaded is unexpected & requires troubleshooting. Is there any issue with the next file (since it is showing up as pending file count of 1)? Are you able to access it otherwise from outside Snowflake? Can you run COPY on it to load it to say another table?
Snowpipe behaves similarly on Azure & AWS except for queue ownership (Azure blob store will not deliver to a queue in another subscription).
Multiple pipes share the same queue on AWS and we use the bucket/prefix to demultiplex to different pipes.

Dinesh Kulkarni

(PM, Snowflake)

Snowpipe not working after upload same file twice

1 Answers