Microsoft Fabric

Materialized Lake Views in Microsoft Fabric: A Practical Guide

Learn how Materialized Lake Views work in Microsoft Fabric, including Delta persistence, dependency-aware refresh, medallion design and practical tradeoffs.

By JaviPublished 5 min readIntermediate

Materialized Lake Views, or MLVs, let data engineers define reusable Lakehouse transformations declaratively with Spark SQL. Fabric persists their results as managed Delta data and tracks the dependencies needed to refresh a lineage in the correct order.

That combination matters: an MLV is neither a normal view that reruns its query for every reader nor just a Delta table whose load and orchestration you must build yourself. This guide uses a small bronze-to-silver-to-gold example to explain where MLVs fit, how refresh works, and when a notebook or pipeline remains the better tool.

What is a Materialized Lake View?

A normal SQL or Spark view stores a query definition but not its result. An MLV stores both the definition and a persisted result in the Lakehouse.

  1. Source tables
  2. Transformation query
  3. Materialized Lake View
  4. Persisted Delta result
How a Materialized Lake View persists a transformation result

Consumers query the materialized result instead of repeating the complete transformation. Fabric also understands dependencies between source tables and MLVs. This dependency-aware approach complements the broader medallion architecture pattern.

Why this matters

Without MLVs, a recurring SQL transformation often becomes a chain of notebooks plus separate orchestration:

  1. Bronze tables
  2. Notebook A
  3. Silver tables
  4. Notebook B
  5. Gold tables
A notebook-orchestrated medallion flow

With MLVs, you declare the relationships. Fabric can derive an execution order from the dependency graph:

    • bronze.orders
    • bronze.products
  1. silver.cleaned_orders
  2. gold.product_sales_summary
Fabric derives the Materialized Lake View dependency order

Prerequisites and scope

MLVs are created and managed in a Fabric Lakehouse with schemas enabled and a supported Fabric runtime and capacity. Because the feature evolves, confirm the current runtime, region and capacity requirements in Microsoft Learn before adopting it in production.

The example uses two source tables, bronze.orders and bronze.products, followed by a cleaned silver result and a reusable gold aggregate.

Build the example

Create and populate the source tables through your normal ingestion process:

SQL query: Build the exampleSpark SQL
CREATE TABLE bronze.orders (
    order_id BIGINT, product_id BIGINT, quantity INT,
    unit_price DECIMAL(12,2), order_date DATE
);

CREATE TABLE bronze.products (
    product_id BIGINT, product_name STRING, category STRING
);

INSERT INTO bronze.orders VALUES
(1, 101, 2, 25.00, DATE '2026-10-01'),
(2, 102, 1, 80.00, DATE '2026-10-01'),
(3, 101, 3, 25.00, DATE '2026-10-02'),
(4, 103, 0, 50.00, DATE '2026-10-02');

INSERT INTO bronze.products VALUES
(101, 'Keyboard', 'Accessories'),
(102, 'Monitor', 'Displays'),
(103, 'Mouse', 'Accessories');
Spark SQL19 linesRead-only query display. It is not connected to a database.

The silver MLV removes invalid quantities and calculates a reusable sales amount:

SQL query: Build the exampleSpark SQL
CREATE MATERIALIZED LAKE VIEW silver.cleaned_orders AS
SELECT order_id, product_id, quantity, unit_price,
       quantity * unit_price AS sales_amount, order_date
FROM bronze.orders
WHERE quantity > 0 AND unit_price > 0;
Spark SQL5 linesRead-only query display. It is not connected to a database.

The gold MLV joins the product dimension and aggregates the cleaned orders:

SQL query: Build the example (2)Spark SQL
CREATE MATERIALIZED LAKE VIEW gold.product_sales_summary AS
SELECT p.product_id, p.product_name, p.category,
       SUM(o.quantity) AS units_sold,
       SUM(o.sales_amount) AS total_sales
FROM silver.cleaned_orders o
JOIN bronze.products p ON o.product_id = p.product_id
GROUP BY p.product_id, p.product_name, p.category;
Spark SQL7 linesRead-only query display. It is not connected to a database.

