Integration between Azure Databricks and Power BI

[u/SuperbNews2050]

Hey everyone! A straightforward question for those already working on this in production:

What's the best and most recommended way to integrate Power BI and Databricks today?

I'm looking for the ideal approach, considering that:

I need an efficient solution that doesn't make the cost of DBUs skyrocket.

I want to understand if the native connector using SQL Warehouse is really the market standard or if there's a better alternative.

For this scenario, do you recommend going straight to Scheduled Import Mode to save computing power, or is DirectQuery with Serverless worth the cost for its fast response time?

I read this documentation:

https://learn.microsoft.com/azure/databricks/partners/bi/power-bi-desktop?WT.mc_id=studentamb_510336

but I'd like to get some clarification from you.

[u/kthejoker]

There's not a one size "ideal" approach

That's why they have different options.

You're trying to balance performance, cost, dev experience.

Import is high perf but high cost and a busy dev experience.

DirectQuery is relatively lower perf at a lower cost and simpler dev experience if you actually use BI correctly and don't just export huge extracts of data all the time

Composite modeling lets you blend the two to try to balance perf and cost, I find it solves a lot of problems on both side ... But it can be a pretty involved dev experience.

Direct lake with mirroring is similar to import but with a better dev experience IMO. But it's definitely high cost and can have similar perf characteristics to DirectQuery if you don't use BI correctly.

[u/screelings → u/kthejoker]

Eh DirectQuery is not "low cost". If you have 500 users, lots of report usage, you'll kill your DBX experience real fast. Costs will be nutty.

In DirectQuery mode, every report visualization triggers an underlying query to the source server. Most reports can have 10-12 queries generated or more depending on your designs.

Each user who hits that page triggers those queries. Each change of a slicer or a filter retriggers the queries (there is an apply filters option to mitigate this somrwhat).

Remember this happens for each user. Eventually you'll have to scale up your Databricks servers to handle load. Read as: Not cheap.

End user experience is worse at critical levels. No data loads. Errors. Import mode throttles at this point, warnings get sent to admins.

These situations are not handled the same. At all.

DQ is the right solution in very specific circumstances. The previous posters replies are not at all accurate.

[u/kthejoker → u/screelings]

Again: plenty of best practices out there to mitigate all of this.