The Snowflake Data Cloud is an industry-leading advanced data platform provided as Software-as-a-Service (SaaS) for many businesses worldwide across data workloads. With Snowflake being a top choice for cloud-based data storage, integration with Microsoft Power BI was next in line to draw actionable insights from your data.
Despite Power BI having 130+ data connections, connecting to your data sources is simple to do in five easy steps.
In this post, we’ll explain how you can connect to your Snowflake instance as well as the most popular types of connection types you can use within Power BI.
What are the Two Connection Types for Power BI to Snowflake?
Power BI offers two different connection types when connecting to your Snowflake instance; Import and DirectQuery.
*Please note that only AAD SSO within Snowflake supports DirectQuery.*
Import
With the Import Mode, your data resides within the Power BI Model and is compressed down to be stored effectively. In the Import Mode for Power BI, when connecting to your Snowflake instance, it is considered to have much faster performance due to in-memory querying, which makes it the default choice when connecting to your data.
A few limitations around the Import Mode would be data size limits of not being able to have a dataset greater than 1GB, delayed data as it is dependent on a manual refresh and not being able to shift your dataset to DirectQuery once you have selected the Import Mode.
DirectQuery
In the DirectQuery Mode, the reports will query the data source and retrieve the data live as it is on the source. DirectQuery steps in to do closer to real-time reporting while enabling users to refresh the data as needed without the need for a scheduled refresh.
DirectQuery should be used when there is a large dataset, underlying security rules in the data source, or reducing file storage. There are some limitations around DirectQuery reporting, such as limited DAX formulas, limited Power Query functions, and limited display of only 1M rows.
How to Connect Power BI to Snowflake
For this example, we’re going to connect using the Import mode in Power BI to Snowflake is very straightforward.
Step 1: Get Data
You will first launch Power BI and select “Get Data,” and under “Database,” find and select “Snowflake.”
Step 2: Enter Server and Warehouse ID
From here, it will prompt you to enter your Server and Warehouse ID, which can be located on Snowflake. Your server name will be the URL you use while connected to your Snowflake instance.
For example: “demonstration.snowflakecomputing.com” will be your server name, and Warehouse will be the name of the warehouse you are looking to connect to.
*Note* Snowflake now supports custom queries, so you are able to write your own queries when bringing in your dataset. However, it does require you to enter the database name and use the fully qualified name while writing your queries.
For example: “SELECT * FROM DBNAME.SCHEMA.TABLE”
Step 3: Sign In
Once complete, you have to select “OK,” which will prompt you to sign in. At this stage, you can either authenticate through Microsoft Azure Active Directory (AAD) or sign in through Snowflake authentication with a username and password.
Once authenticated, hit the “Connect” button to be able to go to the navigator pane. You will see your server/warehouse name as a folder icon, the databases within that warehouse, and also the tables/views that are within the Database.
Step 4: Select Tables/Views
At this point, you can select the tables you would like to load in. To use the “Select Related Tables” button, you have to put true in for the “Include relationship columns” within the advanced options of the connection string. Once you have selected all of your required tables, you can choose Load or Transform Data.
Step 5: Select Method for Connecting
At this point, you are given the option to use Import or DirectQuery. Select your method of connecting and continue forward. Congratulations, you have successfully connected to your Snowflake Data warehouse!
Conclusion
As you can see, connecting to Snowflake in Power BI is relatively straightforward. In addition to connecting straight to Snowflake, you can also create your centralized enterprise data models using Snowflake if you’re looking for more enterprise solutions.
Snowflake and Power BI can be fully integrated to create industry-wide solutions that allow you to drive business insights and make key business decisions. The future of both of these technologies are very bright, so make sure to keep them both in mind when formulating your organization’s data and analytics strategies.
To learn more about Snowflake or Power BI, check out our library of blog posts such as this one, video tutorials, and more!
