November 10, 2023

How to Automate Switching Your Snowflake Datasource with Power BI Deployment Pipelines

By Gavin Pedersen

Power BI Deployment Pipelines are great for several reasons. They not only do away with the manual aspects of moving Power BI content from one workspace to another in the development lifecycle, but they can also automate the transitioning of the data sources used by your datasets from one environment to another.

We already have a great blog about Power BI Deployment Pipelines and how to create them, so if you’re new to this topic, start there. 

In this blog, we will learn how to use deployment pipelines to change which Snowflake Data Cloud environment your dataset is leveraging as it gets moved from one stage in the pipeline to the next. 

Why You Should Automate Switching Your Snowflake Data Sources

Many development teams like to use pre-production data during the development process and reserve their production environment for when the dashboard is released to their end users. This methodology, which is a best practice, ensures that Snowflake production resources are reserved exclusively for production analytics tools. 

Automating these processes ensures consistency and accuracy that would be lacking if done manually. Thankfully, the deployment pipeline feature in Power BI allows you to create rules for switching which data source your dataset uses as it moves from one stage in the pipeline to the next.

How to Automate Switching your Snowflake Data Sources with Power BI Deployment Pipelines

In order to automate switching which Snowflake environment your dataset is using, you will need to configure a rule within the deployment pipeline. In general, you can create two kinds of rules: data source and parameter rules. 

Unfortunately, at the time of writing this blog, Power BI does not allow you to create a data source rule with a Snowflake connection. However, you can use parameter rules as a simple workaround to this limitation.

Please review this full blog on How to Parameterize Snowflake Data Sources in Power BI. Once you have read the blog and created the parameters for your Snowflake data source, pick up from the steps below:

Step 1: Go to your deployment pipeline page within the Power BI Service

Step 2: Click on the vertical ellipses of the dataset that needs the rule configured and then click Configure rules. – note that you will create the rule in the workspace/stage of the deployment pipeline where the connection change needs to occur

Step 3: In the panel that appears on the right, click Parameter rules and then the green + Add rule button

Step 4: Select which parameter you want to use in the From drop-down – the example below uses Snowflake Connection as the name of the parameter

Step 5: Choose a value for the parameter. If you do not see the value you want to change to, select Other and then simply type the value into the free-form text box – in the example below, we are switching the connection to TEST_MANUFACTURING.

Step 6: Click on the green Save button

You will need to redeploy and then refresh the dataset in order for it to use the parameter rule you have just created. Every time someone uses the deployment pipeline to promote the dataset into the workspace, the value of the parameter, and therefore the Snowflake data source, will automatically change. 

Closing

Changing the Snowflake data source that your Power BI dataset uses doesn’t have to be a manual process. Creating a parameter for your Snowflake data source and then a rule to change the value of that parameter in the deployment pipeline is a fast, effective, and consistent way of employing this kind of data governance.

Have more Power BI questions?
Our team of Power BI experts is here to help!

FAQs

As of now, no. You must use a parameter rule to switch your Snowflake connection with a deployment pipeline.

You can easily create a rule for each parameter at the same stage of the deployment pipeline.

Data Coach is our premium analytics training program with one-on-one coaching from renowned experts.

Accelerate and automate your data projects with the phData Toolkit