By: Ian Fogelman | Comments (4) | Related: > Linked Servers
Problem
You have your data warehouse for your OLAP system in Snowflake. You would like to take advantage of some of the key selling features in Snowflake such as time travel to simplify some reporting efforts. Your company is largely SQL Server savvy and does not have the willpower to learn a new RDMS. How can you expose some of the awesome features in Snowflake to your largely SQL Server employee base?
Solution
The answer is through a SQL Server Linked Server. Snowflake offers an ODBC driver that allows for client connectivity. In this tip, we review how to configure a SQL Server linked server into Snowflake and a few use cases of how this solution would be beneficial in a production environment.
My article is based largely off the community post. I want to provide more in-depth setup instructions as well as additional performance and use case scenarios.
Download and Install the ODBC driver
In your Snowflake environment download the ODBC driver. You can do this by going to the Help Icon in your Snowflake account and selecting Download.
After selecting the ODBC Driver option, follow the link for the latest ODBC driver.
Run the executable to install the Snowflake ODBC driver to your system.
Add A System DNS Data source
Open ODBC Data sources.
Click System DSN > Add.
Select SnowflakeDSIIDriver and click Finish.
Next, configure the specifics to your Snowflake account. You may want a more specific data source name. Whatever name you give the Data Source, make a note as it will be used in a future step.
Configure SQL Server Linked Server Provider
Inside of your SQL Server instance, navigate to Linked Servers and configure MSDASQL to include the following check boxes:
Add SQL Server Linked Server
In SQL Server, navigate to Linked Servers, New Linked Server:
Provide the exact name you previously provided in the ODBC data source in the Data Source field.
Next, configure the security. This is the step that was different for me than the original post. I had to use a Remote Login context to get the linked server to work.
Click OK.
SQL Server Linked Server Validation
You will know you have connected to the Snowflake instance correctly if you see databases populate under the linked server.
SQL Server Linked Server to Snowflake Use Cases and Performance
Now that we have a connection to your Snowflake instance through SQL server, you can run Snowflake queries. You can use open query to reach Snowflake database and schemas to which the role you configured has access to.
Performance
As you might expect, the performance when forcing queries through linked servers is extremely degregated.
For my example query, it was a difference of about 40x query time on the initial test.
| Environment | Virtual Warehouse Size | Seconds | Rows | Columns |
|---|---|---|---|---|
| Native Snowflake | Small | 3.66 | 1500000 | 8 |
| SQL Server ODBC Linked Server | Small | 149 | 1500000 | 8 |
Interestingly, the Snowflake result cache was reused when running the linked server and checking the query time. In Snowflake, the remote query took only a few seconds to execute on the Snowflake side. But the results took over 2 minutes to load into the results window in SQL Server.
Use Cases
Reporting efforts in Snowflake are easier to manage in Snowflake due to time travel. Time travel is a feature that when enabled allows you to go back in time to any table state. All updates and changes to tables are tracked within the 90-day time frame. You can pass a raw timestamp to your Snowflake dimension table and look back in time without having to capture slowly changing dimensions. This feature is available in SQL Server as a temporal table. But, if you are going to be warehousing your data into Snowflake anyways, it is definitely is a bonus.
A few example queries and how you would use them in SQL server to capture time travel values.
SELECT * FROM OPENQUERY([SF],'SELECT * FROM MY_DIMENSTIONTABLE AT(OFFSET => -60*60);'); SELECT * FROM OPENQUERY([SF],'SELECT * FROM MY_DIMENSTIONTABLE AT(TIMESTAMP => ''Mon, 01 April 2019 16:20:00 -0700''::timestamp_tz;');
Perhaps you have a vendor who you supply data to and they are a Microsoft shop. You could provide instructions for setting up this linked server environment in lieu of any SFTP file transfers that would normally be required. Roles and permissions can be tuned even though Snowflake already provides a mechanism for sharing data. This solution would keep the vendor comfortable with current technology.
We covered setting up a linked server into your Snowflake instance from SQL Server. We downloaded and configured the ODBC Snowflake driver as well as tested the results of querying from our SQL Server instance. We also reviewed a few use cases for which explain why building a linked server would be viable.
Next Steps
- Learn how to setup a Snowflake account
- Understand Snowflake pricing model
- Check out the documentation on Snowflake time travel
About the author
Ian Fogelman is professional DBA with a hunger and interest for data science and application development. Ian enjoys leveraging cloud-based technologies and data to solve and automate tasks. Ian holds a MS in computer science with a concentration in software development. Ian is also a member of his local SQL Server user group and a member of his local data science user group in Columbus Georgia.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
