Moving data from Snowflake to Azure SQL DB using Copy Activity

PK 20

Hi All,

I am using COPY activity to move data from Snowflake to Azure SQL DB. First of all, this data movement is not allowed without intermediate staging of the data in ADLS. I would love to avoid this intermediate step. If this is how it will work then it's fine but it is causing an issue with "Sink" step. I have an option of Auto creating table in Azure SQL in the Sink . The problem that I am facing is that when there is no data in Snowflake to export and move to Azure SQL DB , then there are no files created in intermediate Staging storage account and in that situation the next step of Auto Creating the table in Sink is not happening. This is leading to failure in ADF pipeline in next steps as there are steps which expects the table to be present but since the table is not getting created in cases when there is no data it is causing a problem. How can we handle this situation ? Please help.

Smaran Thoomu 17,120 Reputation points Microsoft Vendor

2024-09-04T19:50:39+00:00
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:

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.

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.

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.
Smaran Thoomu 17,120 Reputation points Microsoft Vendor

2024-09-05T18:26:18.67+00:00

@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.
PK 20 Reputation points

2024-09-06T02:42:25.9666667+00:00

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.
Smaran Thoomu 17,120 Reputation points Microsoft Vendor

2024-09-06T19:08:04.24+00:00

@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.
PK 20 Reputation points

2024-09-09T15:39:35.2366667+00:00

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.
Smaran Thoomu 17,120 Reputation points Microsoft Vendor

2024-09-10T21:10:24.81+00:00

@PK Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer
PK 20 Reputation points

2024-11-07T18:05:48.26+00:00

I am reopening this thread again as I am seeng a behaviour which I am unable to decipher. As mentioned we need to stage the data in Azure Blob Storage before copying the files. We have created a separate container in the Storage account for that reason . After successful copying of the data we are using the Delete File activity to cleanup all the folders and it's contents but at the same time we are also observing that there are some files created in the storage account in the main root path (outside any containers) and it's contents looks like log data for the process. Can you please explain why this additional file is getting created and that too at the path which is not even mentioned, hence these files are piling up and not getting deleted in the process.

Accepted answer

Smaran Thoomu 17,120 Reputation points Microsoft Vendor

2024-09-10T21:10:03.3866667+00:00

Hi @PK

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Issue: I am using COPY activity to move data from Snowflake to Azure SQL DB. First of all, this data movement is not allowed without intermediate staging of the data in ADLS. I would love to avoid this intermediate step. If this is how it will work then it's fine but it is causing an issue with "Sink" step. I have an option of Auto creating table in Azure SQL in the Sink . The problem that I am facing is that when there is no data in Snowflake to export and move to Azure SQL DB , then there are no files created in intermediate Staging storage account and in that situation the next step of Auto Creating the table in Sink is not happening. This is leading to failure in ADF pipeline in next steps as there are steps which expects the table to be present but since the table is not getting created in cases when there is no data it is causing a problem. How can we handle this situation ? Please help.

Solution: 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.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Moving data from Snowflake to Azure SQL DB using Copy Activity

0 additional answers

Your answer