← Back

How to Connect Snowflake to Excel

2024-01-17 // Nick End, Founder

The following post covers the benefits and considerations and provides step-by-step instructions for connecting a Snowflake cloud data warehouse to Microsoft Excel. The spreadsheet is often the preferred tool for building models and combining various data sets into one analysis. Connecting Snowflake to Microsoft Excel can make it easy for business teams to pull real-time data into Excel for analysis. An alternative to Excel is Row Zero, a spreadsheet designed for big data and more easily connects to Snowflake and other cloud data warehouses. Read on for more information or skip to specific sections using the table of contents below.

Contents

  1. Power Query (6 steps + ODBC driver installation)
  2. Power Pivot (8 steps + ODBC driver installation)
  3. Microsoft Query (5 steps + ODBC driver installation)
  4. Open CSV (8 steps)
  5. Row Zero - Bonus (3 steps)

What is Snowflake?

Snowflake Computing is a cloud data warehousing platform that simplifies data storage and analysis. Unlike traditional data warehousing solutions, Snowflake operates on a cloud-native architecture, enabling organizations to seamlessly store, manage, and analyze vast amounts of data across multiple cloud data sources without the constraints of hardware limitations. Snowflake utilizes a structured data storage model known as a "data lakehouse," which combines the benefits of data warehousing and data lakes. This architecture allows for separation of storage and compute, providing elasticity and scalability.

Benefits of connecting Snowflake to Microsoft Excel

  • Real-time Data - Connecting Microsoft Excel to Snowflake database enables financial and accounting models to be built on production or operational real-time data stored in Snowflake. Rather than static models or analysis that are stale as soon as they are built, connecting to a snowflake database ensures models are always up-to-date.
  • Refresh Live Data Models - Once you connect Snowflake to Excel, you have a live connection and it is easy to re-run or refresh the snowflake query to pull the latest dataset into the spreadsheet without having to recreate another static analysis. Live data models are time savers and keep your analyses fresh.
  • Dashboards - When you connect Snowflake to Excel, it is easy to turn a simple analysis into a dashboard or live model. Simply refresh the queried data from Snowflake and all graphs, pivot tables, and spreadsheet models will update.

Considerations when connecting Snowflake to Microsoft Excel

  • Data Set Size - While Microsoft Excel is a great tool it cannot easily support large data sets. Microsoft Excel will often slow down or crash as data set sizes reach tens or hundreds of thousands rows Excel has a 1,048,576 row limit, which we've written about here. It is common for real-time data sets stored in Snowflake's cloud data lakehouse to be quite large. To import data from Snowflake to Microsoft Excel, the Snowflake query will need to trim the data down to a size Microsoft Excel can manage. There are options within Microsoft Excel, like data model and power query, to enable work with large data sets but they don't allow users to see all the data or use the same spreadsheet flexibility Excel supports with smaller data sets.
  • Sharing - Microsoft Excel is offered in two versions, desktop and Excel 365. Desktop Excel is the traditional version everyone thinks of. The program runs on local computers and is fast and performant. Desktop Excel is difficult to share due to the fact that it runs on users' local computers. It is common for teams to email around multiple different versions creating static one off analyses that need to be updated, versioned, or can get out of sync if two users make updates to the same version. Companies often rely on cloud data storage repositories like Sharepoint to manage all the files with checkout and check-in logic that preserve spreadsheet version integrity. Excel 365 is the new version of Excel that makes sharing easy, though it is not commonly used for big models or data analysis because it is slower than desktop Excel. If a fast powerful spreadsheet with the ability to share across team members is desired, Row Zero is powerful spreadsheet that runs in the cloud and can be shared with anyone
  • Complexity - Creating a live connection from Snowflake to Microsoft Excel is not as easy and intuitive as one might think. There are often complications that can be hard to resolve. The information below does its best to help prevent errors that will inevitably occur and there is plenty of content online to help resolve each one.

Download and Install the Snowflake ODBC Driver

