Hey @Ming 

That’s actually a very good question and lot of customers actually have the same doubts. What we are talking about is comparing two different approaches of solving the same problem:
1.) Replicate Data from Snowflake to SQL Server (or ETL)
2.) Linked Server Approach (or Data Virtualization)

In the first approach, you are actually lifting and shifting the data, however in the second approach, you are just making the data available in SQL Server without having to copy it.

There are definitely tradeoffs in both the approaches and there is no right answer to which approach is better. However, once you understand the tradeoffs, it is easier for you to make a decision depending on your environment.

CData actually has a great whitepaper that covers this in-depth: https://www.cdata.com/resources/data-integration-vs-data-virtualization/.

Why don’t you take a look and see if it helps?

@Ankit Singh 

Thanks for sharing the whitepaper. However, I still have question about CData product for connecting on-prem SQL server to a Snowflake database.

1. Is CData Connect Cloud the only choice for this?
   Although this might be a good solution, this would require me to go through internal InfoSec review and network suuport.
2. Is there another way?
   Does CData have a driver that works with SQL Server Linked Server?

Thanks,

Ming

Hi @Ming 

You can use CData ODBC Drivers for Snowflake to create a Linked Server on you on-prem SQL Server: https://www.cdata.com/kb/tech/snowflake-odbc-linked-server.rst.

The concept still remains the same. You are just making the Snowflake data available via an ODBC connection as a service to SSMS.

If this answers your question, let us know! :)

Best,

Ankit Singh

I was reading that post earlier and will work with our DBA team to do a trial.

However, I wonder if there are any insights, past experiences, or known limitations that you could share?

Thanks,

Ming

Hi @Ming 

How about we setup a meeting to discuss this further? I will have someone from our team reach out to you to setup a meeting and we can answer any questions you might have.

Best,

Ankit Singh