How to delete a File from S3 after it is loaded

Question

I have a STAGE and PIPE setup to load data from a S3 folder to a SNOWFLAKE table. I have setup event notifications to load the file as and when it is uploaded to S3.

It is all working fine.

I want to delete the file in the S3 folder after it is loaded.

How do I Accomplish that.

I tried this statement and it does not seem to work

alter table nph_contact set stage_copy_options = (purge = true);

Any thoughts?

Answer 1

According to the recommendations in the documentation for the PURGE option:

We recommend that you list staged files periodically (using LIST) and manually remove successfully loaded files, if any exist.

A better way would be to do a process in which, after making sure the data is loaded correctly, you use the REMOVE function.

Example:

remove @mystage/path1/subpath2;