Fabric can now see that the gold result depends on both silver.cleaned_orders and bronze.products.

MLV vs normal view vs Delta table

Capability Normal view Materialized Lake View Delta table
Stores the query definition Yes Yes No
Persists the result No Yes Yes
Recomputes for each query Yes No No
Fabric-managed refresh No Yes No
Dependency-aware orchestration No Yes No
Best fit Lightweight reusable logic Repeated declarative transforms Custom processing and writes

A Delta table gives you physical storage, but your code must create, update and monitor it. An MLV combines persisted data with declarative logic and managed refresh behavior.

Refresh, performance and data quality

Fabric evaluates whether source data changed and whether an incremental refresh is possible. It can skip unchanged work, process changes incrementally, or perform a full refresh.

  1. Source changed?
    • No
      Skip
    • Yes, incremental possible
      Incremental refresh
    • Yes, otherwise
      Full refresh
Fabric chooses the appropriate refresh strategy

Incremental processing depends on Delta Change Data Feed being enabled on the source tables:

SQL query: Refresh, performance and data qualitySpark SQL
ALTER TABLE bronze.orders
SET TBLPROPERTIES (delta.enableChangeDataFeed = true);
Spark SQL2 linesRead-only query display. It is not connected to a database.

Incremental does not automatically mean faster. A full rebuild may be cheaper when most rows changed, and persisted results consume storage and refresh capacity. Measure refresh duration, changed-row volume, downstream query latency and capacity use together.

MLVs can also express data-quality constraints declaratively:

SQL query: Refresh, performance and data quality (2)Spark SQL
CREATE MATERIALIZED LAKE VIEW silver.valid_orders (
    CONSTRAINT valid_quantity CHECK (quantity > 0) ON MISMATCH DROP
) AS
SELECT order_id, product_id, quantity, unit_price, order_date
FROM bronze.orders;
Spark SQL5 linesRead-only query display. It is not connected to a database.

Depending on the chosen action, invalid rows can be dropped or make the refresh fail. Keeping the rule in the definition makes it visible alongside the transformation instead of hiding it in notebook code.

Scheduling and monitoring

Fabric can schedule a lineage and respect its dependency order:

  1. 1Refresh silver.cleaned_orders
  2. 2Wait for success
  3. 3Refresh gold.product_sales_summary
Dependency-aware refresh order

Production operation still requires attention. Review refresh history and status, lineage, data-quality results, Spark logs, runtime, capacity consumption and failure behavior. Managed orchestration reduces plumbing; it does not remove the need for observability.

When to use an MLV

Use an MLV when the transformation is mainly Spark SQL, its result is reused, it runs repeatedly, and dependency-aware refresh is valuable. Frequent aggregates, repeated large joins, reporting datasets and declarative medallion stages are strong candidates.

Use a notebook when you need Python or PySpark, custom libraries, APIs, machine learning or procedural logic. Use a Data Pipeline when work crosses systems or requires broader workflow control, copying, branching or activities outside the Lakehouse. These tools are complementary; the data integration patterns guide helps separate their responsibilities.

The Lakehouse vs Warehouse decision guide also helps determine where downstream consumers and write workloads belong.

Design tradeoffs

Do not turn every table into an MLV. Ask whether the transformation produces a persisted, reusable result that must refresh repeatedly. A one-time preparation step may be simpler as a notebook or table. A cross-system workflow may be clearer as a pipeline.

Also avoid long dependency chains without operational boundaries. They can make refresh windows, failure recovery and capacity use harder to reason about. Keep each MLV focused, name layers consistently, test full and incremental paths, and document the freshness contract expected by consumers.

The architectural payoff is broader than faster queries: Fabric can manage persistence, dependencies, refresh strategy, scheduling, lineage and monitoring for a declarative Lakehouse transformation. That makes Materialized Lake Views worth evaluating before automatically adding another notebook and another orchestration step.

References

Tags

  • Microsoft Fabric
  • Fabric Lakehouse
  • Spark SQL
  • Medallion Architecture
  • Delta Lake
  • Performance