~12 min read

Materialized Lake Views

Pre-Computed Performance in Microsoft Fabric

Eliminate expensive recalculations, accelerate queries, and simplify your semantic models

Compare MLVs, Views & Manual Aggregation Tables

Scroll to explore

The Performance Bottleneck

When every report recalculates the same expensive queries

The Scenario

Multiple reports and semantic models query the same raw tables, each recalculating identical expensive operations every single time.

Large JOINs

Multi-table joins across millions of rows

GROUP BY Aggs

Heavy aggregations with multiple grouping columns

Window Calcs

Running totals, rankings, and moving averages

DISTINCT Counts

Unique value calculations on high-cardinality data

Report A

Report B

Report C

Notebook

Same Expensive Query × Every Consumer

Each report independently recalculates identical JOINs, aggregations, and transformations from raw Delta tables

The Cost

CU Spikes

Redundant compute burns through Fabric capacity on identical operations

Slow Report Loads

Users wait while complex queries execute from scratch on every interaction

DirectQuery Fallback

Oversized tables trigger fallback from Direct Lake to slower DirectQuery mode

The core issue: Without pre-computation, every consumer pays the full compute cost every time, even when the underlying data hasn't changed.

What Are Materialized Lake Views?

Pre-computed query results persisted as Delta tables

Core Concept

Regular View

Virtual: no physical storage

Re-executes query on every access

Full compute cost each time

Always up-to-date (live)

Materialized Lake View

Persisted as Delta tables

Pre-computed: reads stored results

Minimal compute on query

Auto-maintained by Fabric

Definition

A Materialized Lake View is a pre-computed query result, defined via Spark SQL, that is automatically persisted as a Delta table in OneLake. Unlike regular views that re-execute on every query, MLVs store the computed results and are automatically maintained by the Fabric engine when source data changes.

Key Properties

Pre-Computed

Query runs at creation and on each refresh; results stored for instant reads

Delta-Stored

Persisted as Delta/Parquet files in OneLake, queryable by any engine

Auto-Maintained

Scheduled refresh auto-selects skip, incremental, or full based on source changes

Spark SQL Defined

Authored in a Lakehouse notebook using CREATE MATERIALIZED LAKE VIEW

How Materialized Lake Views Work

From Spark SQL definition to automatic maintenance

Creation

You write a Spark SQL query defining what the MLV should contain. The Fabric engine evaluates it and writes the results as Delta/Parquet files.

Write Spark SQL

CREATE MATERIALIZED LAKE VIEW

Engine Evaluates

JOINs, aggregations, filters

Delta Table

Stored in OneLake

Storage & Access

The MLV's Delta table lives in OneLake alongside your source tables. Any Fabric engine can read it.

Source Tables

Raw data in OneLake

MLV Delta Table

Pre-computed results

Consumers

Reports, notebooks, queries

Automatic Maintenance

On each scheduled refresh, Fabric's optimal refresh engine compares delta logs and picks skip / incremental / full automatically – no manual orchestration needed. Refresh frequency is set in the Lakehouse → Materialized lake views → Schedules pane.

Source Changes

New/updated rows

Fabric Detects

Delta log comparison

MLV Updated

Incremental refresh

Prerequisite: Change Data Feed and append-only source data. Incremental refresh requires two conditions. First, delta.enableChangeDataFeed=true must be set on every source table referenced by the MLV; without it, optimal refresh can only choose between skip and full rebuild. Second, the source must be append-only during the refresh cycle; any delete or update triggers a full rebuild regardless of CDF status.

Point Your Queries at the MLV

MLVs appear like any other Delta table. The engine does not automatically rewrite queries to use a matching MLV. Point your reports or semantic model at the MLV by selecting it explicitly, in the SQL analytics endpoint or the semantic model designer. Once the model points at the view, every downstream query reads pre-computed results without touching the raw tables.

Spark SQL Syntax

Creating and managing Materialized Lake Views

CREATE

Define the MLV with a SELECT statement. The engine evaluates the query and persists the result set as a Delta table. Optional data quality constraints and partitioning are supported.

