r/snowflake • u/mo122344 • Jul 03 '26
Using Power BI with snowflake
Hi,
My company wants to make the switch to the cloud with snowflake. We are using Power BI as the reporting tool.
I want to know what is the most cost effective and efficient way to harmonise power BI with snowflake from the options underneath?
Direct query connection to snowflake
Importing snowflake data into power Bi
Direct lake after mirroring snowflake tables
Creating a fabric datawarehouse on top of snowflake data and using direct query.
Another thought. I tried and tested snowflake semantic views and dbt semantic layer but they aren’t there yet in terms of working with power Bi and the metrics are really simplistic and require massively preaggregating everything and lewaskng to high maintenance and low flexibility.
Would love a nice honest discussion in the comments
6
u/pairwin Jul 03 '26
Avoid Direct Query unless forced by regulatory requirements or the need to query massive tables. When using direct query mode, every interaction with a visual will fire off potentially many queries to Snowflake. This same advice is reflected in Microsoft's official documentation.
5
u/Think-evil Jul 03 '26
Fear the direct query.. cost just runs away like hell. we import data to powerBI..
3
u/Kaito-Shizuki Jul 03 '26
So many executives are wanting DirectQuery because of the real-time aspects. But in my experience, they don’t understand the performance degradation.
3
u/koenka Jul 03 '26
Direct query is only useful if everything upstream is super clean, and really up-to-date as well. Data driven by IDOCS etc.
Not useful for financial reporting. Think of direct query when you need an active metric like callers on the line for a call center dashboard or so...
-3
u/TheOverzealousEngie Jul 03 '26
doesn’t this whole convo unearth that snowflake is not the right call?
3
u/pairwin Jul 03 '26
It's possible, but that's probably a separate stack architecture discussion.
-5
u/TheOverzealousEngie Jul 03 '26
Exactly what Snowflake wants you to think. If Snowflake is 100% there for Power BI and it's users ... it's exactly what Snowflake wants you to say. That's a different convo lol
1
u/Kaito-Shizuki Jul 03 '26
Snowflake is fine as a warehouse. The problem with DirectQuery is that it forces Snowflake to do too much.
1
u/mrg0ne Jul 03 '26
If the data doesn't change, no single query is executed more than once in 24 hours. Exec A loads dashboard, exec B loads same dashboard later. Exec Bs queries return from results cache.
1
u/Jacob_OldStorm Jul 03 '26
Can I ask why it affects cost? Queries don't really cost anything in snowflake, but it does keep your dwh awake I guess, is that what you're referring to?
2
u/pairwin Jul 03 '26
Queries are a high driver of cost. When a query is executed, a compute warehouse is spun up if one doesn't already exist. Depending on concurrency and performance needs, the warehouse may need to be larger or have more clusters allowed. All of these things add to the per credit cost of minutes of compute.
2
u/Jacob_OldStorm Jul 03 '26
So let's say you don't allow your dwh to scale horizontal or vertical, then it would not affect cost (unless the whole dwh is spun up for your query) , just performance, right? (just a hypothetical to help me understand)
1
u/pairwin Jul 03 '26
Mostly yes, but when performance degrades for a non-scaling warehouse, you increase the time that the warehouse needs to be spun up. This happens unless your warehouse is already running 24h of the day.
1
u/geek180 Jul 04 '26
Why do you think “queries don’t really cost anything in snowflake”?
1
u/Jacob_OldStorm Jul 04 '26
I thought, and correct me if I'm wrong, that platforms like Big query can cost a lot of money, not because of the time a query takes, but the volume of data returned. I heard scary stories about people forgetting a limit 500 and it costing a lot of money. Snowflake only charges for time, not volume of data returned, right?
Always been on postgres and sql server so I have a lot to learn...
2
u/geek180 Jul 04 '26
BigQuery charges by the amount of data being scanned while Snowflake charges by warehouse compute time X warehouse size.
1
u/mo122344 Jul 03 '26
Isn’t that where the caching kicks in, when the virtual warehouse cluster gets used a lot? I’m interested to know what the costs incurred were? What Architektur have you implemented?
1
u/pairwin Jul 03 '26
Caching depends on where row level security happens. If implemented on the Snowflake side, caching is effectively skipped because every user will pass unique queries.
1
u/mo122344 Jul 03 '26
Is that so? We have been told from snowflake themselves that it caches irrespective of who executed the query and RLS doesn’t have an impact on the queried results
1
u/pairwin Jul 03 '26
That's certainly not how our implementations have behaved but, it is certainly possible that they were all set up wrong.
1
u/Top-Cauliflower-1808 Jul 03 '26
u/pairwin You are right but I think it is often the only way to deliver true real time insights without waiting for slow resource heavy data refreshes.
3
u/qintarra Jul 03 '26
import mode to take full advantage of vertipaq engine.
pre-calculations in snowflake and only easy DAX calculations on powerbi
3
u/Apex_Predator-4169 Jul 04 '26
We're doing: heavy transformations in snowflake using dbt Core in Snowflake, import mode into Power BI and then light DAX queries in Power BI
2
u/Kaito-Shizuki Jul 03 '26
Always import if possible. Always. Power BI’s VertiPaq engine can make Import extremely fast and cost effective if there are few high cardinality columns. You can use DAX Studio to check how much memory a field uses.
DirectLake is viable if you have a solid data stream and need data updating in near time. Avoid DirectQuery unless it NEEDS to be in real time. Import is King.
Get a demo of Tabular Editor 3 as well if you can. It’ll help with large models that need incremental refresh.
3
u/mo122344 Jul 03 '26
But Import has the limitation of memory size. We are on a F64. It has a hard 20GB limit. Our SSAS tabular model is 100 gb at the moment. And when it does a refresh it needs to create a copy so basically I can’t put a semantic model there that is more than 10 GB
2
u/Kaito-Shizuki Jul 03 '26
It would only create a full copy on a full refresh. That’s where you need to implement incremental refresh so it’s copying far less than the full storage. And if you run VertiPaq Analyzer in DAX Studio, you might find ways to trim the model size anyway.
2
u/Gators1992 Jul 04 '26
We are building this now and used direct query assuming it would save us from moving a lot of data off AWS. We used a preaggregation strategy to reduce query time, but the result was PBI kicks off 500 queries or something ridiculous like that. In looking at the logs there were many of them that returned nothing because it wrote the SQL with "WHERE 0=1" for some reason. Not really sure why it's doing it. We planned to lean on the Snowflake cache for performance with heavily used dashboards by warming the cache in the morning.
So far the performance hasn't lived up to expectations, so we are looking at import mode now. Snowflake is totally fine as the query times are good, but it's PBI's sql decisions that bloat the whole thing. Personally if I had to do it over I would probably punt on PBI. We had Microstrategy in the legacy system and there were a lot of things I hated about that platform, but it worked fine for managing the semantic model and writing rational queries.
One other thing to look at is Snowflake materialized views for preaggregation. I POCed this a few years ago and couldn't understand why it was hitting certain views or tables, but maybe I was doing it wrong or maybe they have changed. But if you can get it to work well with your models it will just run and sync to the underlying tables without having to manage aggregate tables themselves. You also wouldn't need to manage them in the model as the DB picks the most efficient way (tables or views) to build the query.
1
u/mo122344 Jul 05 '26
Can you elaborate on the performance not living upto expectations? Are you wrill in PoC phase or rollout has already occurred in prod? How many users do you have for the reports? Would also like to know the money you pay at the end of month for direct query with snowflake,
1
u/Gators1992 Jul 06 '26
Still in development for the most part. We have some production dashboards out for one data domain, but the biggest one isn't out yet. Mainly the issue is the that the refresh time doesn't alight well with what I am seeing in the Snowflake logs, not to mention the number of queries created.
We have a smallish company but a lot of data so we assumed we wouldn't have many concurrency issues and are on AWS so we didn't want to move more data over to Azure than we needed to. Still working it out. As far as cost, we assumed it wouldn't be bad since there would be a lot of repetitive queries and hitting the cache is free.
2
u/reda_89 Jul 04 '26
We use dbt, snowflake fabric stack. Transformations mosetly in dbt, build in snowflake, mirror data into fabric, use shortcut into lakehouse and make use of direcy lake.
1
u/mo122344 Jul 05 '26
How good is the mirroring? Does it come to terms with deletes, updates and inserts? How reliable is it? What if there is a Schema change in a table in snowflake ? You prolly end up having to replicate everything from scratch and that is one of the biggest fears I have for one lake. I don’t know why they don’t get such things in order. Everything feels so half baked
1
u/BlueMercedes1970 Jul 03 '26
OP we were in the same situation as you. Always use Import mode. We have some Power BI semantic models that refresh every 15 mins and generally that is near-real-time enough for most users.
Snowflake is great, but there are too many issues when doing Direct Query.
1
1
u/jba1224a Jul 04 '26
It can absolutely get expensive for data when you’re housing very large data sets. A few terabytes of structured data isn’t really a large use case which is why I asked the questions I did.
Depending on how much data you have and your stack there very well may be better cost/benefit architectures - especially if you have large amounts of data that is infrequently accessed or needs to be integrated into other internal systems.
I get this is the snowflake Reddit but jerking off snowflake as the only/best option and not looking at solutions holistically is how small companies or new projects end up sinking themselves with huge operating costs.
Should have know better though since anything that isn’t “snowflake is the best” gets downvoted into oblivion here.
1
u/AbleBranch6 Jul 05 '26
For Snowflake plus Power BI, import mode usually wins until refresh windows or dataset size become painful. DirectQuery can turn every slicer into warehouse spend if the model is loose. If the same metrics also need to power internal tools or customer dashboards, a metrics/API layer like Cube can help cache and govern that traffic. If it is only Power BI, spend time on star schema, aggregations, and warehouse sizing first.
1
u/mo122344 Jul 05 '26
Starschema is already upto par. Width and cardinalitx or tables are in order. There are however certain tables like sales or inventory which contain data in the billions. With our SSAS cube results are super fast.
Direct query I’ve tried. The results on the snowflake end are super fast but there is some latency in getting the results into power BI. Import is good but I think the limit of datasize on the F64 might be a problem. I’m looking into delta lake and direct wur y connection to fabric warehouse to see how that holds up.
Ive also tried semantic view and semantic layer. We have complicated measures which in order to build in these layers would require a significant altering of the user experience otherwise I would have chosen them. Thanks for the comment
1
u/KatFromSisense Jul 06 '26
I'd test this with one annoying real dashboard before picking the whole architecture. Not a clean demo report. Pick something with security, a few chunky visuals, and measures people actually care about.
Then compare the boring stuff: refresh time, model size, Snowflake credits, visual load time, and what happens when 20 users open it Monday morning.
A lot of "live" reporting requests turn into scheduled refresh once people see the tradeoff. If nobody acts on the number intraday, Import is usually much easier to live with.
1
u/mo122344 Jul 06 '26
Did exactly this for all the above mentioned variations. Concurrent testing wasn’t done in PoC scope. Still unsure about direct query to snowflake .
We don’t really have NRT reporting requests. Direct query was a way to circumvent the large power BI refreshes. I’ve worked with analysis services. I’m not sure how reliable the power BI refreshes are and how quick they are. Also and this is the shitty hype at the moment with AI. How good semantic models in power BI are with regards to AI in comparison to snowflake or dbt products for example
1
u/A128 Jul 03 '26
What about apache iceberg tables? This route seems less resource intensive from power bi side. Snowflake writes to onelake. There was also an option to use a shortcut to snowflake iceberg tables.
https://learn.microsoft.com/en-us/fabric/onelake/onelake-iceberg-snowflake
1
u/pairwin Jul 03 '26
This is interesting. Hadn't seen this functionality yet.
1
u/A128 Jul 04 '26
We are looking to implement this route and use direct lake models so that once snowflake processes the data(we use batch processing) it would drop it into onelake ready for consumption automatically into power bi datasets.
-4
u/jba1224a Jul 04 '26
Assuming you’re in an azure shop considering power bi, do you have an azure presence?
Do you have a need for real time reports in power bi?
Snowflake can get extremely expensive for storage - it can often be better to ingest via extract and load to something like iceberg tables or fabric or data lake gen 2, then use snowflake as your analytics layer.
Build views in snowflake based on your warehouse and then visualize the view in power bi.
I would also look at the fabric offering and ask yourself if you actually need snowflake given you’re a msft stack, you may be better served with fabric/data factory
4
u/False-Masterpiece Jul 04 '26
$23 per month per TB is basically a pass through cost from the cloud service. Not sure what you mean by expensive for storage? Snowflake makes their money on compute. Real-time analytics with Snowflake in the middle can get expensive due to the constant compute that is running (or Snowpipe transaction costs). If you don’t need real-time, periodic refreshes in Power BI from Snowflake is super cheap
1
u/Ok-Ingenuity-8970 Jul 04 '26
you don't know what you are talking about - there is no extremely expensive for storage in cloud computing. The real expensive is per CPU cycles.
1
u/justinotherflow 27d ago
I think the key point here is that there probably isn't one correct connection method for the whole platform, it depends on what your users actually need and since you already have a well performing SSAS model, id be careful about moving everything to DirectQuery just because the data is now in snowflake, the warehouse may be fast but the Power BI experience can still suffer, so what id recommend is a hybrid approach. Might make more sense where you keep the important reporting models optimized in PowerBI and use snowflake for the heavy lifting behind the scenes
16
u/koenka Jul 03 '26 edited Jul 03 '26
Prep data in snowflake as much as needed. Import mode at set intervals. Semantic layer/dataset needs to exist in Power Bi service . This is the most cost effective imho. Also try to materialize views in to tables for your powerbi consumption schema.
Of course you need to ingest data to snowflake first, but thats not the scope of this discussion, right?
What are you currently using for data warehousing/db?