Introduction
The world of business intelligence and big data has never been more competitive than it is today. The landscape of companies competing in this space is ever changing, with some technologies separating themselves with their innovative solutions.
At phData, we have the luxury of working with a diverse array of clients differing in industry, location, and size. Our visibility and experience with these different companies and use cases, also our technology partnerships, allows us to gauge the market for different emerging technologies and to analyze the current technological landscape.
Two of the platforms that we have seen emerge as a popular combination for cloud data warehousing and business intelligence and reporting are Snowflake and Microsoft Power BI.
The objective of this whitepaper is to provide context for the relationship between Snowflake and Power BI, some best practices for optimizing solutions that leverage both, and to explain why phData is uniquely positioned to help service these two technologies.
Note: It is important to call out that selecting tools for your analytics needs is just a smaller component of creating and implementing an analytics strategy. Much of what is discussed in this whitepaper will assume some level of analytics strategy has been defined.
Want to Save This Guide for Later?
No problem! Just click this button and fill out the form to download it.
(You can read the full guide without giving us your email — keep scrolling!)
Why Discuss Snowflake and Power BI?
Snowflake is a cloud-based data warehousing company that provides a SaaS platform for scalable data storage, processing, and analytics available on multiple cloud environments.
Having gone public in 2020 with what at the time was the largest software IPO in history, Snowflake continues to invest and grow rapidly as organizations move to the cloud for their data warehousing and processing needs.
Power BI is a data visualization and business intelligence platform developed by Microsoft. Since its debut in 2015, Power BI has undergone meaningful updates that have made it a clear leader not just in data visualization but in the business intelligence space as well, including packaging Power BI alongside other workloads within Microsoft Fabric.
Snowflake and Power BI are market leaders in their respective categories, each leading in innovation and growth amongst cloud database and business intelligence platforms. Because of their impact, they are a common combination for companies when selecting tools for their analytics and reporting needs.
The Microsoft and Snowflake Conundrum
Despite dozens of clients that phData has helped successfully leverage Snowflake and Power BI, there is still some apprehension from both clients and vendors in using the two platforms together.
One of the biggest issues that contributes to this hesitation is that although Snowflake is an industry leader in cloud data warehousing, Microsoft has its own solutions in Azure Synapse and now Fabric. If Microsoft had their way, they would have clients push all of their storage and compute to their platforms.
On the other hand, Snowflake wants to drive as much storage and compute onto their platform as possible too. One of the easiest ways for Snowflake to achieve this is to have analytics solutions query their data warehouse in real time (also known as DirectQuery). This ensures the maximum amount of Snowflake consumption possible.
While Microsoft might be fine with your organization using Snowflake – especially when Snowflake is deployed on Azure – Power BI is most efficiently used when running its reports on an imported or cached model of the data, which limits Snowflake’s consumption.
Despite improvements made by both vendors in the last few years, there are still some best practices to consider when deploying Power BI solutions using Snowflake as a data source. We’ll get into more details in this whitepaper on what those best practices and considerations are, but for now this context should help you understand how the two companies view one another in the market.
Why phData?
phData is one of Snowflake’s most trusted implementation partners – so much so that in 2025 phData was awarded Snowflake’s Americas Partner of the Year for the fifth consecutive year.
We are so incredibly grateful to be able to partner with an innovative company like Snowflake and help our common customers achieve their data goals. phData is committed to Snowflake and we firmly believe that Snowflake’s brightest days are still ahead.
During the past couple of years, phData has seen tremendous growth in its Power BI Analytics business. With our deep commitment to Snowflake and our team of export Power BI consultants, phData is uniquely positioned to help you and your organization take on the challenges of deploying Snowflake and Power BI.
Common Issues, Complaints, and Misconceptions
As discussed in a previous section, there are a lot of complexities that can arise when using these two platforms, along with some misconceptions. In this section, we’ll take a look at some of those items and discuss whether or not they have any merit.
Microsoft Doesn’t Want You Using Snowflake
Our Rating: Somewhat True
Microsoft would obviously prefer that clients use Azure Synapse or Fabric over other cloud data warehouses like Snowflake. However, Snowflake runs very efficiently on Azure – so even though it’s not the ideal situation for Microsoft as a business, they still see consumption when organizations host Snowflake on Azure.
With Azure being a common landing spot for Snowflake, the term “frenemy” is appropriate here, especially if the client is using other Microsoft tools. Both companies seem to recognize this “necessary evil” dynamic as they continue to be partners as of 2025. Simply put, Microsoft and Snowflake are both too prolific for either one of them to not play nice.
You Can’t Use DAX with DirectQuery in Snowflake
Our Rating: Mostly False
Microsoft has made significant improvements to DirectQuery in recent years. While some DAX functions and modeling features remain restricted, these limitations aren’t unique to Snowflake—they apply to all sources when using DirectQuery.
Previously, time-intelligence functions like TOTALYTD, DATESYTD, and EOMONTH were unavailable; now they work in measures when a proper Date table is used and marked (DirectQuery still doesn’t create built-in date hierarchies). The largest impact is in calculated columns, which are limited to intra-row operations—referencing only columns in the same row without aggregates like AVERAGE or MAX.
While the absence of certain functions can be a drawback, most core analytical scenarios are still possible in DirectQuery with careful modeling and by pushing complex logic upstream into Snowflake, which is a generally recommended best practice.
Power BI Throttles Snowflake
Our Rating: Somewhat True
Companies invest in Snowflake because of its ease of use and performance. Therefore, degradation in the performance from analytics workloads is not met with any amount of excitement. While it is true that the way that Power BI constructs queries to be sent back to the source while using DirectQuery mode does indeed throttle Snowflake, this throttling is not unique to Snowflake.
This is one of the many reasons that Power BI is more efficient when using Import mode, because the data gets cached into Power BI.
When Power BI loads a visual, it sends several queries back to the source data instead of one query, This causes more queries being needed to render a report when compared to other BI tools such as Tableau or Sigma. These added queries do in fact restrict Power BI’s rendering time by sending more queries than other tools to accomplish similar tasks.
The good news is that Microsoft recognizes that query throttling in DirectQuery is an issue and they continue to invest in some very welcomed updates.
Horizontal Fusion is an example of one of those updates and is aimed at processing DAX queries more efficiently by reducing the number of source queries required to render a visual. Horizontal Fusion analyzes the queries being sent back to the source data and identifies opportunities to consolidate those queries to reduce the amount of round trips Power BI needs to take. Vertical Fusion is a similar optimization technique, albeit not a new one, that we cover in a later section.
The native Arrow Database Connectivity (ADBC) Snowflake connector is another recent update, made generally available in July 2025, that improves query performance with its use of a columnar storage format and optimized data transfer methods. We will dive deeper into the native snowflake connector in the next section.
Best Practices for Using Snowflake and Power BI
As suggested in the previous section, there are some complexities when using Power BI with Snowflake that should be considered. Although developing a solution in Power BI using Snowflake is similar to using other data sources, we do have some specific suggestions when using the two tools together.
All of the suggestions in the next section are elements you can control within Power BI; there are also optimizations we recommend making from the Snowflake side too which are briefly listed at the end of this section.
Use Power BI’s Native Snowflake Connector
Like many other data sources, Power BI can connect directly to Snowflake using a native connector. The initial release of this connector shipped in 2019 and is now referred to as implementation 1.0. Prior to this, a locally established ODBC connector was the only way to connect to Snowflake from Power BI.
The implementation of the 1.0 connector is built on top of ODBC technology. This version remains reliable with years of significant usage across all types of use cases. Support for custom SQL queries to be run against Snowflake using DirectQuery was released in 2021 and support for query tagging as of April 2025.
Microsoft introduced the Snowflake connector 2.0 in January of 2025 and it became the default version in March of the same year. The 2.0 version is built using the Arrow Database Connectivity (ADBC) driver which is especially efficient at fetching large datasets and incorporates security enhancements. The 2.0 version does support custom SQL for DirectQuery. Query tagging is not yet available, but said to be coming soon. While the 1.0 version still remains available, 2.0 will undoubtedly continue to bring significant improvements as it evolves.
In addition to being easier for Power BI developers to configure both versions of the connector allows for Microsoft Entra SSO (formerly Azure AD SSO) to be enabled.
Choose Import or DirectQuery Mode Carefully
Power BI has two main connection types when connecting to Snowflake, and indeed all data sources, Import and DirectQuery. We will explain the benefits and drawbacks, when to use them, and when to pair them together in a Composite model.
Using Import
mport mode is the most common connection type we see when working with customers. Import mode is aptly named as it allows you physically move the data out of the data source and store it in memory. During ingestion, developers can make full use of Power BI’s low/no-code transformation tool, Power Query to further massage the data. Once stored, Power BI’s compute engine, called VertiPaq, provides significant benefits.
The VertiPaq engine uses a columnar storage format that is optimized for data compression and analytical queries, which differs from traditional row-based databases like SQL Server. This structure enables highly efficient data compression – the general rule of thumb is that you can typically expect about 10x compression when importing data into Power BI – and allows data to be stored directly in memory. As a result, Power BI can perform analytical queries and DAX calculations much faster than systems designed for row-by-row processing.
Import mode creates a cache of the dataset, requiring a scheduled refresh in Power BI Service to keep data current. Refreshes of large datasets may require thoughtful up-front planning. Features like Large Dataset Storage and Incremental Refresh, available with a Premium/Fabric Capacity, should be considered for importing large data volumes.
Using DirectQuery
You can also connect to data sources using DirectQuery mode, which creates a live connection. Although a majority of use cases for tools like Power BI rely on cached data, there remain some that require DirectQuery including:
- Near real-time insights
- Very large data sources that would exceed the import limits
- Leveraging pass-through authentication to source data
In late 2021, Power BI introduced custom SQL queries to Snowflake using DirectQuery. The connector is built in a way that will format the input SQL statement as Snowflake SQL when the command is submitted, allowing users to write their query in standard SQL. One consideration to using the custom SQL queries is that you will need to use fully qualified table names in the query to ensure it connects successfully. See below for an example:
SELECT *
FROM table(information_schema.task_history(scheduled_time_range_start=>DATEADD('hour', -1, current_timestamp()),task_name=>'load'))
While DirectQuery has some great use-cases, it is not without trade-offs. Using DirectQuery may allow you to see more near real-time data, but your ability to leverage certain DAX functions, create calculated columns, and use much of the ETL capabilities of Power Query will be lost. For situations where data transformation is imperative, we recommend pushing these transformations back to Snowflake. Most importantly perhaps is that in most cases, user experience will suffer. Unlike with Import mode, DirectQuery does not move any of the data out of Snowflake. Because of this, each time a user interacts with the dashboard, it generates a new SQL query that has to fetch the data from Snowflake and render it on the dashboard, which takes time.
Using a Composite Model
For users wanting the best of Import and DirectQuery methods, Power BI also allows for Composite models. In a Composite model, the storage mode can be configured differently for each table. With Snowflake, we recommend setting the largest, most frequently updated fact tables as DirectQuery connections, and the rest as Import or dual mode.
This configuration leverages the benefits of DirectQuery (e.g. having near-real time data) with the increased performance that comes with caching data for tables that do not have very frequent updates.
For more information on composite models, check out Microsoft’s official documentation.
Wrapping Up DirectQuery vs Import
At phData, we suggest using whatever connection method is best for our client’s specific use case. Selecting DirectQuery as the connection mode, when unnecessary, can lead to slower performance and may require the consideration of DirectQuery optimization techniques (e.g. aggregate tables).
In Import mode, Power BI caches the data in a highly optimized format that often leads to improved report performance and a better end-user experience. On the other hand, when the data is rapidly changing, not using DirectQuery in your fact tables can result in decisions being made based on data that is not up-to-date.
It is important to note that from a performance perspective, the decision to use Import or DirectQuery is tool agnostic, however there may be cost implications for either one.
Model Your Data Appropriately
Creating an optimal and efficient data model can be the difference between great and poor performance, especially for large models or when using DirectQuery.
After choosing the storage mode for your data (Import, DirectQuery, Composite), it is imperative to create an efficient and optimized data model to support your Power BI reporting needs.
Here are some recommended best practices for building data models in Power BI to optimize your Snowflake experience:
Use a STAR schema – A star schema will simplify joins and queries, process transactions faster, and allows for continuous development as new data becomes available without worrying about changing the granularity of the entire source (Power BI’s VertiPaq engine is also optimized for star schema models). We recommend utilizing Snowflake to build out the necessary views and tables for your comprehensive data models. These tables and views that you build will be pieced together to create the enterprise reporting data models that will ultimately be used to feed your Power BI reporting.
Use only the necessary data in your model – Limiting the columns in your data model is almost always more impactful to the model’s performance than limiting the amount of rows. Whether you limit the columns in your Snowflake reporting view or using Power Query, unnecessary data will slow rendering and data refresh times.
Use materialized views or aggregated tables where possible – It is best practice to create materialized views for your reporting needs rather than querying the actual source tables in your production environment. If your reporting does not require your data to be at its lowest level of granularity, consider aggregating your data to the required level of granularity to reduce the amount of data being pulled in and the subsequent workloads that would otherwise be necessary to aggregate the data once in Power BI Desktop.
Assume referential integrity in relationships – Choosing the Assume Referential Integrity option will streamline the queries sent between tables in a relationship and will result in quicker render times. This tactic can be used in all connection modes, but is especially impactful in DirectQuery mode.
Avoid bi-directional filtering – Enabling bi-directional filtering in your relationships not only makes your data model more complex, but it can also cause undesired end-user behavior.
Hide the unnecessary relationship fields – Once you have built your data model in Power BI, hide the fields in your relationships you don’t want users to have access to. This wont necessarily impact performance but your end users will thank you for supplying them with a clean and easy to use data model.
Set up Microsoft Entra SSO for Snowflake
When setting up Power BI Premium or Fabric Capacity, admins will be prompted to select which region they want the data center for the dedicated capacity to be located in. Minimizing the geographical distance between your Snowflake and Power BI data centers can reduce latency and improve performance.
If you’re running both of them on Azure, selecting the same data center will result in the best performance for your setup.
As shown in Snowflake’s documentation on this topic, you can see how the Microsoft Entra process passes your Microsoft credentials to Snowflake to allow for authentication.
The Microsoft Entra to Snowflake Process:
The user logs into the Power BI service using Microsoft Entra ID.
Optionally, Microsoft Entra ID can verify the user through an IdP via SAML. Currently, Microsoft only supports Microsoft Entra ID as the IdP for Power BI SSO.
When the user connects to Snowflake, the Power BI service asks Microsoft Entra ID to give it a token for Snowflake.
The Power BI service uses the embedded Snowflake driver to send the Microsoft Entra ID token to Snowflake as part of the connection string.
Snowflake validates the token, extracts the username from the token, maps it to the Snowflake user, and creates a Snowflake session for the Power BI service using the user’s default role.
Use the Right Azure VM For Your Gateways
A gateway is a piece of software, typically installed on an Azure Virtual Machine, that acts as a bridge, connecting an on-premise data source to Power BI Service. The question in this case, is why would someone use an on-premise gateway with a Snowflake given that it’s a cloud data warehouse?
There are two scenarios we see that call for this configuration:
Snowflake Private Link is used – in this case a gateway is required to refresh your datasets from a workspace.
Your IT or Cloud Security team requires an added layer of security between Snowflake and Power BI.
Once you’ve identified the need for a gateway, you should host it on an Azure Virtual Machine, not an individual’s computer.
Different Azure VMs are meant for different types of jobs. Make sure to find the right one for your use case. Here are some considerations when choosing the right VM:
Decide whether your main connection method is going to be Import or DirectQuery
What is the volume of data that needs to be processed during the highest period of traffic in a given day or week.
Use the Azure pricing sheet to find out which VMs match your budget.
In general there are two different series of Azure VMs we recommend looking at:
D-Series (v4 or v5): these VMs are for general purpose computing and will do a fine job with small workloads.
E-Series (v4 or v5): these VMs are optimized for in-memory analytical workloads and large relational database servers. The E-series VMs are our phData’s go to recommendation.
Note: no matter which series you choose, it’s always easy to scale or down, and even out if you want to cluster your gateways to provide load balancing and fault protection.
Ensure Data Center Distance is Minimized
When setting up Power BI Premium or Fabric Capacity, admins will be prompted to select which region they want the data center for the dedicated capacity to be located in. Minimizing the geographical distance between your Snowflake and Power BI data centers can reduce latency and improve performance.
If you’re running both of them on Azure, selecting the same data center will result in the best performance for your setup.
Note: as of when this whitepaper was written you cannot change the location of your Power BI Premium or Fabric Capacity data center. If this is required you will need to delete the capacity and create a new one from scratch.
Increase Concurrent Query Limit for Your Data Model
When using DirectQuery for your model, the default amount of concurrent queries that can run to the source is 10, but you can increase that to 30 in your Settings in Power BI Desktop.
In your Power BI Desktop report – go to File > Options and Settings > Options > DirectQuery(Current File).
Note: This does increase concurrent queries to the source system if your Snowflake instance is not appropriately sized for the queries being generated. If this is your case, this may not help your performance.
If you are using an On-Premises Gateway, you can adjust the number of mashup engine containers running simultaneously on the gateway. These containers are the functions running your queries, and are the same components that run in Power BI Service and Desktop.
The more mashup containers, the more queries you can concurrently run through the gateway. Finding a good balance here is critical as these mashup containers require resources and will affect the VM’s performance.
Here are the steps on how to change this setting:
Open the gateway configuration file on the machine
Find the
MashipDefaultPoolContainerMaxCountsetting and change it from the default0to the desired valueSave and restart the gateway
Using Artificial Intelligence with Power BI
AI features in Power BI are evolving rapidly, and their integration can dramatically improve both the speed of report development and the accessibility of insights. With the introduction of Microsoft Copilot in Power BI, report developers and business users can now leverage AI for everything from writing complex DAX calculations to conversationally exploring datasets. When combined with a high performance cloud-data warehouse like Snowflake, these tools can help teams move from raw data to actionable insight faster than ever.
AI-Assisted Development with Copilot
What is it
Copilot in Power BI can accelerate development by allowing developers to use natural language to generate DAX measures and calculated columns. Instead of manually writing formulas, users can describe the intent of the DAX calculation, and Copilot will create the code for you.
Key considerations:
Be specific in your prompts – Include the metric, time frame, and any filters you need. For example: “Create a DAX measure for year-to-date sales for the current year, filtered by active customers only.”
Validate the output – AI can accelerate the development process, but it’s essential to review the generated DAX for correctness and performance.
Leverage star schemas – Like everything else in Power BI, Copilot works best when the data model is clean, well-named, and contains supporting reference tables (like a robust date table).
Optimize for Snowflake performance – Even if the DAX produced by Copilot is correct, the generated queries should be tested when using DirectQuery to ensure optimal performance with Snowflake.
“Talk to Your Data” with Copilot
What is it
Copilot in Power BI also allows users to interact with the data on the dashboard using natural language, either by automatically summarizing existing report pages or by letting the user ask questions about the data. This capability can help reduce the gap between complex data and business understanding, especially for users with limited data or visual literacy.
Key considerations:
Use business-friendly naming conventions – In the semantic model, make sure to use business friendly names and provide the model synonyms to help it better understand variances in how users may ask questions.
Balance DirectQuery and Import mode – DirectQuery enables live Snowflake queries but may add latency for complex questions.
Other Considerations
In addition to the other strategies listed above, here are some more general tips for making sure that your Snowflake and Power BI experience is optimized.
Snowflake
Dedicated warehouse that are sized appropriately specifically for Power BI use cases
Optimize Storage for Performance by using materialized views and automatic clustering
Results caching can be turned on to help with performance
Power BI
Write simple and efficient DAX to limit the use of excessive computation and materialization at runtime.
Limit visuals on the page and consider the use of small-multiples to reduce queries sent to the source.
Apply report/page/visual level filters to pre-filter data.
Limit cross-visual interactions to reduce queries sent back to the source.
Use Apply buttons on your filters/slicers to reduce queries sent back to the source and limit constant visual updates that can degrade the user experience.
The Microsoft Advantage
If you’re using Snowflake for your data warehouse and are evaluating BI tools, make sure you consider Power BI. Microsoft Power BI has been a leader on the Gartner Magic Quadrant for Analytics and Business Intelligence Platforms for the past several years.
Power BI’s cost, easy learning curve, and continued investment from Microsoft in new and innovative features make it a popular choice for commercial and enterprise organizations.
Cost
From a pricing standpoint, Power BI Pro licenses – used by developers to publish and share content – start at $14 per user/month, while a dedicated Fabric Capacity (formerly Power BI Premium Capacity) starts at just over $5,000 per month and increases depending on how much compute and storage you need.
Organizations that have a Microsoft 365 or Office 365 E5 plan get Pro licenses included at no additional cost. For organizations that don’t need dedicated capacity, most premium features are still available through Premium Per User (PPU) licensing at $24 per user/month.
Determining the right mix of Pro, PPU, and capacity-based licensing should be approached carefully, considering factors such as the number of report creators, dataset sizes, refresh frequency, and concurrency requirements.
Microsoft Integration
One of the most attractive features for organizations moving to the Power BI is the integration with the Microsoft Office suite of products. Reports and dashboards can be embedded directly into Teams channels for collaboration, published to SharePoint pages for easy access, or embedded into Power Point for executive presentations. This tight integration ensures that insights are available in the tools people use every day, reducing friction and accelerating decision-making.
Microsoft Excel users benefit from a two-way relationship with Power BI – semantic models can be analyzed in Excel using PivotTables, while Excel workbooks can serve as data sources for Power BI reports, and even blend that data with Snowflake. Integration with Outlook allows links and snapshots to be shared directly within email threads, keeping conversations connected to the latest data. By bridging the gap between analytics and productivity tools, Power BI helps organizations use data without disrupting established processes.
Monthly Updates
Microsoft continues to show investment in the product and its user base by releasing monthly Power BI updates. These updates include new features, product enhancements, and additional or updates to data source connectors (like the recent addition of the ADBC Snowflake connector).
To keep up with all the new updates in Power BI, check out Microsoft’s official documentation.
phData’s Power BI team stays up to date with the latest feature releases and additions to the Microsoft roadmap, and how that may impact usage when connecting to Snowflake.
See some of our blogs on Power BI product features and how to best use them:
Client Success Stories
Consumer Packaged Goods Industry
A global CPG company partnered with phData to implement and scale the adoption of Power BI Premium / Fabric Capacity, leveraging data hosted in Snowflake. From the outset, phData guided the deployment with best practices in capacity planning, gateway virtual machine sizing, and access policy design. The result is a healthy, scalable reporting platform that empowers users to fully harness the combined capabilities of Power BI and Snowflake for faster, data-driven decisions.
Pharmaceutical Industry
A large pharmaceutical company with stringent data security requirements partnered with phData to implement an analytics strategy using Power BI with data hosted in Snowflake. To meet strict privacy and data residency standards, phData delivered high-performance dashboards using DirectQuery connections and passthrough authentication. This approach enables the organization to gain fast insights in Power BI while fully leveraging Snowflake’s role-based security policies.
Other Industries We’ve Worked With
- Financial Services
- Manufacturing
- Food Services
- Retail
- Media & Technology
In Closing
Snowflake and Power BI aren’t just leaders in analytics—they’re shaping its future. Snowflake’s continued innovation in scalability, performance, and cross-cloud capabilities is redefining what’s possible with enterprise data. At the same time, Microsoft continues to enhance Power BI with faster performance, richer visualizations, and AI-driven insights, solidifying its position as the industry’s top BI platform.
Together, they enable organizations to move beyond static reporting toward governed, real-time, self-service analytics at scale. By pairing Snowflake’s secure, high-performance data foundation with Power BI’s intuitive and powerful visualization capabilities, companies can deliver trusted insights where and when they’re needed most—turning data into a true competitive advantage.
At phData, we help organizations harness these innovations to build secure, scalable, and future-ready analytics solutions.
Check phData’s Analytics and Visualization page on our website for more details on how we think about developing these types of solutions.
