ADF Snowflake Connector Creating Excessive Temporary Folders in Blob Storage

Question

In an Azure Data Factory (ADF) pipeline, the Snowflake connector in the sink of the Copy activity is being used to insert rows into a Snowflake table. This connector requires a staging area in Azure Blob Storage.

Currently, executing the pipeline results in the creation of numerous temporary folders in Blob Storage, leading to over 8000 folders accumulated over time.

What is the cleanest way to prevent the creation of these temporary folders? Is there a method for automatic deletion of these folders after the sink has inserted rows into the Snowflake table?

Answer 1

You can use a Delete activity to your pipeline.

• Set the Dataset to point to the Blob Storage where the temporary folders are created.
• Use a wildcard or specific path to target the temporary folders for deletion.

https://learn.microsoft.com/en-us/azure/data-factory/delete-activity

Or instead of using Azure Blob Storage as the staging area, you can use Snowflake internal stage.

https://docs.snowflake.com/en/user-guide/data-load-local-file-system-create-stage.html

Or simply, Azure Blob Storage offers lifecycle management policies that can automatically delete blobs or folders after a specified period.

https://learn.microsoft.com/en-us/azure/storage/blobs/lifecycle-management-overview