snowflake copy into statement not purging from blob store

Question

Im running the below copy statement into snowflake destination table with a precreated external stage for the existing blob store account. The copy works correctly and rows are then visible in destination snowflake table. The purge of the corresponding folder 'D_CALENDER' in azure blob store doesn't process though. No error, just doesnt process. Any ideas perhaps?

COPY INTO "STAGING"."D_CALENDAR"
FROM @STAGE/D_CALENDAR/
FILE_FORMAT = CSV_IMPORT ON_ERROR = CONTINUE PURGE = TRUE;

Regards Posty

Does the token you are using to Azure Blob for your external stage definition have the correct privileges to delete an object? That's the first place that I would check. — Mike Walton
Hi Mike,yes thanks found the issue. Basically as you referring to as well. The shared access signature for the blob container in question only had read permissions ticked when it was set up a while back. The external stage was then set up with that access token which is why selecting into snowflake destination worked fine but the purge didnt. Created a new shared access sig with read write and delete, recreated stage using this token ran the copy/purge and voila. Thanks for the prompt feedback ;) — Posty

Posty Posty · Accepted Answer · 2019-11-07T06:33:15

Found the issue. The shared access signature for the blob container in question only had read permissions ticked when it was set up a while back. The external stage was then set up with that access token which is why selecting into snowflake destination worked fine but the purge didnt. Created a new shared access sig with read write and delete, recreated stage using this token ran the copy/purge and voila.

snowflake copy into statement not purging from blob store

1 Answers