-- CREATE OR REPLACE overwrites any existing definition; use CREATE ... IF NOT EXISTS instead for re-runnable deployment scripts
CREATE OR REPLACE MATERIALIZED LAKE VIEW silver.monthly_sales_summary
(CONSTRAINT valid_sales CHECK (TotalSales >= 0) ON MISMATCH DROP)
PARTITIONED BY (OrderYear)
COMMENT 'Monthly sales rollup by product category'
AS
SELECT
    p.ProductCategory,
    YEAR(s.OrderDate)   AS OrderYear,
    MONTH(s.OrderDate)  AS OrderMonth,
    SUM(s.SalesAmount)  AS TotalSales,
    COUNT(DISTINCT s.CustomerID) AS UniqueCustomers,
    AVG(s.UnitPrice)   AS AvgPrice
FROM bronze.fact_sales s
INNER JOIN bronze.dim_product p
    ON s.ProductID = p.ProductID
GROUP BY
    p.ProductCategory,
    YEAR(s.OrderDate),
    MONTH(s.OrderDate);
          

Run this in a Lakehouse notebook with the Spark SQL editor. Names are case-insensitive and stored as lowercase. Sources must be Lakehouse tables (Delta) or other MLVs – to use a Fabric Data Warehouse table as a source, create a OneLake shortcut to it in the Lakehouse first.

ALTER & DROP

Rename an existing MLV:

-- ALTER only supports RENAME
ALTER MATERIALIZED LAKE VIEW
  monthly_sales_summary
RENAME TO sales_by_month;
              

Remove an MLV entirely:

-- Drop the MLV and its Delta table
DROP MATERIALIZED LAKE VIEW
  monthly_sales_summary;
              

Use CREATE OR REPLACE for schema changes. Re-running the CREATE statement with OR REPLACE updates the definition in place. ALTER itself only supports renaming. Renaming or dropping an MLV affects downstream lineage and scheduled refreshes, so update references in dependent MLVs.

Metadata Queries

List the MLVs in a schema, or inspect the SELECT that defines a specific one.

-- List all materialized lake views in a schema
SHOW MATERIALIZED LAKE VIEWS IN silver;

-- Retrieve the CREATE statement for one MLV
SHOW CREATE MATERIALIZED LAKE VIEW monthly_sales_summary;
          

namespace	viewName	isTemporary
silver	monthly_sales_summary	false
silver	customer_metrics	false

When to Use MLVs

Ideal patterns and when to avoid them

Ideal Patterns

Heavy Aggregations

SUM, AVG, COUNT across millions of rows with GROUP BY, pre-compute once, read many times

Complex Multi-Table JOINs

Flatten fact-dimension joins into a single pre-joined table that's instantly queryable

Frequently-Queried Subsets

Filtered subsets (e.g., last 2 years, top regions) that many reports reference repeatedly

When NOT to Use

Sub-Minute Volatile Data

If data changes every few seconds, the MLV refresh can't keep up, use DirectQuery instead

Small Simple Tables

Tables already small enough to query directly, the overhead of maintaining an MLV isn't worth it

Row-Level Security Needs

MLVs don't natively support RLS, per-user filtering still needs to happen downstream

Decision Framework

Is the query expensive?
(JOINs, aggregations, 1M+ rows)

No MLV needed

YES

Queried frequently?

Cost is acceptable

YES

Source data stable?
(not sub-minute changes)

Use DirectQuery

YES

Use an MLV

MLVs + Direct Lake

The power combination for semantic model performance

The Connection

Direct Lake reads Delta tables from OneLake into the VertiPaq engine. Point it at an MLV instead of raw tables and you get pre-aggregated data at import speed. Because MLVs are stored as Delta tables in the Lakehouse, they appear in the SQL endpoint and semantic model designer like any other table – just pick the MLV when building or editing the model.

Raw Tables

50M+ rows

MLV

500K rows

Direct Lake

VertiPaq

Import-speed queries

The VertiPaq Advantage

Fewer rows = faster transcoding into VertiPaq. The aggregation ratio depends on your data, but 10-100x reduction is typical for fact-table rollups – in this example, 50M raw rows compress to 500K.

Raw Tables

