Direct Lake or Databricks SQL? How to Benchmark Your Own Workload

"Should Power BI read our lakehouse through Direct Lake or through a Databricks SQL warehouse?" comes up in almost every estate that runs both Databricks and Microsoft Fabric. Vendor benchmarks will not answer it for you, because the answer depends on your tables, your report design and your users' habits. The good news is that a fair comparison takes a few days, not a quarter.

This is the method we use.

How the two paths work

• Direct Lake loads columns from Delta Parquet files in OneLake straight into the Power BI engine's memory on demand. There is no import refresh and no SQL round trip per visual. When data does not fit the capacity's guardrails, the model can fall back to DirectQuery through the SQL endpoint.
• Databricks SQL answers each visual's query on a SQL warehouse (serverless or classic) over the same Delta tables, through DirectQuery from Power BI.

The same Delta table can feed both. A OneLake shortcut to the Databricks-owned gold table means there is no copy to keep in sync.

Set up a fair test

1. Same data. Point both paths at the same gold table through a shortcut. Do not compare a tuned copy against an untuned one.
2. Same report. Build one Power BI report and create two versions: one on a Direct Lake model, one on a DirectQuery model over the SQL warehouse. Same visuals, same filters.
3. Comparable spend. Choose a Fabric capacity SKU and a warehouse size that cost roughly the same per hour for your usage pattern. Write the assumption down.
4. Prepared tables. Optimise both sides the way you would in production: liquid clustering or Z-ordering in Databricks, V-Order where your Fabric setup applies it, sensible file sizes, and no tiny files.

Decide what to measure

Measure at least four things:

• Cold latency: the first query after the model or warehouse has been idle. Users feel this every morning.
• Warm latency: repeated interactions once columns are in memory or results are cached.
• Concurrency: the same report opened by many users at once. Use a load-testing script or Power BI's performance tools.
• Cost per period: capacity units consumed in Fabric, DBUs consumed by the warehouse, over a realistic day.

Record median and 95th percentile latency, not averages. One slow outlier ruins a morning meeting.

Use two query sets

Dashboards and analysts behave differently, so test both:

• Dashboard set: the visuals in your real reports. Aggregates by date, region and product; a few slicers; predictable shapes.
• Ad-hoc set: questions analysts actually ask. High-cardinality joins, distinct counts over customer IDs, long date ranges, filters nobody planned for.

What to expect, and why

Results vary, but the patterns are consistent with how the engines work:

• Direct Lake tends to shine on predictable dashboards once the columns a report uses are resident in memory. There is no per-visual SQL round trip.
• Cold starts matter for Direct Lake. The first touch of a column has to be loaded. Pre-warming the model after data refreshes helps.
• SQL warehouses tend to handle unpredictable queries better, especially large joins and wide scans, because they can scale compute per query and cache results.
• Fallback to DirectQuery can surprise you. If a Direct Lake model exceeds its capacity guardrails, performance changes character. Check whether fallback happened during your test.

Turn the results into a rule

Most estates we see end up with a boundary like this:

• Curated BI consumption goes through Direct Lake, on gold tables mirrored by shortcut.
• Analyst SQL, data science and anything with unpredictable shape stays on the warehouse.
• Only gold is mirrored. Bronze and silver stay in the engineering platform, so governance has one boundary to police.

Write the boundary down as an architecture decision record, with the measurements that justified it, so the decision survives the next reorganisation.

A short checklist

• Same table, shared by shortcut, not copied.
• Same report, two models.
• Comparable hourly spend.
• Cold and warm latency, median and p95.
• Dashboard and ad-hoc query sets.
• Fallback checked.
• Decision recorded.

If you would like help running this on your own estate, it fits neatly into a one-week fixed-scope review.