Moving data from Snowflake to Azure SQL DB using Copy Activity

Hi @PK

Welcome to the Microsoft Q&A forum.

I understand your concern about avoiding the intermediate staging step when moving data from Snowflake to Azure SQL DB using the COPY activity in Azure Data Factory (ADF).

Unfortunately, this is a current limitation of the COPY activity, and it's not possible to directly copy data from Snowflake to Azure SQL DB without staging the data in Azure Data Lake Storage (ADLS) or Azure Blob Storage.

Regarding the issue you're facing with the "Sink" step, I understand that when there's no data in Snowflake to export, no files are created in the intermediate staging storage account, and therefore, the Auto Create table option in the Sink step doesn't work as expected.

To handle this situation, here are a few possible workarounds:

• To use PolyBase in Azure SQL DB, you need to create an external data source that points to the Snowflake database and then create an external table that maps to the Snowflake table. You can then query the external table in Azure SQL DB as if it were a regular table.
• If you still prefer to use the COPY activity in ADF, you can try the following steps to handle the situation when there is no data in Snowflake to export and move to Azure SQL DB:
  1. Use a Lookup activity to check if there is any data in Snowflake to export. If there is no data, set a variable to indicate that there is no data.
  2. Use a Conditional activity to check the value of the variable. If there is no data, skip the Sink step and proceed to the next step in the pipeline.
  3. If there is data, proceed with the Sink step and auto-create the table in Azure SQL DB.

By using a Lookup activity and a Conditional activity, you can handle the situation when there is no data in Snowflake to export and move to Azure SQL DB without causing a failure in the ADF pipeline.

I hope this helps you resolve the issue. Let me know if you have any further questions.

@PK We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Regarding the staging of the data in the intermediate step, the only option available in this case ( Snowflake to SQL Server) is Azure Blob Storage. It automatically creates sub folders under the mentioned container path and the subfolders keep on piling up with every run.

@PK My apologies for the confusion in my previous response. You are correct that the only option available for staging data when moving from Snowflake to Azure SQL DB is Azure Blob Storage. As you mentioned, this can cause subfolders to pile up with every run, which can be an issue.

To avoid this issue, you can use the "Delete from sink" option in the Copy activity to delete the data from the sink after it has been copied. This will prevent the subfolders from piling up and taking up unnecessary space.

To use this option, you need to set the "deleteBehavior" property to "deleteFromSink" in the Copy activity. This will delete the data from the sink after it has been copied.

I hope this helps. Let me know if you have any further questions.

Sorry..I am unable to find this option anywhere. To tackle this situation, we have an explicit Delete Activity before Copy which tales care of deleting the subfolders before the next iteration of COPY activity runs.