time, constructing a medallion structure in Microsoft Cloth meant stitching collectively a small orchestra of transferring components: notebooks for the transformations, pipelines for orchestration, schedules for refresh, customized code for knowledge high quality checks, and the Monitor Hub for maintaining a tally of whether or not something really labored. Each layer labored – till one thing didn’t, and then you definately had to determine which layer broke, why, and which downstream layers acquired affected alongside the best way.
In the event you’ve ever tried to debug a silver layer that didn’t replace as a result of the bronze pocket book failed three hours in the past, you realize precisely what I’m speaking about.
Then, at FabCon Atlanta in March 2026, materialized lake views (MLVs) went usually accessible. And the story they’re telling is easy: what in case your complete medallion pipeline may very well be a number of SELECT statements?
Let me stroll you thru the entire thing – what they’re, how they work, what modified between preview and GA, and the place they match (and the place they don’t) in your structure.
Materialized Lake View – WHAT?
A materialized lake view is a endured, robotically refreshed view outlined in Spark SQL or PySpark. You write a SELECT question that describes the transformation you need, and Cloth takes care of execution, storage, refresh, dependency monitoring, and knowledge high quality enforcement.
The result’s saved as a Delta desk in your lakehouse. So downstream customers, akin to Energy BI Direct Lake, Spark notebooks, SQL endpoints, can question it identical to another Delta desk. No particular dealing with, no totally different syntax.
To place it in plain English: an MLV is nothing else however a SELECT assertion that discovered to materialize itself, handle its personal dependencies, schedule its personal refresh, and test its personal knowledge high quality.
OK, that’s good. However what does that really change?
That’s a good query. Earlier than MLVs, constructing a single bronze-to-silver-to-gold move appeared roughly like this: you’d write a pocket book for every transformation, arrange a Knowledge Manufacturing facility pipeline to name them in the best order, configure schedules, construct customized validation logic, after which wire up the Monitor Hub to observe for failures. 5 totally different surfaces, 5 various things to debug when one thing breaks.
With MLVs, all of that collapses into declarative SQL. You describe what you need. Cloth figures out the remainder.
The 4 phases of an MLV’s life
Each MLV strikes via 4 phases. In keeping with the Microsoft documentation, understanding them is the muse for all the pieces else:
Create – You write the Spark SQL (or PySpark) that defines the transformation. Cloth shops the definition and materializes the preliminary outcome as a Delta desk.
Refresh – When supply knowledge modifications, Cloth chooses the optimum technique: incremental (course of solely modifications), full (rebuild), or skip (no modifications detected).
Question – Any software or software reads the materialized outcome. They don’t know – and don’t have to know – that it’s an MLV.
Monitor – Refresh historical past, execution standing, knowledge high quality metrics, and lineage are all tracked and visualised natively in Cloth.
Now let’s dive into every bit.
Create: the syntax
Right here’s the total Spark SQL pseudo-code syntax for creating an MLV, straight from the Microsoft Be taught reference:
CREATE [OR REPLACE] MATERIALIZED LAKE VIEW [IF NOT EXISTS]
[workspace.lakehouse.schema].MLV_Identifier
[(CONSTRAINT constraint_name CHECK (condition) [ON MISMATCH DROP | FAIL], …)]
[PARTITIONED BY (col1, col2, …)]
[COMMENT “description”]
[TBLPROPERTIES (”key1”=”val1”, …)]
AS select_statement
An actual instance – cleansing order knowledge joined from merchandise and orders, with a knowledge high quality constraint and partitioning:
CREATE OR REPLACE MATERIALIZED LAKE VIEW silver.cleaned_order_data
(
CONSTRAINT valid_quantity CHECK (amount > 0) ON MISMATCH DROP
)
PARTITIONED BY (class)
COMMENT “Cleaned order knowledge joined from merchandise and orders”
AS
SELECT
p.productID, p.productName, p.class,
o.orderDate, o.amount, o.totalAmount
FROM bronze.merchandise p
INNER JOIN bronze.orders o ON p.productID = o.productID
Two issues value flagging immediately. First, MLV names are case-insensitive (MyView turns into myview). Second, all-uppercase schema names (like MYSCHEMA) aren’t supported, so use both blended or lowercase.
You additionally want a schema-enabled lakehouse and Cloth Runtime 1.3 or larger. In case your lakehouse doesn’t have schemas turned on, MLVs aren’t accessible, that’s the very first prerequisite.
Refresh: the mind of MLVs
Right here’s the place MLVs cease being intelligent and begin being sensible.
When supply knowledge modifications, Cloth’s optimum refresh engine appears at each MLV within the lineage and asks a collection of questions: Did something really change? Can I course of simply the modifications? Or do I have to rebuild from scratch?
Three potential outcomes:
Skip refresh – supply knowledge hasn’t modified. Don’t waste compute. Transfer on.
Incremental refresh – course of solely the brand new or modified rows. Quick, low-cost, very best.
Full refresh – rebuild the entire thing. Slowest path, used when incremental isn’t secure or potential.
However, and that is vital, incremental refresh isn’t free. It has conditions:
The Delta change knowledge feed (CDF) should be enabled on each supply desk referenced by the MLV (delta.enableChangeDataFeed=true).
The supply should be a Delta desk. Non-Delta sources all the time get a full refresh.
The information should be append-only. In case your supply has updates or deletes, Cloth falls again to a full refresh.
The question should use solely supported SQL constructs (extra on this in a second).
With out CDF enabled, optimum refresh can solely select between skip and full. With CDF on, the total incremental path opens up. Enabling CDF in your supply tables has no measurable storage or efficiency affect for append-only workloads, so there’s little or no purpose to not flip it on:
ALTER TABLE bronze.orders SET TBLPROPERTIES (delta.enableChangeDataFeed = true);
ALTER TABLE bronze.merchandise SET TBLPROPERTIES (delta.enableChangeDataFeed = true);
Can it get higher than this? Really, sure! And, that is the place the GA story actually begins.
What’s new within the Basic Availability section?
MLVs had been launched in preview at Construct 2025. Between then and GA in March 2026, Microsoft closed crucial gaps. 5 main modifications turned MLVs from “fascinating” into “production-ready”:
Multi-schedule assist
Broader incremental refresh protection
PySpark authoring (preview)
In-place updates with Exchange
Stronger knowledge qc
Let me take them one by one.
1. Multi-schedule assist
In preview, you would solely refresh all MLVs in a lakehouse on a single schedule. Want finance to replace hourly, however analytics to replace each six hours? You needed to work round it with notebooks, which broke dependency consciousness, error reporting, and retry logic. Pocket book-triggered refreshes don’t floor MLV error particulars. Failures seem solely in cell output, and dependent views haven’t any consciousness of them. Errors can persist week after week with out anybody figuring out the pipeline is damaged.
Now you possibly can outline named schedules inside a single lakehouse, every concentrating on a particular subset of views. Finance pipeline hourly. Analytics each six hours. Advertising and marketing each quarter-hour. All in the identical lakehouse, no customized code.
When a named schedule runs, Cloth nonetheless refreshes all upstream dependencies within the right order, runs unbiased views in parallel, and surfaces errors centrally. If a run is already in progress when a schedule fires, the brand new run is skipped, and the subsequent window proceeds as anticipated – so that you don’t have to fret about overlapping runs stomping on one another.
2. Broader incremental refresh
Incremental refresh used to fall again to full very often, as a result of the record of “supported” SQL constructs was slender. At GA, that record expanded considerably. MLVs now refresh incrementally when the definition consists of:
Aggregations like COUNT and SUM with GROUP BY
Left outer joins and left semi joins
Widespread desk expressions (CTEs)
That’s a significant change. Most real-world medallion pipelines I’ve labored on use precisely these patterns, and now they qualify for incremental processing with out being rewritten. With optimum refresh, a built-in choice engine examines every refresh, evaluates the quantity of modified knowledge in opposition to the price of a full recomputation, and robotically chooses the sooner path.
I hear you, I hear you: Nikola, what occurs if my question makes use of one thing the engine can’t deal with incrementally? Don’t fear, it’s a lot simpler than it sounds:) Utilizing unsupported constructs doesn’t forestall you from creating the MLV. It solely implies that Cloth makes use of a full refresh as an alternative of an incremental one. Optimum refresh robotically falls again to full when wanted, so that you don’t usually have to drive it. In the event you do need to drive one (for instance, to reprocess knowledge after a correction), there’s a one-liner for that:
REFRESH MATERIALIZED LAKE VIEW silver.cleaned_order_data FULL;
3. PySpark authoring (preview)
This one is big! SQL is nice till your transformation logic includes a customized Python library, an ML inference name, or a UDF that wraps complicated enterprise guidelines. You then’d hit a wall as MLVs had been SQL-only.
With PySpark authoring, now you can create, refresh, and change MLVs from Cloth notebooks utilizing PySpark and the acquainted DataFrameWriter API. The fmlv module exposes a decorator-based sample, documented within the official PySpark MLV reference:
import fmlv
from pyspark.sql import features as F
@fmlv.materialized_lake_view(
title=”LH1.silver.customer_silver”,
remark=”Cleaned & enriched buyer silver MLV”,
partition_cols=[”year”, “city”],
table_properties={”delta.enableChangeDataFeed”: “true”},
change=True
)
@fmlv.test(title=”non_null_sales”, situation=”gross sales IS NOT NULL”, motion=”DROP”)
def customer_silver():
df = spark.learn.desk(”bronze.customer_bronze”)
cleaned_df = df.filter(F.col(”gross sales”).isNotNull())
enriched_df = cleaned_df.withColumn(”sales_in_usd”, F.col(”gross sales”) * 1.0)
return enriched_df
A number of PySpark gotchas value figuring out about:
PySpark MLVs are nonetheless in preview on the time of writing.
Right this moment, PySpark-authored MLVs all the time carry out a full refresh. Optimum refresh for PySpark is on the roadmap, however not right here but.
The @fmlv decorator doesn’t assist dynamic parameters or variables. All parameters should be hardcoded.
You possibly can’t create an MLV from a PySpark short-term view (createOrReplaceTempView) – the engine can’t see session-scoped views. Use bodily Delta tables or different MLVs as sources.
Don’t delete the pocket book the place the MLV is outlined. Scheduled refresh fails with out it.
So in case your transformation could be expressed cleanly in SQL, SQL continues to be the higher selection for efficiency. PySpark MLVs unlock the circumstances the place SQL alone gained’t do.
4. In-place updates (Exchange)
Enterprise logic modifications. A filter shifts. A be part of good points a column. An aggregation provides a metric. In preview, updating an MLV definition required dropping and recreating it, which misplaced refresh historical past and compelled downstream customers to reconnect.
Now, with the Exchange functionality, you replace an MLV’s definition in place. Cloth validates the brand new logic, swaps it in, and preserves the view’s identification, metadata, and lineage. Downstream dependencies stay intact. Works for each SQL (CREATE OR REPLACE) and PySpark (change=True).
That is a kind of “beneath the radar” GA options that doesn’t get headlines however issues enormously day-to-day. In the event you’ve ever needed to coordinate dropping and recreating a closely consumed desk whereas manufacturing is operating, you realize the ache. That goes away with this.
5. Stronger knowledge high quality
Knowledge high quality constraints are nothing new in MLVs, however at GA, they acquired a severe improve. Now you can:
Use expression-based logic that mixes a number of columns
Apply arithmetic and built-in features inside a single rule
Invoke session-scoped user-defined features for validation logic that lives in Python fairly than SQL
Mix that with the auto-generated knowledge high quality experiences, and also you get one thing near a built-in knowledge observability layer. You possibly can rapidly spot which guidelines fail most frequently, which views they have an effect on, and the way tendencies shift over time, with out constructing a separate monitoring pipeline.
The lineage view – Dependencies without cost
When one MLV references one other (or a desk), Cloth infers the connection robotically. No handbook configuration, no exterior orchestration software. The dependencies are found out of your SQL.
That dependency graph turns into a visible lineage in your lakehouse. Every node represents a change. Arrows present execution order. Cloth makes positive that when bronze knowledge lands, the bronze-to-silver MLV runs first, then the silver-to-gold MLV runs in opposition to the freshly up to date silver.
That is the place the declarative method actually pays off. You’re not writing pipelines. You’re not defining orchestration. You’re writing what every layer ought to seem like, and Cloth figures out the order. That is the great thing about a declarative method:)
A number of helpful behaviors to find out about:
Impartial views run in parallel
Errors floor centrally as an alternative of getting misplaced in pocket book cell output
The lineage view auto-refreshes each two minutes when a run is in progress
All shortcuts are handled as supply entities within the lineage view
You possibly can connect a customized Spark setting to materialized lake views lineage to optimise efficiency and useful resource utilization throughout refresh
Knowledge high quality – Declared!
I touched on this above, nevertheless it deserves its personal part as a result of it’s one of many issues that makes MLVs really feel totally different from a hand-built pipeline.
Each MLV can have a number of knowledge high quality constraints connected:
CREATE OR REPLACE MATERIALIZED LAKE VIEW silver.valid_orders
(
CONSTRAINT positive_quantity CHECK (amount > 0) ON MISMATCH DROP,
CONSTRAINT valid_date CHECK (orderDate >= ‘2020-01-01’) ON MISMATCH FAIL
)
AS
SELECT * FROM bronze.orders
Two motion sorts:
DROP – violating rows are eliminated, the rely is logged within the lineage view, and the pipeline retains going
FAIL – the refresh stops on the first violation. That is additionally the default if you happen to don’t specify
If a number of constraints are current and each behaviors are configured, FAIL takes priority.
Violations floor within the lineage view and run particulars. Superb, however what does that really seem like in apply? Properly, within the knowledge high quality report, you’ll see counts by constraint, by view, over time. So if a constraint that usually drops 0.1% of rows instantly drops 15%, you’ll see the spike and know precisely which rule failed and which view it belongs to. That’s a top quality sign you’d in any other case should construct by hand.
The Microsoft docs additionally observe that the brand new expression-based constraints assist built-in Spark/SQL features like UPPER(), LOWER(), TRIM(), COALESCE(), INITCAP(), and DATE_FORMAT(), so your CHECK circumstances could be richer than simply easy comparisons.
When MLVs shine and once they don’t
MLVs aren’t a hammer for each nail. The Microsoft documentation is unusually direct about the place they match and the place they don’t.
Use MLVs when you could have:
Incessantly accessed aggregations (every day totals, month-to-month metrics) the place precomputed outcomes beat re-running costly queries
Complicated joins throughout a number of massive tables that must be constant for all customers
Knowledge high quality guidelines you need to apply uniformly, declaratively
Reporting datasets that mix knowledge from a number of sources and profit from computerized refresh
Medallion design sample – bronze to silver to gold outlined as SQL transformations
Don’t use MLVs when:
The question runs as soon as or hardly ever – precomputing gained’t assist
Transformations are easy and quick already
You want non-SQL logic like ML inference, API calls, or complicated Python processing – notebooks are nonetheless higher (although PySpark MLVs are beginning to bridge this hole)
You want sub-second latency for streaming – that’s Actual-Time Intelligence territory
I’ll add a private observe right here. I’m at the moment deep in a Microsoft Cloth engagement the place the silver layer design selection – Warehouse vs. Lakehouse with MLVs – is actively on the desk. And what I maintain coming again to is that this: MLVs aren’t competing with the Warehouse the best way some individuals body it. They’re competing with the spaghetti of notebooks-and-pipelines that you simply’d in any other case construct contained in the Lakehouse. In case your staff is already SQL-fluent and your transformations reside naturally in SELECT statements, the case for MLVs because the silver layer in a Lakehouse-based structure is genuinely sturdy.
The positive print – What to know earlier than you soar in
I’d be doing you a disservice if I painted MLVs as a silver bullet. They’ve significant limitations, a few of which is able to matter to your structure:
No cross-lakehouse lineage and execution – all sources, MLVs, and dependencies should reside in the identical lakehouse. In the event you’re utilizing a Cloth Knowledge Warehouse desk as a supply, it’s a must to create a shortcut to it in your lakehouse first.
No DML statements – you possibly can’t INSERT, UPDATE, or DELETE into an MLV. The information is regardless of the SELECT produces.
No time-travel queries within the definition – VERSION AS OF and TIMESTAMP AS OF aren’t allowed.
No UDFs within the SQL definition – although PySpark authoring fills this hole with session-scoped UDFs.
No short-term views as sources – the SELECT can reference bodily tables and different MLVs, however not temp views. This is applicable to PySpark too: createOrReplaceTempView() outputs aren’t seen to the MLV engine.
Session-level Spark properties don’t apply throughout scheduled refresh – set them on the lakehouse or workspace stage as an alternative.
Schema title casing issues – all-uppercase schema names aren’t supported. Use blended case or lowercase.
Area availability – on the time of writing, MLVs aren’t accessible within the South Central US area.
None of those are showstoppers for many pipelines. However they’re value figuring out earlier than you commit an structure to MLVs and uncover the limitation midway via.
Wrapping up
In the event you’ve been constructing medallion design patterns in Cloth utilizing notebooks and pipelines, MLVs are value a severe look. They collapse 5 surfaces into one declarative layer. The dependency administration is computerized. The information high quality is in-built. The lineage is seen. And as of FabCon Atlanta, they’re production-ready.
The roadmap from Microsoft is obvious: optimum refresh for PySpark-authored MLVs is coming, extra SQL operators will change into incremental-refresh-eligible, and deeper integration with different Cloth workloads is on the best way. It is a milestone, not the end line – and I’m curious to see how MLVs evolve over the subsequent few quarters, particularly round PySpark incremental refresh and any cross-lakehouse story Microsoft would possibly inform.
Two takeaways I’d maintain onto:
The “T” in your ELT simply acquired rather a lot simpler to jot down, schedule, and belief – if that “T” is SQL.
MLVs don’t change each pocket book, each pipeline, or each Warehouse. However for declarative transformations that want lineage, refresh, and knowledge high quality baked in, they’re now a legit default in Microsoft Cloth.
Thanks for studying!
