Forum Discussion
SnowFlake - Direct Query - Slow Perfomrance - Adding Role at Power Query
Hello,
I am using Snowflake as source for reports. Some of reports were not changed for over a 1,5 year.
Company made decision, that due to security reasons, some data will be masked and roles will be added. At our, Power Bi side we had to implement roles at Power Query level. Below example what was added:
After this change reports starter to work significantly slower.
From my observation when comparing Snowflake Query History before and after "Role" change - now Power Bi extractor opens whole table > unmasks it > applies query. Before changes, query was executed immadietly, without running select on whole table (even unused parts).
Questions:
1. Is there anything that should be checked by Snowflake team or setting that should be changed by them to speed up the reports?
2. Are there any good practices at power Bi level to speed up Direct Query? Maybe we missed something and can improve via it simple changing settings.
Thank you in advance for help.
Why it's slow now
- Adding roles and masking in Snowflake forces full table scans—even unused columns get queried.
- Power BI now unmasks first, filters later, which breaks query folding and slows things down.✅ What to check
On Snowflake side:
- Use column-level masking, not table-level.
- Add clustering keys and materialized views.
- Ensure warehouse is right-sized and optimized.
On Power BI side:
- Maximize query folding in Power Query.
- Use composite models (import + DirectQuery).
- Reduce visuals, optimize DAX, enable Horizontal Fusion.
5 Replies
Hello! What is the use case of why you are using direct query rather than import?
Why it's slow now
- Adding roles and masking in Snowflake forces full table scans—even unused columns get queried.
- Power BI now unmasks first, filters later, which breaks query folding and slows things down.✅ What to check
On Snowflake side:
- Use column-level masking, not table-level.
- Add clustering keys and materialized views.
- Ensure warehouse is right-sized and optimized.
On Power BI side:
- Maximize query folding in Power Query.
- Use composite models (import + DirectQuery).
- Reduce visuals, optimize DAX, enable Horizontal Fusion.Hello, this helped mostly to solve it with Snwoflake team. Thank you for help.
Hi Pawel_1990 ,
Thanks Shahid12523 , that’s a really solid breakdown.
My earlier note was more about handling this at the design layer (using reporting views so the masking doesn’t force table scans every time). Your tips on column-level masking, clustering/materialized views, and Power BI folding/composite models add great performance-tuning angles.Between both approaches, Pawel_1990 has options either redesign with views if possible, or optimize Snowflake/Power BI settings to mitigate the impact.
Regards,
Akhil.Hi Pawel_1990 ,
As per my understanding, when you add the role in the connector, every query from Power BI runs under that role. If the role has masking or row access policies, Snowflake ends up scanning the whole table first, then applying the policy, and only afterwards filtering the rows. That’s why you’re now seeing slower queries and big scans in query history.
What usually helps.
- Ask your Snowflake team to create reporting views with the masking already applied. Power BI should connect to those views instead of hitting the raw table with masking policies on top.
- Double-check that your Power Query steps still fold to SQL (right-click > View Native Query). If folding breaks, Power BI pulls a full dataset before applying filters, which is very slow.
- If the datasets are large, consider using Import/Hybrid mode or aggregation tables rather than full DirectQuery for everything.
In short, the slowdown isn’t really a Power BI issue it’s how the role + masking is being enforced in Snowflake. Pushing the masking logic into dedicated views usually restores performance.
Regards,
Akhil.