50,000,000

MLV Output

500K

Fewer rows means faster transcoding and a smaller memory footprint. In testing, that showed up as a much cheaper cold start and dramatically less compute on heavy queries (up to ~210x less engine CPU on identical work). On a big enough fact it also keeps a heavy query under the capacity's memory ceiling, which is the line that otherwise gets the query rejected outright. That cold and compute saving, not warm speed, is the real reason to pair an MLV with Direct Lake.

See the full before-and-after test, with the matched-grain method, the compute numbers, and every losing case.

Full Architecture Pattern

An MLV lands at Silver+ or Gold+ in your Medallion Architecture. The "+" means the view sits at that layer and reads from everything upstream, so a Silver+ MLV can pull from Bronze, and a Gold+ MLV can pull from Silver and Bronze. Bronze is the one layer it can't sit in: an MLV needs conformed input, not raw ingestion.

Two placements, same engine. The highlighted card is where the MLV lives in each pattern.

Pattern 1 · Silver+ MLV

Bronze

Raw Ingestion

ERP, CRM, logs. Source format preserved.

Silver

Cleaned & Conformed

Validated, de-duplicated. Delta tables.

Silver+

Materialized Lake View

Cleaned, conformed, pre-computed.

Gold

Business-Ready

Star schema. Aggregation tables.

Direct Lake

Semantic Model → Reports

Power BI & paginated reports.

The MLV does the cleaning and conforming, then Gold rolls it into a star schema for the model. Use this when your heavy lifting is the Silver transform.

Pattern 2 · Gold+ MLV

Bronze

Raw Ingestion

ERP, CRM, logs. Source format preserved.

Silver

Cleaned & Conformed

Validated, de-duplicated. Delta tables.

Gold

Business-Ready

Star schema. Aggregation tables.

Gold+

Materialized Lake View

Pre-computed Delta tables.

Direct Lake

Semantic Model → Reports

Power BI & paginated reports.

The MLV is the business-ready aggregation itself, feeding the semantic model directly. This is the most common placement and the one that pairs with Direct Lake.

Gold+ means Lakehouse plus Direct Lake, not a Warehouse. The serving engine matters here. If your gold layer is a Fabric Warehouse doing real DWH work (T-SQL transforms, IDENTITY surrogate keys, MERGE, stored procs), MLVs aren't the mechanism for that. If your serving layer is Direct Lake on a Lakehouse, a gold MLV is a good fit. Same medallion, different serving engine.

See also: Direct Lake Guide · Medallion Architecture Guide

Best Practices & Limitations

Getting the most from Materialized Lake Views

Best Practices

Start with Slowest Queries

Identify your most expensive SQL operations first, those deliver the biggest ROI as MLVs

Monitor Cost vs. Savings

MLVs consume storage and refresh compute, ensure the query savings outweigh the maintenance cost

Keep Definitions Focused

Narrow, purpose-built MLVs outperform wide "kitchen sink" views, target specific report needs

Combine with Partitioning

Partition source tables before creating MLVs, incremental refresh becomes even faster

Use Naming Conventions

Prefix with mlv_ to distinguish MLVs from regular views and tables at a glance

Test with Real Workloads

Validate performance gains with actual report queries, not just synthetic benchmarks

Cost Considerations

MLVs trade storage and refresh compute for query-time savings. Understanding the billing model helps you right-size your approach.

Refresh Compute

Runs on Apache Spark (1 CU = 2 vCores). Classified as a background operation with 24-hour smoothing, reducing throttling risk.

OneLake Storage

Each MLV persists as Delta files. Storage is billed separately per GB (not from your CU capacity). Old Delta versions also count until VACUUM.

Refresh Strategy

Optimal refresh auto-selects: skip (no changes), incremental, or full rebuild. SUM/COUNT and left/semi joins and CTEs are incremental-eligible; other aggregates need partitioned sources; window functions force full refresh.

