Breaking the Ice: Integrating Snowflake with Power BI

A simple guide on how to connect Snowflake data in Power BI to create reports, publish them, and schedule refreshes.

Mithil Oswal

Published in

helpshift-engineering

6 min readApr 22, 2024

Pre-requisites

Well, since you’ve already reached this page, I’m assuming that you know of, and have access to both the tools — Snowflake as well as Power BI.

In case you do not have both / any of these accounts, you can try signing up below:

Once you have completed the setup and have things up & running, you can proceed to the next steps.

Creating some dummy data in Snowflake

For our demo purposes, we will create some dummy data.

Using the list of SQL queries below, we will create an employee database, an employee schema, & an employee table.

CREATE DATABASE employee_database; 

CREATE SCHEMA employee_database.employee_schema; 

CREATE TABLE employee_database.employee_schema.employee_table
(
    id NUMBER, 
    name VARCHAR,
    salary NUMBER
);

2. Our newly created Snowflake structure for “employee” data should look like this —

3. Let’s add some test data to the table & see how it looks. Also, you can “describe” the table to view the schema.

DESC TABLE employee_database.employee_schema.employee_table; 

INSERT INTO employee_database.employee_schema.employee_table(id, name, salary) 
    VALUES (111, 'Max', 50000), 
           (222, 'Irene', 55000),
           (333, 'Tim', 70000), 
           (444, 'Harris', 41000), 
           (555, 'Isabelle', 97000), 
           (666, 'Lennon', 87000); 

SELECT * FROM employee_database.employee_schema.employee_table;

→ Now that we have the required data set up in Snowflake, we can move to Power BI Desktop for the next section of this process. ✅

Setting up the Snowflake connection in Power BI Desktop and creating reports

Open Power BI Desktop → Get Data → Snowflake → Connect. Click “Advanced options” on the dialog box that appears next.

2. You will now need to add some credentials for the connection details. Although some of these may be optional, it is recommended to include them.

Server: <your server name>[Note: It will be in the format of <account_name/locator>.<region_id>.snowflakecomputing.com and remember to EXCLUDE the https://]
Warehouse: <your warehouse name>
Role: <your role name>
Database: <your database name> [Note: This should be in CAPITAL LETTERS only]
SQL statement: <your SQL query>

In the next step, I will explain where to find this information in your Snowflake account.

Setting up Snowflake connection in Power BI

For our use case, we can add the following details —

Server: <your server name>
Warehouse: COMPUTE_WH
Role: ACCOUNTADMIN
Database: EMPLOYEE_DATABASE
SQL statement: SELECT * FROM employee_database.employee_schema.employee_table

3. Now, let’s go back to our Snowflake account to fetch some credentials.

For the Server name, click on your initials on the bottom right → Account → ID → Copy account URL. Don’t forget to remove the “https://” when pasting it.

Role and Warehouse names can be taken from the top right. For a new account, they are usually the same default names.

In a larger organization, you will be provided with these details during your Snowflake account setup, based on your restricted access.

4. Back to Power BI now. After saving the credential details, you will be asked for a “Username” and “Password”.

👉 This is your login information from your Snowflake account.

5. If everything works out as expected, you should be able to see the result of your SQL query. Simply click “Load”.

6. Finally, to wrap on the connection side - choose a mode. Pretty self-explanatory here.

Import:
- Data from the source is loaded into Power BI and stored within the file itself.
- Faster for small to medium-sized datasets. Can perform extensive data transformations, modeling, and calculations on the imported data.
- Data can be refreshed based on a schedule.
Direct Query:
- Power BI connects directly to the data source in real-time and queries the data on-demand.
- Allows you to work with the most current data available in the source system.
- Can be slower for large datasets or complex queries because it relies on the performance of the source system.

Personally, I prefer to use the “Import” mode for my use case since I need faster reporting.

7. Time to create reports! To demonstrate, I’ve just made a simple bar chart based on “employee_name” and “employee_salary” using our sample data.

8. Let’s save the report & then publish it to “My Workspace” in the Power BI Service online.

Note: When you publish a Power BI report to your workspace, it also publishes the corresponding dataset automatically.

9. Voila, your report & dataset aka semantic model have been published! 🥳

Scheduling Snowflake data refresh in Power BI Service

Now that we can access our report in the Power BI Service online, it is time to connect Power BI Service with our Snowflake account, so that if we make any changes to the database, it is reflected in our reports as well as the PBI semantic model.

For this, Click on the Semantic Model → Settings

2. Under “Data source credentials”, you will notice an error. That’s okay, we’ll fix it now. Click on “Edit credentials”

3. Now enter your Snowflake account login details / credentials, and click “Sign in”.

You can choose any Privacy level setting that fits your purpose.

4. You will notice that the error has disappeared because we have connected our Power BI Service account with our Snowflake account.

Moving on to scheduling a data refresh automatically.

5. Turn on the toggle under “Refresh” and select a frequency, time, and notification (if required). Click “Apply”.

Done! 👍

Testing the connection in Power BI Service

To check if our “refresh” would work as expected, let’s make some changes in our “employee_table” in Snowflake and see if it is reflected in our Power BI report.

I will just add some additional records to the table.

INSERT INTO employee_database.employee_schema.employee_table(id, name, salary) 
    VALUES (777, 'Onyx', 100000), 
           (888, 'Selena', 115000), 
           (999, 'Wren', 120000), 
           (123, 'Ariana', 175000),  
           (456, 'Linda', 142000); 

SELECT * FROM employee_database.employee_schema.employee_table;