DEV Community

Cover image for Fabric Engines on 1B Rows: Pick by Query Shape
Sai Prudhvi Neelakantam
Sai Prudhvi Neelakantam

Posted on Originally published at geekindata.com AI-assisted

Fabric Engines on 1B Rows: Pick by Query Shape

I used AI tools to help draft and edit this post. I checked the measurements and the wording, and the original lives at geekindata.com. The diagrams are on that page.

Key takeaways

  • The SQL analytics endpoint and Spark answered all 7 query shapes on every run (161 of 161 each). The endpoint's worst cold run was 37.8 s. Spark first needs a session start of about three minutes.
  • Direct Lake was the fastest warm on most shapes, at about 0.6 to 0.8 s. But its first successful load took 40 to 170 s, and it failed every cold run on 4 of 7 shapes.
  • Eventhouse won the point lookup (0.011 s engine time) and was the slowest on joins (216 to 248 s).
  • Direct Lake used more than 10 times the CU of the SQL analytics endpoint for the same 7 queries.
  • Measured on a 64-CU Fabric trial with synthetic TPC-H-derived data. Not a statement about paid F64 or production.

One Delta table with a billion rows, four engines that can query it, and one question: which engine should the dashboard, the analyst and the nightly job use? This is the third post in my Fabric trial series, and I ran the same seven queries on Spark, the SQL analytics endpoint, a Direct Lake semantic model and an Eventhouse, cold and warm, to find out.

{/* truncate */}

Which Fabric engine should you use for a billion-row query?

On about one billion rows on a 64-CU Fabric trial, the SQL analytics endpoint was the safe default: it answered all seven query shapes on every run, in 0.2 to 8.5 s engine time warm and at most 37.8 s cold at the caller. Direct Lake was the fastest warm engine for aggregates at about 0.6 to 0.8 s, but needed 40 to 170 s for its first successful load and failed every cold run on four of the seven shapes. Eventhouse was fastest for a point lookup (0.011 s engine time) and slowest for joins. Pick by query shape and how warm the engine will be, not by one overall winner.

Results: warm median, seconds

Query shape Spark SQL endpoint Direct Lake Eventhouse
q1 point lookup 0.874 1.046 0.690 0.329
q2 one month, filtered aggregate 4.159 1.521 0.633 4.820
q3 group by ship mode 17.544 1.312 0.677 59.151
q4 two joins 47.079 9.489 0.674 not run
q5 one join 39.564 5.912 0.657 215.505
q6a top 100 rows 15.093 1.988 failed every run not measured
q6b top 5 brands per ship mode 48.730 7.112 0.796 247.757

Caller time, median of the warm runs that passed. Direct Lake q2 and q4 leave out runs delayed by throttling. Eventhouse has no q4 (no copy of orders or customer), and q6a was not measured because of a bug in my harness.

A grid of seven query shapes by four engines. The SQL analytics endpoint answers every cell, 1 to 10 seconds warm and at most 38 seconds cold. Direct Lake is the fastest warm cell in most rows but fails cold on the point lookup, the two-join query and the join plus window, and fails every run of the row-level top 100. Eventhouse is fastest on the point lookup and slowest on joins. Spark sits in the middle with a session start of about three minutes.

Figure 1. Once warm, Direct Lake is fastest on most shapes. Cold, it is the engine most likely to fail, and the SQL analytics endpoint and Spark have no red cells.

Cold is where the picture changes:

  • Direct Lake has to load columns from OneLake first. That took 60 s for the one-table aggregates and 151 s for the join to part. On four shapes the cold load failed after two to four minutes.
  • The row-level top 100 (q6a) failed 6 of 6 runs on Direct Lake, cold and warm, at the 10 GB per-query memory limit (Power BI capacity SKUs). The SQL analytics endpoint answered it in about 1 s of engine time.
  • Spark pays at session start: 163 to 301 s for a new session on a custom pool, then a median of 40 to 87 s for the first query.
  • Eventhouse failed 8 runs on the joins, on low memory or by returning 1 row with HTTP 200. Check the row count, not the status code.