Key insight: SUM() and COUNT() (without DISTINCT) with GROUP BY refresh incrementally without partitioning requirements. Other aggregates, AVG(), MIN(), MAX(), STDDEV(), require every source table to be partitioned with the partition column in the GROUP BY; mixing those with SUM/COUNT in the same query applies the partitioning requirement to the whole query. Left/semi joins and CTEs are incremental-eligible; window functions still force a full rebuild. The bigger catch is that incremental only applies when the source is append-only. The moment a source table records an update or delete (any SCD type-1/type-2 change), that refresh drops to full regardless of the query pattern. Factor that into capacity planning for MLVs over mutable tables.

Current Limitations

CDF Required for Incremental

Incremental refresh needs source tables with delta.enableChangeDataFeed=true AND append-only data for that refresh cycle. Any delete or update forces a full rebuild, CDF or not.

Spark SQL Authoring Only

Authored in a Lakehouse notebook via Spark SQL. PySpark authoring is in preview and currently only supports full refresh.

Storage Cost

Each MLV is a physical Delta table, you're trading storage for compute savings. Monitor OneLake usage

Refresh Latency

Automatic maintenance isn't instant, there's a latency window between source changes and MLV update

ALTER Is Rename-Only

Changing the SELECT, constraints, or partitioning means re-running CREATE OR REPLACE; ALTER itself only supports RENAME TO

Skip Sequential Keys

Skip ROW_NUMBER-style keys inside an MLV. A deterministic hash key is the safe choice.

Single-Lakehouse Scope

Cross-lakehouse lineage and execution aren't supported. All source tables and dependent MLVs must live in the same Lakehouse.

No T-SQL Change Feed

You can't cleanly incremental-load a Warehouse from an MLV. The Fabric Warehouse can't read an MLV's change feed in T-SQL. Plan to reload, not delta-load.

MLVs are an evolving feature. Microsoft is actively expanding Spark SQL coverage, PySpark authoring (currently preview), and maintenance capabilities. Check the latest Fabric documentation for current limitations.

Comparison Table

MLVs vs. other pre-computation strategies

Regular View

Storage None

Compute Cost Every query

Freshness Real-time

Maintenance None

Complexity Low

Direct Lake No

Best For Simple logic reuse

MLV

Storage Delta table

Compute Cost Minimal

Freshness Optimal refresh

Maintenance Automatic

Complexity Low

Direct Lake Yes

Best For Repeat heavy queries

Manual Agg Table

Storage Delta table

Compute Cost Minimal

Freshness Manual refresh

Maintenance Manual pipeline

Complexity Medium

Direct Lake Yes

Best For Custom ETL control

Power BI Agg Table

Storage Imported

Compute Cost Minimal

Freshness Dataset refresh

Maintenance Manual config

Complexity Medium

Direct Lake Dual-mode only

Best For Legacy PBI agg patterns

A Real-World Test

I built this on real data, scaled it to nearly half a billion rows, and measured it. The wins weren't where I expected.

Real NYC taxi data, 489 million rows, queried raw vs through an MLV (Direct Lake on OneLake). On a trial F64, so read the times as relative, not absolute. The surprise: the MLV barely changes warm query speed, even at half a billion rows. What it actually buys is a faster cold start, a smaller memory footprint, and the ability to answer a heavy query that the raw fact rejects outright.

These are my results on my setup, not a law of physics. There are surely edge cases where pre-joining or pre-aggregating does help warm. I just couldn't produce one after a lot of trying, so take it as a strong signal, not a guarantee.

Cold Start

A complex query (489M rows, 5-way join, 4 measures), raw star vs a pre-joined MLV

Cold query (first hit after a refresh)

Raw star

10.3 s

MLV

1.6 s

6.3x faster on the MLV (10.3s vs 1.6s). This is the real win: the first hit after a refresh, before columns are resident in memory.

Warm Start

Same query, columns already resident, result cache defeated

Simple report query, small output

Raw star

132 ms

MLV

113 ms

Dense matrix visual, expensive measure

Raw star

2,885 ms

MLV

193 ms

Warm depends entirely on how much the query computes. For an everyday report query that returns a few thousand rows, it's a wash (132ms vs 113ms), because once columns are resident the cost tracks output, not raw rows. But a dense visual that materializes a large grid with an expensive measure is a different story: warm there ran 2.885s on the raw star vs 0.193s on a grain-matched MLV, about 15x faster on identical output, and roughly 210x less engine CPU. The full write-up has both tests and the method.

