Data Factory to pull information from Snowflake using external stage to Blob Storage

Question

We are creating an ADF pipeline to pull information from Snowflake to Blob Storage. Reading the help article https://learn.microsoft.com/en-us/azure/data-factory/connector-snowflake?tabs=data-factory I am getting some pre-requisites to use this process is to have both Snowflake account properties REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_CREATION and REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_OPERATION as false. However, these properties as false create a real risk for data exfiltration, and Snowflake is recommending them to be true in customer environments (https://www.snowflake.com/blog/how-to-configure-a-snowflake-account-to-prevent-data-exfiltration/), we also have as PREVENT_UNLOAD_TO_INLINE_URL=true.
I am looking for alternatives to setup the data extraction process to use a set Snowflake external stage instead of the direct location.

For reference, the error we are experiencing when these properties are set to true is:

SQL compilation error: Cannot unload to an inlined external location. Please create a stage first and unload to the stage instead.

Answer 1

Hi @Enrique
Thank you for your question and using the Microsoft Q&A platform.
To address the issue while adhering to Snowflake's security recommendations, you can use a Snowflake external stage to manage the data extraction process. Here are the steps to set up the data extraction process using an external stage:

1. Create an External Stage in Snowflake:

• You can create an external stage in Snowflake that points to your Azure Blob Storage. This stage will act as an intermediary storage location for your data.

https://www.youtube.com/watch?v=83qAIrEz3KE
https://docs.snowflake.com/en/user-guide/data-load-azure-create-stage

2. Modify Your ADF Pipeline:

• In your Azure Data Factory (ADF) pipeline, modify the Copy activity to use the external stage you created in Snowflake.
• Instead of directly copying data to Blob Storage, copy the data to the external stage first, and then from the stage to Blob Storage.
  https://docs.snowflake.com/en/user-guide/data-load-azure

By following these steps, you can maintain the security of your data while still achieving the desired data extraction process. This approach allows for secure and efficient data extraction from Snowflake to Azure Blob Storage using Azure Data Factory.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.