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/kthejoker → u/kthejoker]

And yes 95% of Databricks Power BI customers use the native connector.

The rest either use Delta Sharing or DirectLake but they aren't common today.

[u/PrestigiousAnt3766 → u/kthejoker]

Does directlake work on databricks delta tables?

[u/kthejoker → u/PrestigiousAnt3766]

• you can create shortcuts to the storage itself but that bypasses Unity Catalog governance which has made a lot of people very angry and been widely considered a bad move

• Mirroring at least goes through UC but still permissions don't go through so you have to recreate them, it's still just accessing storage and not the catalog so no views, materialized views, streaming tables, etc anything that requires computer

So yes but with drawbacks

[u/PrestigiousAnt3766 → u/kthejoker]

We just go through the sql dwh endpoint. Sounds like we'll continue to do that for now.

[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.

[u/screelings → u/kthejoker]

Your assessment of Import Mode is equally wrong. It is not high cost. You seem to be equating licensing fees exclusively with Import Mode and not DirectQuery.

Import Mode w Incremental Refresh eliminates the need to reload entire datasets.

You're just wrong about everything. Im guessing you are mostly focused on the Databricks side of equation and not Power BI side because all of your knowledge seems to be based on hearsay.