The Verdict

Where it helps

Faster cold start: 6.3x (10.3s to 1.6s) on the 489M query
Smaller model: 20 VertiPaq segments vs 142, 264 KB vs 2.19 GB on disk
Cheap incremental refresh: one aggregation MLV merged 134 changed rows, no full rebuild
Runs queries the raw fact can't: a measure that hit the memory governor and was rejected at 489M, the MLV served in 83ms
Speeds up a heavy warm visual: a dense matrix ran 2.885s on the raw star vs 0.193s on a grain-matched MLV (identical 790-row output), and ~210x less engine CPU (6,500ms vs 31ms)

Where it doesn't

Faster warm reports in the common case: cache-busted, 132ms raw vs 113ms MLV, a wash on almost everything I tested
Queries below the MLV's grain fall back to the raw fact
Distinct counts that need to roll up (a summed one gave 9,108 vs the real 260)
Automatic query rewrite: Fabric has none, the model points at the MLV on purpose (unlike Synapse MVs or Power BI aggregations)
Warehouse serving, this is Direct Lake on a Lakehouse

Read the full write-up, with the matched-query method, the fairness corrections, and every number.

Set up the test: real NYC taxi data, 489 million rows, raw vs MLV on Direct Lake. **Say the trial-F64 caveat out loud** so nobody thinks it's rigged: read the times as relative, not absolute. Tease the honest headline now, the warm wins weren't where you expected.

**Cold start is the real win.** 10.3s raw vs 1.6s MLV, 6.3x faster. This is the first hit after a refresh, before columns are resident. If the room only remembers one number, it's this one.

**Warm is a wash.** 132ms vs 113ms even with the result cache defeated. Be honest here, this is the surprise. Once columns are resident, VertiPaq runs the join and the math fast no matter the row count. Don't oversell the MLV as a warm-query accelerator.

The two-column verdict. Left: cold start, smaller model, cheap incremental refresh, runs queries the raw fact can't. Right: warm reports (wash), sub-grain queries fall back, distinct counts that roll up, warehouse serving. Point to the full write-up for the matched-query method and every losing case.

Start with the **pain point**: everyone has reports hitting the same tables. *Ask: "How many of you have reports that take 30+ seconds to load?"*, gets engagement. Walk through the 4 expensive patterns. Emphasize: **each report recalculates independently**.

Drive home the **cost**: CU spikes, slow loads, and the dreaded **DirectQuery fallback**. *Transition: "What if we could compute these results once and store them?"*

Frame the contrast that's about to land. "Same query, two very different storage strategies. Let's look at each side."

**Regular View.** Virtual; no physical storage. Re-executes the underlying query on every access. Full compute cost each time. Always live, but you pay for that freshness on every read. *"A saved query, not a saved result."*

**Materialized Lake View.** Persisted as a Delta table in OneLake. Pre-computed; reads return stored results. Minimal compute on query. Auto-maintained by Fabric. *"A saved result, not a saved query."*

Read the definition aloud. Emphasize three words: **pre-computed**, **Delta**, **auto-maintained**.

Frame the four properties about to land. "Four things that make MLVs different from a regular view; we'll walk each one."

**Pre-Computed.** Query runs at creation and again on each refresh; results are stored, so reads are instant. "Saved result, not saved query."

**Delta-Stored.** Persisted as Delta/Parquet files in OneLake. Queryable by *any* engine that reads Delta, not locked to Power BI or Spark.

**Auto-Maintained.** Scheduled refresh auto-selects: skip (no changes), incremental (append-only), or full rebuild. No manual pipeline.

**Spark SQL Defined.** Authored in a Lakehouse notebook using `CREATE MATERIALIZED LAKE VIEW`. Familiar SQL surface; most analysts can write one immediately.

Walk through the creation flow. Emphasize: "You write Spark SQL in a Lakehouse notebook, Fabric does the rest." If demo time: show the CREATE statement running in the Lakehouse Spark SQL editor.

