Materialized Lake Views: Benchmarked at 489 Million Rows

Most write-ups about Materialized Lake Views make the same promise: pre-compute the heavy queries, point Direct Lake at the result, and reports get faster. This benchmark tests that claim on real data, with a fair before-and-after.

The short version: the cold-start improvement is real and large. Warm queries are mostly unchanged, unless a query computes a lot of datapoints with an expensive measure behind them, in which case the view wins by a wide margin. The most valuable benefits have little to do with report speed: memory and compute.

The data is real NYC taxi trips, replicated up to 489 million rows in a Fabric Lakehouse, with a small star schema around them: zone, date, vendor, payment, rate code. Everything is served through Direct Lake on OneLake, which is the pattern these views are built for.

Each test uses two Direct Lake models over the same data. One reads the raw fact and computes the answer live; the other reads a Materialized Lake View holding the pre-computed, additive building blocks. The same query runs against both.

One rule held everywhere: same work, same answer. Letting the view answer an easier question than the raw fact is the most common way these comparisons go wrong. Before any timing counts, both sides have to return the identical number of rows and match on every total to the penny.

The weighted ratio is the one that matters, because a ratio cannot be pre-summed and stay correct. The view stores its numerator and denominator separately and divides at query time, which keeps every subtotal exact. Timing began only after these matched.

Cold is the first query after a refresh, before any columns are resident. Direct Lake has to transcode Parquet into VertiPaq's in-memory format, build the dictionaries and join indexes, and only then answer. That cost scales with the amount of data, so the view, with far less data to load, wins consistently.

A 6x improvement on this query, and the gap grows as queries get heavier, since the raw model pays the transcoding cost on the full fact every time. Models on shared capacity go cold more often than most people assume, so this is the difference between a report that opens instantly and one that makes the first user of the day wait ten seconds.

The smaller footprint brings two side benefits. The raw fact carried 123 VertiPaq column segments and 595 MB on disk; the view carried 9 segments and tens of kilobytes. That difference is what keeps a model under the capacity's memory limit, and the limit is real: one heavy query against the raw fact was rejected outright by the memory governor, while the view answered the same question without issue. Refresh is also cheaper than expected. With change data feed enabled and an append-only source, the aggregation view refreshed as a Delta MERGE of only the changed rows, not a full rebuild.

Warm is the steady state: the columns are already resident, so the query runs without the transcoding step. For a query of an ordinary size, returning a few thousand grouped rows, the results are quite similar, with a slight but insignificant win for the MLV. Here is the same five-way-join rollup as above, cache-busted so nothing is served from a stored result:

132 milliseconds against 113. The view saved 19ms on a query that was already fast, which is noise. Once the columns are resident, the cost of a small result tracks the number of groups it returns, not the number of rows underneath, so it makes little difference whether the fact holds 30 million rows or 489 million. For everyday report queries, an MLV does not make warm reports faster.

That changes once a visual asks for a lot of datapoints.

A deep matrix, a detailed table, or a scatter plot with tens of thousands of points materializes a large grid, and every cell or point evaluates the measures behind it. If one of those measures is expensive, the engine pays that cost on every query, warm or not.

The test case: a matrix down to Borough, Zone, and Month, broken out by Vendor, with four measures in the body. One of them, a weighted ratio, is a SUMX that iterates the full fact. Power BI generates a much heavier query for this than a clean aggregate, with subtotal rollups at every level, top-N windowing per axis, and a join to stitch the row and column grids together.

The raw matrix needed a 2.885 second warm median, rising past four seconds on the heaviest slice, because the SUMX re-ran over 489 million rows at every rollup cell on every query. The view returned the same 790 rows in a steady 0.193 seconds, because the expensive arithmetic had already happened at refresh time, leaving only a small scan and a divide. Roughly 15x faster on identical work and identical output.

The CPU gap is wider than the wall-clock gap. Wall-clock is the seconds a user waits; CPU is the work the engine's cores actually did, and the thing the capacity gets billed for. From Workspace Monitoring, the raw matrix burned about 6,500 milliseconds of engine CPU per warm query; the view burned 31, roughly 210 times less. The stopwatch gap stays at 15x only because both queries pay the same fixed cost of shipping the result back.

So warm being a wash only holds for small results, where there is nothing expensive to pre-compute. Give a visual enough datapoints with a costly measure behind each one, and the view wins by an order of magnitude, because it moved the per-datapoint work out of every query and into a single refresh.

A query below the view's grain gets nothing from it. The coarse view had no zone column, so a per-zone question went straight back to the 30-million-row fact and took 12.6 seconds. The view saved nothing and still cost storage and refresh to maintain. A view only helps queries at or above its own grain.

Distinct counts don't sum. The coarse view stored a distinct-zone count per group, and summing those across months gave about 9,108 distinct zones for 2017. The real answer is 260, out of 265 TLC zones with two reserved for "unknown" and "outside NYC", so the rolled-up number was 35 times too high. A distinct count has to be stored at the final grain or computed from the fact, for the same reason a model stores SUM and COUNT and derives an average instead of storing the average itself.

This test is also scoped to Direct Lake on a Lakehouse, the serving pattern these views are built for. Fabric Warehouse doesn't support materialized views at all, and there's no supported way today for a Warehouse to read a view's change feed over T-SQL, so a gold layer that lives in a Warehouse needs a different tool.

Materialized Lake Views are often advertised as a report-speed optimization, but they earn their keep on memory and compute.

Memory decides what fits on a capacity at all. The raw 489-million-row model had a heavy query rejected outright by the memory governor; the view's model never gets close, and a smaller model is also evicted less on shared capacity, so it goes cold less often. Compute is the quieter half: the dense matrix burned 210x less CPU on the view, and CPU is what a capacity is sized and billed for. Neither of those is "the report got faster," and either one would justify a view on its own.

For warm speed itself, the rule of thumb is the number of datapoints a visual materializes times the cost of the measure behind each one. Cheap measures over a small result gain nothing; a large grid with a SUMX in the body is the difference between a three-second visual and an instant one. So a view is worth building when an expensive rollup is hit by many reports, the source is mostly append-only, and Direct Lake serves it.

One case remains untested: import-mode refresh. A view that collapses 489 million rows to a few thousand should make an import of that rollup nearly free, since the import reads finished aggregates instead of scanning the fact. A full refresh-then-import pipeline would likely beat importing from the raw fact for append-heavy sources, but that is a separate benchmark, not a finding here.

A note on routing: an MLV is never used automatically. The docs are clear that it is queried like any other Delta table, so a model has to point at it directly. Power BI's old aggregation tables rewrote queries onto the agg whenever the grain allowed, and Databricks does the equivalent today for materialized metric views. The same automatic routing for MLVs is not yet available.