How to pick

Four questions to pick a Fabric engine for a query: the shape of the query, how warm the engine is, how the engine bills for it, and the hard ceiling that turns slow into failed.

Figure 2. Speed is only one of four questions. In this run the state, the meter and the ceiling decided as many cells as the stopwatch did.

If the query is Pick Why
Unpredictable, or the engine won't be warm SQL analytics endpoint No failed runs, worst cold run 37.8 s
A repeated aggregate on a model that stays warm Direct Lake About 0.6 to 0.8 s warm
A row-level top N over the whole fact table Not Direct Lake Every run hit the 10 GB per-query limit
A lookup on one table Eventhouse 0.011 s engine time
A join between big tables Not Eventhouse 216 to 248 s, and runs failed
A nightly job Spark is fine A session start of about three minutes doesn't matter once a night

What it cost

Four meters side by side, as estimated block totals. The SQL analytics endpoint used about 21 thousand CU-s for the seven queries, with q2 and q3 not separated, Spark about 186 thousand, Eventhouse about 90 thousand including idle uptime, and Direct Lake queries about 351 thousand, much of it on cold loads, some of which failed. The engine that was fastest warm on most shapes was the most expensive.

Figure 3. Seconds and CU-s rank the engines differently: the engine that was fastest warm on most shapes used the most capacity.

Over the same seven queries, Direct Lake used more than 10 times the CU of the SQL analytics endpoint (about 13 times, 17 counting failed and timed-out runs). Much of that went on cold loads, including the ones that failed: the q6a block, six runs that all failed and returned nothing, still used 67,871 CU-s and pushed the trial to 182%.

The meter matters too. SQL and Spark work is background and is smoothed over 24 hours. Direct Lake queries from an app are interactive and smoothed over as little as 5 minutes, so a few heavy cold loads can throttle the capacity in minutes (throttling). That happened here twice. For a dashboard that stays warm, you pay the cold load once. For queries that arrive after the model has gone cold, you pay it every time.

What I tested

  • About one billion rows: TPC-H-derived tables at about scale factor 170, generated in Spark with a fixed seed. Synthetic data, not the official dbgen tool.
  • A 64-CU Fabric trial. Spark, the SQL analytics endpoint and Direct Lake read the same Delta tables. Eventhouse got its own copy of the fact table and part.
  • Seven query shapes, the same intent in each engine's own language: point lookup, filtered aggregate, group by, one join, two joins, row-level top 100, and a join plus window.
  • Each engine and query ran 3 cold runs, then 20 warm runs, one block at a time in a shuffled order. One night (30 to 31 October 2026), one capacity, one dataset.

Limits

  • A trial is not a paid F64. It has the same smoothing and Power BI limits, but no autoscale, no overage billing and no Spark burst.
  • Cold is not the same everywhere. SQL endpoint and Eventhouse caches can't be flushed, so cold there means first run. Spark cold runs leave out the session start.
  • Times are what the caller waited, so they include each client's network hop.
  • CU-s figures are my estimates from the capacity's utilization events, as block totals. They are not Capacity Metrics app figures.

Method notes

  • A run counts as failed if it returned an error or the wrong number of rows, even with HTTP 200. Failed runs are counted, never averaged in.
  • Direct Lake runs delayed by throttling (a flat 20.7 s each) are left out of the warm medians and were not re-run. Direct Lake q4 has 3 clean warm runs.
  • By engine time the SQL analytics endpoint took 0.175 to 0.462 s on q1 to q3, so read those against Direct Lake as a tie.
  • The SQL analytics endpoint CU total leaves out q2 and q3, whose billing step I could not separate.
  • Spark ran on a fixed pool of 8 Medium nodes, 64 of the trial's 128 vCores, the same for every run.

Related

If you only change one habit, time your query cold as well as warm, on the engine and client your users will actually hit, and count the runs that fail.

Top comments (0)