Highlight that the MLV lives **alongside** source tables in OneLake. Any engine (Spark, SQL, Power BI) can read it, it's just a Delta table.

**Key selling point**: auto-maintenance. "You don't rebuild it, Fabric detects changes and updates incrementally." *Compare to manual agg tables that need pipeline orchestration.*

**CDF Prerequisite.** Hammer this point. Incremental refresh needs TWO things: source tables must have `delta.enableChangeDataFeed=true`, AND the source must be append-only for that refresh cycle. Any delete or update forces a full rebuild even with CDF on. Without CDF at all, you're stuck choosing between skip and full rebuild. Easy to forget; easy to debug once you know.

Correct a common misconception here: routing is NOT automatic. The MLV is just another Delta table, point your model or report at it explicitly. "Once you've pointed at the MLV, every downstream query reads pre-computed results, no DAX or query changes needed after that."

If live demo: run this exact query. Otherwise, walk through each clause. Emphasize the **familiar SQL**, no new language to learn.

Quick note: ALTER only renames; for everything else, re-run CREATE OR REPLACE. DROP is a separate operation.

Show the metadata query if doing a demo. Otherwise, quick pass. *Transition: "Now that we know HOW, let's talk about WHEN."*

Frame the ideal-patterns trio. "Three places MLVs shine; we'll walk each then flip to the anti-patterns."

**Heavy Aggregations.** SUM / AVG / COUNT across millions of rows. Classic MLV use case; pre-compute once, read many times.

**Complex Multi-Table JOINs.** Flatten fact-dimension joins into a single pre-joined table. Eliminates the JOIN cost at query time.

**Frequently-Queried Subsets.** Filtered slices (last 2 years, top regions) that many reports hit. Ask: "Which of these sounds like your environment?"

Frame the anti-patterns. "Now the inverse; three signals that say don't reach for MLV."

**Sub-Minute Volatile Data.** First anti-pattern. If data changes every few seconds, the refresh can't keep up; use DirectQuery instead.

**Small Simple Tables.** Don't over-engineer. If the table is already small enough to query directly, the maintenance overhead isn't worth it.

**Row-Level Security Needs.** MLVs don't natively support RLS. Per-user filtering still has to happen downstream (Power BI model, view layer).

**Decision Tree.** Walk top to bottom. Three yes answers → MLV. Otherwise: skip it or use a different storage strategy.

**This is the key slide for Fabric audiences.** Direct Lake + MLV = import speed on pre-aggregated data. Point at the row counts: 50M → 500K.

Hammer the **100x reduction**. "Fewer rows means faster transcoding, less memory, and you stay in Direct Lake mode." *If they've seen the Direct Lake guide, reference the fallback thresholds.*

Frame the medallion placement. **An MLV is a layer, not a bolt-on.** The "+" just means "sits here and reads everything upstream." Bronze is the only layer it can't be, it needs conformed input. Tell them you're about to show the same engine in two spots.

**Pattern 1: Silver+.** The MLV does the cleaning and conforming, Gold rolls it into the star. Use when the expensive work is the Silver transform. Point at the silver dashed card, that's where the MLV lives.

**Pattern 2: Gold+.** The MLV *is* the business-ready aggregation feeding the model. Most common placement, the one that pairs with Direct Lake. Point at the gold dashed card.

**The serving-engine caveat.** Gold+ means Lakehouse plus Direct Lake, NOT a Fabric Warehouse doing real DWH work (T-SQL transforms, surrogate keys, MERGE, stored procs). Same medallion, different serving engine. Cross-reference the Medallion and Direct Lake guides.

Frame the section. "Six things to keep in mind once you're actually building MLVs in production." Let the title land before you start walking the cards.

**Start with Slowest Queries.** Best opening advice. Find the queries that hurt today; MLV those first. Biggest ROI.

**Monitor Cost vs. Savings.** MLVs aren't free; refresh compute + storage. If the query savings don't outweigh the maintenance cost, it's not worth keeping.

**Keep Definitions Focused.** Narrow, purpose-built MLVs beat "kitchen sink" views. Target specific report needs, not "maybe useful someday."