In order to make use of many of the below options for connecting Microsoft Excel to Snowflake, you must first download and install the Snowflake ODBC driver. Instructions and download links can be found in Snowflake ODBC driver documentation. You can also go straight to operating system specific instructions:

Operating system agnostic Snowflake ODBC driver instructions are included below (Linux, Mac OS, Windows)

1. First visit the downloads page and download the appropriate Snowflake ODBC driver for your operating system (Linux, Mac, or Windows). Find the downloaded executable file in your downloads and run it. Follow the installation instructions when prompted. 2. Configure the Snowflake ODBC driver for your environment by first verifying the Snowflake ODBC driver is downloaded and installed on your computer. 3. Create a new DSN and enter configuration credentials/parameters of data source name, user, password, and server. Test the connection to ensure information is correct. Once the test confirms the live connection is successful, return to Excel to continue the process.

4 Best Ways to Connect Snowflake to Excel

The following instructions cover 4 methods for connecting a Snowflake cloud data lakehouse to Excel and one additional option for using a more powerful spreadsheet.

1. Connect Excel to Snowflake with Power Query

  1. Open Excel and click Data > Get Data > From Other Sources > From ODBC. Excel get data from ODBC
  2. In the 'From ODBC' menu select the data source name given when the ODBC driver was installed. Excel From ODBC menu
  3. Click on advanced options to enter a SQL statement to be executed upon connection or use the bottom drop down to select methods for reducing row size for the query results. Row size is important because Microsoft Excel can slow down or crash when it tries to open large data sets. Excel from ODBC advanced
  4. When prompted, enter the snowflake username and password for the database. If the database is not password protected select 'default or custom' in the upper right corner. Then press 'Connect' snowflake username and password
  5. A Navigator window will appear showing all the tables in your database. Select the table you want to work with and click 'Load' select and load Snowflake data
  6. Finally, your data will be visible in Microsoft Excel in a data table. Excel data tables have different properties than data normally displayed in Excel but that is content for a separate blog. snowflake data loaded in excel

2. Connect Excel to Snowflake with Power Pivot

  1. Open Excel and click on the 'Power Pivot' heading from the top level menu. Excel Power Pivot Entry
  2. Once the Power Pivot menu opens, click on Manage > Get External Data > From Other Sources. Get external data from other sources
  3. Select 'Others (OLEDB/ODBC)' from the list of options Power pivot
  4. Click on the 'Build' button under the text box. Power Pivot build button
  5. On the Data Link Properties screen click on the 'Provider' tab and select 'Microsoft OLE DB Provider for ODBC Drivers.' Power Piot Provider tab
  6. Return to the connection tab and select your DSN from the 'Data Source Name' drop-down. If you don't see your Snowflake DSN, review the Snowflake ODBC download instructions.
  7. Enter your user name and password, and test the connection. Data link properties test
  8. Click 'Next' and use the 'Select Tables and Views' page of the wizard to choose the tables to import.

3. Connect Excel to Snowflake with Microsoft Query

  1. In Excel, click on the data tab, then 'other sources' and 'Microsoft Query.' snowflake excel microsoft query
  2. In the 'Choose Data Source' Menu, select your DSN from the list. Choose data source
  3. Uncheck the box for 'Use the Query Wizard to Create/Edit Queries' and click 'Ok. uncheck query wizard
  4. On the following screen select the tables to add to your query and click the 'add' button.
  5. In the graphical editor there are options to filter, sort, join, and create other parameters for your query.

4. Download and Open CSV

The CSV option is simple and free but is not an actual live connection between Excel and Snowflake. CSV import is a one-time data import to Excel that will need to be repeated every time the data in Snowflake changes. Follow the steps below for the CSV import from Snowflake to Excel.

  1. Log in to your Snowflake Account
  2. Create a new worksheet in Snowflake Create Snowflake Worksheet
  3. Write your query. 'Select * from tablename' is an easy way to pull in all data from a particular table. If SQL is a familiar language, write a more complex query to pull the data needed into Excel.
  4. Choose the compute warehouse in the upper right.
  5. Click the blue run button in the upper right-hand corner. Execute Snowflake query
  6. Download results by clicking on the download button and selecting 'download as csv.' Snowflake results download
  7. Open the csv with Microsoft Excel. Open Microsoft Excel and click on Get Data > From File > From Text/CSV. Navigate to the file and click 'open.' Excel will open a preview window. Then click 'Load' to open the data. load CSV data to Excel
  8. Repeat the process if Snowflake data is updated.

