Data Analytics
  • All of Microsoft
  • Python
  • My Role
    • All in My Role
    • Data Analytics
  • Focus
    • All in Focus
    • Microsoft Ignite 2026
    • Microsoft Fabric
    • What's Hot
    • M365 Release
  • Security & Identity
Timespan
explore our new search
Power BI on Databricks: choose a connection type
Power BI

Power BI on Databricks: choose a connection type

by HubSite 365 about Reza Rad (RADACAD) [MVP]

Founder | CEO @ RADACAD | Coach | Power BI Consultant | Author | Speaker | Regional Director | MVP

Power BI teams can compare DirectQuery, composite models and Direct Lake to match Databricks data access with report performance needs.

Key insights

  • Import mode was excluded from the benchmark comparison; the guests describe it as the fastest option.
  • Composite models performed well on queries served by imported aggregation tables, but not on a distinct-count query that bypassed them.
  • Direct Lake on OneLake assumes data has been brought into OneLake and optimized with V-Order.
  • Mirrored Unity Catalog tables keep the data in Databricks while Fabric accesses it through synced metadata.
  • The benchmark used Azure Databricks in the same region as Power BI and kept Fabric capacity at F128.

Power BI offers several ways to query Databricks data, and the connection pattern affects where data resides and how report queries are served. This choice matters to Power BI developers, data architects, Databricks users and Fabric administrators planning interactive reports. Reza Rad (RADACAD) [MVP] hosts a discussion with Eiki Sui and Liping Huang comparing these approaches and their measured query performance.

What's new

The key point is that “connect Power BI to Databricks” does not describe a single architecture. The options discussed include DirectQuery to Databricks, a composite model with imported aggregations, Direct Lake over data in OneLake, and Direct Lake over mirrored Azure Databricks Unity Catalog tables. Import mode is another option, but it was not included as a separate benchmark pattern.

These choices differ in where the data lives and which engine handles a report interaction. With DirectQuery, Power BI sends queries to Databricks. A composite model can route some queries to imported aggregation tables and others to DirectQuery tables. Direct Lake over OneLake reads data brought into OneLake, while the mirrored Unity Catalog pattern leaves the underlying data in Databricks.

The comparison focused on Power BI query performance: the experience when users interact with reports. It did not include data engineering work, copying time or refresh time. That distinction matters when using benchmark results to make an architecture decision; query response is only one part of operating a solution.

Who it is for and what you need

The discussion is aimed at teams that use Databricks as a data platform and Power BI for reporting, including BI consultants and data architects evaluating a connection pattern. The performance results were based on Azure Databricks located in the same region as the Power BI tenant. The presenters note that cross-region setups can add network delay, and use of a different cloud may also involve egress costs.

For the tested DirectQuery and composite-model patterns, Databricks compute was scaled according to the fact-table size: Medium for 26 million rows, Large for 260 million rows and Extra Large for 2.6 billion rows. Fabric capacity remained F128 throughout. The results therefore describe that test setup, not a universal comparison of every capacity or deployment.

Before using the results, establish which data location and query behavior your reports require. If data must remain in Databricks, compare DirectQuery with mirrored Unity Catalog tables. If data can be copied into OneLake, Direct Lake is another pattern to evaluate. The comparison does not include a cost study, so plan to assess compute usage and costs for both platforms separately.

How it works

Each pattern routes work differently, so the useful question is not simply which one is fastest. Consider the query types in your reports, the data volume, where data should reside and the compute available on both sides. The benchmark also distinguishes cold-cache tests from warm or hot cache scenarios, which can produce different outcomes.

  1. DirectQuery: Connect Power BI to a Databricks SQL warehouse. Report interactions query Databricks, so the SQL warehouse performs the source-side work.
  2. Composite model: Keep detailed fact data in DirectQuery, use dimension tables in dual mode, and add imported aggregation tables. Queries resolved by an aggregation can use the imported data; queries that miss the aggregation still reach Databricks.
  3. Direct Lake on OneLake: Bring the data into OneLake, with V-Order applied in the tested pattern, then build the semantic model using Direct Lake.
  4. Direct Lake on mirrored Unity Catalog tables: Mirror the Unity Catalog metadata and catalog structure in Fabric. The underlying data stays in Databricks rather than being copied into OneLake.

In the test, Direct Lake on OneLake was generally the strongest performer in warm and hot cache scenarios, except where composite-model queries hit imported aggregations. Those aggregation queries effectively use Import, which the guests describe as faster than Direct Lake. The comparison also found DirectQuery worked well in cold-cache and load-test scenarios, with Databricks compute scaled to the tested data size.

Composite-model results depended on routing. Queries that hit imported aggregations performed well, while the distinct-count query that did not use an aggregation performed similarly to DirectQuery. The mirrored pattern was comparable to other approaches on the smaller dataset, but needed more memory as data volume increased and failed the largest load test at F128.

Limitations and alternatives

Do not treat the benchmark as a complete cost or operational comparison. Its scope was Power BI query performance, not refresh, data movement or engineering effort. The Databricks warehouse size changed with the data volume, while Fabric stayed at F128, so the test did not hold compute constant across both platforms.

The mirrored Unity Catalog result is particularly tied to the tested capacity and workload. The guests say it struggled at larger volumes and failed the 2.6-billion-row load test because of memory limits; they suggest a larger Fabric capacity might change that outcome, but the discussion does not provide a measured result for one. Direct Lake on OneLake, in turn, requires a data-copy step in the tested design.

Import remains an alternative when fast report interaction is the priority and importing the data is suitable. It was not benchmarked as a separate pattern, although imported aggregation queries in the composite model provide a point of comparison. Validate representative report queries, cache conditions, data volumes and compute sizing before choosing, and evaluate billing independently because it was outside the comparison's scope.

Sources

Frequently asked questions

Which Power BI connection type is fastest for Databricks?

The guests describe Import mode as the fastest, though it was not benchmarked as a separate pattern. Direct Lake on OneLake was generally the strongest tested option in warm and hot cache scenarios, except for composite-model queries served by imported aggregations.

Does Direct Lake on mirrored Unity Catalog tables copy data into OneLake?

No. In the pattern discussed, the data stays in Databricks while Fabric accesses it through mirrored metadata and catalog structure.

How did the mirrored Unity Catalog pattern perform on large datasets?

It was comparable to other patterns on the smaller dataset, but performance worsened as volume increased. At 2.6 billion fact-table rows, it failed the load test on the tested F128 Fabric capacity because of memory limits.

What was included in the Power BI and Databricks benchmark?

It compared Power BI query performance, not refresh time, data movement or data engineering effort. The tests used Azure Databricks in the same region as Power BI and kept Fabric capacity at F128.