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.
On this page
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.
- Source tables
- Transformation query
- Materialized Lake View
- Persisted Delta 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:
- Bronze tables
- Notebook A
- Silver tables
- Notebook B
- Gold tables
With MLVs, you declare the relationships. Fabric can derive an execution order from the dependency graph:
- bronze.orders
- bronze.products
- silver.cleaned_orders
- gold.product_sales_summary
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:
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');The silver MLV removes invalid quantities and calculates a reusable sales amount:
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;The gold MLV joins the product dimension and aggregates the cleaned orders:
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;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.
- Source changed?
- NoSkip
- Yes, incremental possibleIncremental refresh
- Yes, otherwiseFull refresh
Incremental processing depends on Delta Change Data Feed being enabled on the source tables:
ALTER TABLE bronze.orders
SET TBLPROPERTIES (delta.enableChangeDataFeed = true);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:
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;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:
- 1Refresh silver.cleaned_orders
- 2Wait for success
- 3Refresh gold.product_sales_summary
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
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.