5. Row Zero

Connect Snowflake to Row Zero

Row Zero is a blazingly fast spreadsheet designed for big data. Row Zero spreadsheets run in the cloud, making it easy to connect to data warehouses, like Snowflake, and import giant data sets. Once data is in Row Zero, users can filter/sort, pivot, graph, and use all the spreadsheet functions they are familiar with.

There are 3 steps to easily connect Snowflake to Row Zero and analyze data in the spreadsheet. Follow the instructions below to get started.

  1. Connect Row Zero to your Snowflake account
  2. Write a query to import data
  3. Begin analysis in Row Zero

1. Connect Row Zero to Snowflake

In Row Zero click on the 'data source' icon in the right-hand menu and then click the button for 'New data source.' New data source Add new connection

The connection requires 5 pieces of information defined below.

  • Name - This field can be anything that helps identify the data source being connected. (e.g. "Production Data" or "Product Metrics")
  • Description - (optional) Can be anything to help describe the data source
  • Account name - This is the snowflake account name. It can be found and copied from Snowflake as identified in the image below. When it is copied and pasted from Snowflake into Row Zero, it will paste with a decimal between the two alphanumeric identifiers. The decimal must be replaced by a hyphen ( - ). For example, "HW97234.DV88430" should be modified to "HW97234-DV88430" when pasted into Row Zero. See the image below. Snowflake account name
  • Username - The username used to log into your Snowflake account.
  • Password - The password used to log into your Snowflake account. Snowflake account information

Once all the information is entered, hit 'Test connection' to ensure the information is correct. If the test returns green, you will be asked to select the 'Warehouse' and 'Role' before you can '+ Add Source.' Snowflake add source

2. Write a query to import data

Now that Snowflake is connected to Row Zero, begin writing a query to pull real-time data into the spreadsheet. First select the 'Database' and 'Schema' from the two drop downs. Write a query in the query editor. The easiest query to write is the 'select *' statement, which pulls in the entire table. Example:

select * from events

Row Zero is equipped to handle large cloud data sets (and local) and will easily ingest the entirety of a database. Write more complicated queries to pull in various subsets of data stored in Snowflake. All results will be displayed in the 'connected data table' in the spreadsheet. Snowflake write query

3. Begin analysis in Row Zero

Once the data is loaded into the data table use all the features in Row Zero to analyze, filter/sort, pivot, and graph the data. Build a model with real-time data.

Snowflake query table graph

Conclusion

After reviewing many of the most common options for connecting Snowflake cloud data warehouse to Excel, it is clear there are many trade-offs to consider. The advantages to a live connection are, making dashboards, and working on live data models. All are possible with a Microsoft Excel connection to Snowflake. There are several downsides to connecting Snowflake to Excel. First, Many options are somewhat complicated. The step by step instructions outlined in this post are not terribly complicated but can stump even technical analysts navigating different versions of Excel and drivers. Second, due to Excel running locally, the ODBC drivers must be downloaded and installed on each individual computer, which prevents Excel workbooks from being shared with others. Third, Excel begins to slow down the more data you load. Even with Power Pivot and data tables, Excel will slow down or freeze with big data sets. The processing power of each local computer is yet another constraint. While Excel is the ubiquitous analytics software, Row Zero offers a more robust spreadsheet option that functions just like Excel but with more power for bigger data sets, easy shareability, and easier connections from Row Zero to Snowflake. Give Row Zero a try for free.

If you use another cloud data warehouse or blob storage, Row Zero also connects to Redshift, Databricks, Postgres, and S3.

FAQs