Data Pipeline between SQL Server and SnowFlake

KK 191 Reputation points
2022-11-09T02:42:53.993+00:00

Hi Experts,

I would like to do an Data integration between Sql Server and Snowflake data warehouse. But I am not sure what is the best practice.

What I need is data ETL between the DB and Data warehouse by schedules and sliding window automatically.

My question is if SQL Server and Snowflake are both sitting on-prem. My thought was to use Snowflake data piepline but not sure if that supports ETL on both directions (from SQL DB to Snowflkae, and from Snowflake to SQL Server)? Did someone have better approach for this scenario?

And if use Azure Sql Server, then maybe ADF is the best way to do the bidirectional data ETL?

Any advice and suggestions would be high appreciated!

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,663 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,247 questions
Accepted answer
  1. ZoeHui-MSFT 33,301 Reputation points
    2022-11-10T07:27:31.307+00:00

    Hi @KK ,

    You may use ODBC connector to connect to SnowFlake as said here.

    Except ADF, you may also use OLEDB Connection to connect to Azure Sql Server.

    You may choose the different ways as you need.

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Pratik Somaiya 4,201 Reputation points
    2022-11-09T07:53:22.967+00:00

    Hello @KK

    I would suggest to check Azure SQL DB as source and sink for Snowflake

    https://learn.microsoft.com/en-us/azure/data-factory/connector-snowflake?tabs=data-factory

    ADF supports Snowflake connector very well, if you plan to use both source and sink as On-Prem and then ADF then there might be multiple hops as ADF sits on cloud, your source and sink on OnPrem

    You can also use Azure Synapse Analytics as Snowflake connector works there as well

    1 person found this answer helpful.
    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more