**Combine with Partitioning.** Partition source tables before creating MLVs; incremental refresh becomes even faster because Spark can skip whole partitions.

**Use Naming Conventions.** Prefix with `mlv_`. Distinguishes MLVs from regular views and tables at a glance; saves confusion in the Lakehouse explorer.

**Test with Real Workloads.** Synthetic benchmarks lie. Validate with actual report queries before committing to a refresh schedule.

**Cost Considerations.** Three buckets: refresh compute (Spark CUs), OneLake storage (billed per GB), and refresh strategy (optimal auto-selects). SUM/COUNT aggregations refresh incrementally without partitioning; other aggregates (AVG/MIN/MAX/STDDEV) need partitioned sources or they fall back to full refresh.

Frame the limitations section. Be honest about the state of MLVs. "Eight things you need to know about today; we'll walk each one, then close with where this is headed."

**CDF Required for Incremental.** Two conditions, not one: `delta.enableChangeDataFeed=true` on source tables, AND append-only data for that refresh cycle. Without CDF, optimal refresh can only skip or full-rebuild. With CDF but a delete or update in the cycle, it still falls back to full rebuild. Easy to forget; check both first when incremental refresh isn't kicking in.

**Spark SQL Authoring Only.** Lakehouse notebooks via Spark SQL today. PySpark authoring is in preview and only supports full refresh. T-SQL authoring isn't on the roadmap that I can see.

**Storage Cost.** Each MLV is a physical Delta table. You're trading OneLake storage for query compute savings. Monitor your OneLake usage when you start scaling MLV count.

**Refresh Latency.** Automatic maintenance isn't instant; there's a latency window between source changes landing and the MLV catching up. For near-real-time needs, MLV isn't the right tool.

**ALTER Is Rename-Only.** Schema changes, partitioning changes, constraint changes; all require CREATE OR REPLACE. ALTER MATERIALIZED LAKE VIEW only handles RENAME TO. Plan deployment scripts accordingly.

**Skip Sequential Keys.** Don't mint ROW_NUMBER-style surrogate keys inside an MLV. The window function forces a full refresh, and a recompute can reassign the numbers and silently break fact-to-dim joins. A deterministic hash key (`HASHBYTES('SHA2_256', natural_key)`) is safe because it survives recompute. For identity-style sequential keys, mint them upstream: a Fabric Warehouse IDENTITY column (currently preview) or a MERGE.

**Single-Lakehouse Scope.** All source tables AND dependent MLVs must live in the same Lakehouse. Cross-lakehouse lineage isn't supported.

**No T-SQL Change Feed.** If anyone's planning to feed a Warehouse off an MLV, set expectations now. The Warehouse can't read an MLV's change feed in T-SQL; `table_changes` isn't exposed to the SQL endpoint yet (open Fabric Idea). And because any update or delete drops the MLV to a full refresh, the feed isn't a clean row-level delta anyway. Bottom line: plan to reload the Warehouse, not delta-load it.

**MLVs are an evolving feature.** Land the closing note: this list will shrink. Microsoft is actively expanding Spark SQL coverage, PySpark authoring (preview), and maintenance capabilities. Tell the audience to check Fabric docs for the current state before designing around a limitation that may already be lifted.

Frame the table. "Four storage strategies side by side; we'll walk each column. The teal one's the MLV."

**Regular View.** No storage, no maintenance, but every query re-executes the full compute. Real-time freshness is the only win; everything else loses at scale.

**MLV.** The teal column. Delta-stored, minimal compute on query, optimal refresh (auto-selected), automatic maintenance, Direct Lake compatible. Best for repeat heavy queries. *"In Fabric, MLVs give you the best of both worlds."*

**Manual Agg Table.** Same Delta storage + minimal query compute as an MLV, but **you** own the refresh pipeline. Higher complexity, manual maintenance. Pick this when you need custom ETL control the MLV engine doesn't give you.

**Power BI Agg Table.** The legacy pattern. Imported into the model, dataset refresh, manual config, Direct Lake works only in dual-mode. Still valid for existing PBI agg deployments; for new builds, MLVs are usually the better answer.

Discussion

Loading comments...

1 / 10