Synapse Pipeline to snowflake hosted on AWS

Question

Synapse Pipeline to snowflake hosted on AWS

Vamsi Madhav 46

Hello there, i cant seem to find conclusive details to the usage of Snowflake hosted on AWS as a sink in an Copy & transform stage of our synapse pipeline. can someone kindly confirm if I am looking at old information or if this still holds true. I might have misread it but there isn't anywhere explicitly stating on microsoft that SF on AWS cannot be used as sink.

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2023-05-02T06:06:11.9633333+00:00

@Anonymous - Just checking in to see if the below answer helped. 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.
Vamsi Madhav 46 Reputation points

2023-05-02T07:32:51.0233333+00:00

@PRADEEPCHEEKATLA

Just want to point out for someone else that your statement below isnt accurate - "Azure Synapse Analytics supports Snowflake as a sink for Copy and Transform activities. You can use the Snowflake connector in Synapse to copy data from a source data store to a Snowflake table hosted on AWS."

in my case, it did NOT work. I still don't see evidence to suggest that it would work. the workaround is to configure a odbc connection via a self-hosted integration runtime.

but thanks for taking the time to respond and helping out.

Accepted answer

0 additional answers

Your answer

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2023-05-02T06:06:11.9633333+00:00

@Anonymous - Just checking in to see if the below answer helped. 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.
Vamsi Madhav 46 Reputation points

2023-05-02T07:32:51.0233333+00:00

@PRADEEPCHEEKATLA

Just want to point out for someone else that your statement below isnt accurate - "Azure Synapse Analytics supports Snowflake as a sink for Copy and Transform activities. You can use the Snowflake connector in Synapse to copy data from a source data store to a Snowflake table hosted on AWS."

in my case, it did NOT work. I still don't see evidence to suggest that it would work. the workaround is to configure a odbc connection via a self-hosted integration runtime.

but thanks for taking the time to respond and helping out.

Answer 1

PRADEEPCHEEKATLA 90,641 Moderator

Vamsi Madhav - Thanks for the question and using MS Q&A platform.

Azure Synapse Analytics supports Snowflake as a sink for Copy and Transform activities. You can use the Snowflake connector in Synapse to copy data from a source data store to a Snowflake table hosted on AWS.

To use Snowflake as a sink in Synapse, you need to create a linked service for Snowflake in Synapse. You can do this by following the steps in the Azure documentation for creating a linked service for Snowflake: Copy and transform data in Snowflake using Azure Data Factory or Azure Synapse Analytics.

After creating the linked service, you can use it in a Copy activity or Transform activity to copy data from a source data store to a Snowflake table hosted on AWS. You will need to specify the Snowflake table name, database name, schema name, and other connection details in the activity settings.

It is important to note that the Snowflake connector in Synapse is designed to work with Snowflake instances hosted on AWS, Azure, or Google Cloud Platform. However, there may be some differences in the configuration and performance of the connector depending on the cloud platform hosting the Snowflake instance.

I hope this helps you use Snowflake as a sink in your Synapse pipeline. If you have any further questions or concerns, please let me know.

Vamsi Madhav 46 Reputation points

2023-04-20T11:31:02.34+00:00

@PRADEEPCHEEKATLA thanks for your response.

after getting through various issues about enabling staging etc currently getting the job to write from a blob storage to a snowflake (hosted on AWS) as sink - i get this error which is where my question started from
ErrorCode=SnowflakeUnsupportedCouldPlatformForImport,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Snowflake only support the account hosted in azure platform while as sink connector, please make sure your account is hosted in azure, current region and the platform is 'AWS_AP_NORTHEAST_1',Source=Microsoft.DataTransfer.ClientLibrary

if you have confirmed that i can use synpase pipelines to write data to SNowflake as sink despite being on AWS, is there something i should do different ?
Vamsi Madhav 46 Reputation points

2023-04-21T12:24:49.57+00:00

Guys - I am still waiting for @PRADEEPCHEEKATLA to respond to my original issue.

But as a workaround - I managed to create an ODBC connection on a Windows machine, use a self-hosted integration runtime to connect via it and create a linked ODBC connection in azure to get data loaded direct into the Snowflake table. for the test I did - I don't have any performance issues or challenges yet - but I have a working solution for now. hope this helps someone.
PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2023-04-24T06:25:50.6866667+00:00
Vamsi Madhav - Glad to know that your issue has been resolved. And thanks for sharing the solution, which might be beneficial to other community members reading this thread.

The error message is self explanatory Snowflake only support the account hosted in azure platform while as sink connector, please make sure your account is hosted in azure, current region and the platform is AWS.

As a prerequisites:

If your data store is located inside an on-premises network, an Azure virtual network, or Amazon Virtual Private Cloud, you need to configure a self-hosted integration runtime to connect to it. Make sure to add the IP addresses that the self-hosted integration runtime uses to the allowed list.

If your data store is a managed cloud data service, you can use the Azure Integration Runtime. If the access is restricted to IPs that are approved in the firewall rules, you can add Azure Integration Runtime IPs to the allowed list.

Hope this helps. Do let us know if you any further queries.
Vamsi Madhav 46 Reputation points

2023-04-24T07:10:50.63+00:00

Thanks Pradeep - so to conclude - i have got this right that ADF cannot support SF on AWS as sink and the documentation doesn't mention that anywhere? is there a roadmap for when this would be available ?
PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2023-04-27T09:11:15.1133333+00:00

Vamsi Madhav - Appreciate if you could share the feedback on our feedback channel. Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.
Vamsi Madhav 46 Reputation points

2023-08-24T10:56:10.95+00:00

just concluding this link - AWS hosted snowflake cannot be used as a sink.

Share via

Synapse Pipeline to snowflake hosted on AWS

0 additional answers

Your answer