Connecting Snowflake to Microsoft Excel can make it easy for business teams to pull data into a spreadsheet for analysis. The spreadsheet is often the prefered tool for building models and combining various data sets into one analysis. The following post covers the benefits and considerations and provides step-by-step instructions for connecting Snowflake to Microsoft Excel. The post also includes instructions for connecting Snowflake to Row Zero, a blazingly fast spreadsheet designed for big data. Row Zero makes it much easier to get snowflake data into a spreadsheet and can better process large data sets than Excel. To get started now, watch the video below and connect your Snowflake instance to Row Zero for free..
- What is Snowflake?
- Benefits of connecting Snowflake to Microsoft Excel
- Considerations when connecting Snowflake to Microsoft Excel
- 5 Snowflake to Excel Connection Options
What is Snowflake?
Snowflake Computing is a cloud-based 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 clouds 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
- Production Data - Connecting Microsoft Excel to Snowflake enables financial and accounting models to be built on product or operational data stored in Snowflake.
- Live Models - Once Snowflake is connected to a spreadsheet, like Microsoft Excel, 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.
- Dashboards - With a connected spreadsheet it is easy to turn a simple analysis into a dashboard. 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 data sets stored in Snowflake 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. If the data set is large and a spreadsheet UI is the preferred way to analyze data, try Row Zero, a blazingly fast spreadsheet designed for big data.
- 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 storage repositories like Sharepoint to manage all the files with checkout and check-in logic that preserve spreadsheet 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 - Connecting data sources 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 erros that will inevitably occur and there is plenty of content online to help resolve each one.
5 Snowflake to Excel Connection Options
Below are instructions for the following methods connecting Snowflake to Excel as well as instructions for Row Zero, a blazingly fast spreadsheet.
- Open CSV (8 steps)
- Power Query (6 steps + ODBC driver installation)
- Power Pivot (8 steps + ODBC driver installation)
- Microsoft Query (5 steps + ODBC driver installation)
- Row Zero (3 steps)
1. Download and Open CSV
The CSV option is simple and free but is not an actual 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.
- Log in to your Snowflake Account
- Create a new worksheet in Snowflake
- 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.
- Choose the compute warehouse in the upper right.
- Click the blue run button in the upper right-hand corner.
- Download results by clicking on the download button and selecting 'download as csv.'
- 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.
- Repeat the process if Snowflake data is updated.
2. Connect Excel to Snowflake with Power Query
- The following instructions assume Snowflake ODBC driver has already been downloaded.. If this is already complete, skip straight to Power Query instructions.
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 documentation here. You can also go straight to operating system specific instructions:
Generic instruction are included below.
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 ODBC driver for your environment by first verifying the driver is downloaded and installed on your computer. 3. Create a new DSN and enter configuration credentials/paramters of data source name, user, password, and server. Test the connection to ensure information is correct. Once the test confirms the connection is successful, return to Excel to continue the process.
Power Query Instructions
- Open Excel and click Data > Get Data > From Other Sources > From ODBC.
- In the 'From ODBC' menu select the data source name given when the ODBC driver was installed.
- 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.
- 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'
- A Navigator window will appear showwing all the tables in your database. Select the table you want to work with and click 'Load'
- 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.
3. Connect Excel to Snowflake with Power Pivot
- The following instructions assume the Snowflake ODBC driver has already been downloaded.
- Open Excel and click on the 'Power Pivot' heading from the top level menu.
- Once the Power Pivot menu opens, click on Manage > Get External Data > From Other Sources. Get external data from other sources
- Select 'Others (OLEDB/ODBC)' from the list of options
- Click on the 'Build' button under the text box.
- On the Data Link Properties screen click on the 'Provider' tab and select 'Microsoft OLE DB Provider for ODBC Drivers.'
- 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.
- Enter your user name and password, and test the connection.
- Click 'Next' and use the 'Select Tables and Views' page of the wizard to choose the tables to import.
4. Connect Excel to Snowflake with Microsoft Query
- The following instructions assume the Snowflake ODBC driver has already been downloaded.
- In Excel, click on the data tab, then 'other sources' and 'Microsoft Query.'
- In the 'Choose Data Source' Menu, select your DSN from the list.
- Uncheck the box for 'Use the Query Wizard to Create/Edit Queries' and click 'Ok.
- On the following screen select the tables to add to your query and click the 'add' button.
- In the graphical editor there are options to filter, sort, join, and create other parameters for your query.
5. 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.
- Connect Row Zero to your Snowflake account
- Write a query to import data
- 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.'
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.
- Username - The username used to log into your Snowflake account.
- Password - The password used to log into your Snowflake account.
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.'
2. Write a query to import data
Now that Snowflake is connected to Row Zero, begin writing a query to pull 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 data sets 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.
3. Begin analysis in Row Zero
After reviewing many of the most common options for conneting Snowflake to Excel, it is clear there are many trade-offs to consider. The advantages are connecting to live data, making dashboards, and working on live models. All are possible with a Microsoft Excel connection to Snowflake. There are several downsides to connecting Excel to Snowflake. First, Many options for connecting Excel to Snowflake 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 options 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.