Materialized Lake Views in Microsoft Fabric

Introduction

In this article, i want to focus specifically on Materialized Lake Views (MLVs in order to better understand how they work and where they can be applied.

MLVs are still relatively new in Microsoft Fabric and, at first glance, do not fully align with what Microsoft has strongly promoted so far, namely a strict separation of the individual layers. Nevertheless, they represent a very interesting alternative, especially when it comes to reducing complex ETL processes.

In my projects, I generally follow the Medallion architecture, where I use a separate Lakehouse for each of the Bronze, Silver, and Gold layers. Alternatively, the Gold layer can also be implemented as a Fabric Warehouse.

This is exactly where Materialized Lake Views come into play, as they enable a different approach:

Instead of operating multiple Lakehouses for the individual layers, Bronze, Silver, and Gold can be represented via separate schemas within a single Lakehouse. The individual layers are logically separated while being technically created within the same Lakehouse.

This leads to the central question:

For which scenarios is the use of a single Lakehouse, especially in combination with Materialized Lake Views, actually sufficient or even the better choice?

Arguments for materialized lake views

Performance – Results are precomputed and physically materialized
Consistency – A single, centralized, and consistent data foundation
SQL-based approach – Clear, declarative, and easy to maintain
Efficiency – Refreshes run only when source data changes, reducing compute consumption

Objective of the Article

In the remainder of this article, I will demonstrate how Materialized Lake Views can be created and used in practice. In conclusion, we will summarize the scenarios in which this approach proves to be effective.

Prerequisites

The following components are required to implement this approach:

A workspace with Fabric capacity
A Lakehouse with schema support
A notebook
A data source for transformations

As the data source, I use SAP data provided via SAP Mirroring to illustrate a realistic, production-oriented scenario.

Step 1: Create the Lakehouse

First, we create a Lakehouse with schema support. This is straightforward to set up.

Follow these steps:

Select New item
Search for Lakehouse and choose Lakehouse
Provide a name for the Lakehouse
Click Create

The result can be seen in the screenshots below.

Step 2: Create the Notebook with MLV

In the next step, we create the notebook for working with Materialized Lake Views using Spark. Alternatively, you can choose Python as the language. At the moment (2026‑04‑14), Python support for this scenario is still in preview.

Use the follow steps to create the Notebook and MLV.

In the Lakehouse click to Materialized Lake views
Select New and select New Notebook with Spark SQL
Take a Moment and you see the Notebook Overview

Step 3: Add Schema to the Lakehouse

This step is straightforward. Next, we create the three schemas in the Lakehouse so that we can later separate the individual layers.

What needs to be done:

Start the notebook session
Insert the script and execute the notebook cell

Alternatively, you can insert the script first and then run the cell, the session will start automatically.

Depending on how your Spark pool is configured, it may take up to five minutes for the session to start. Once the execution is complete, refresh the Lakehouse. You should then see the three newly created schemas.

This is also illustrated in the screenshot below.

Here the Skript:

CREATE SCHEMA bronze;
CREATE SCHEMA silver;
CREATE SCHEMA gold;

Step 4: Add Tables to the Lakehouse

After creating the schemas, we now need data. Before continuing, it’s important to note that it is, of course, also possible to create the required tables for processing immediately after creating the Lakehouse. The order of these steps is therefore flexible. The only important requirement is that the schemas must be created beforehand.

I decided to use shortcuts to my replicated SAP Mirroring database. The SAP database contains three tables that I would like to use for this scenario.
The goal later is to use Materialized Lake Views to write the data from the Bronze layer into the Silver layer. The Gold Layer is used here only as an example and will not be discussed further in this article.

Now let’s add the tables to the Lakehouse.

Click on the three dots next to the Bronze schema, as we want to assign the tables to the Bronze layer.
Select Microsoft OneLake.
In my case, choose the SAP Mirrored Database and click Next.
Select the tables you want to add as shortcuts and click Next.
You now have the option to rename the tables. I keep the default names and click Create.
The tables are then added and available as shortcuts.

If the tables do not appear immediately, make sure to refresh the Lakehouse. The same applies to the notebook view.

Once the tables are available, we can continue working in the notebook.

Step 5: Create the Tables with the MLVs

I have created two sample queries that each write data from the Bronze layer into the Silver layer. The tables in the Silver layer are therefore implemented as Materialized Lake Views (MLVs).

Once an MLV is created, metadata is automatically available, such as when it was created or which refresh policy is being used.

These queries are intentionally kept simple for demonstration purposes. However, much more complex and extensive transformations are fully supported.

Here are the sample queries:

Script 1: Create silver.cleaned_customer_data MLV

CREATE SCHEMA IF NOT EXISTS silver;

CREATE MATERIALIZED LAKE VIEW IF NOT EXISTS silver.cleaned_customer_data AS

SELECT

UPPER(Customer) as customer_id,

UPPER(CustomerName) as customer_name,

UPPER(PostalCode) as postal_code,

UPPER(StreetName) as street_name,

UPPER(CityName) as city_name

FROM bronze.Z_CUSTOMER3;

Script 2: Create silver.cleaned_salesorder_data MLV

CREATE SCHEMA IF NOT EXISTS silver;

CREATE MATERIALIZED LAKE VIEW IF NOT EXISTS silver.cleaned_salesorder_data AS

SELECT

SalesOrder as salesorder_id,

UPPER(SalesOrderType) as salesorder_type,

UPPER(CreatedByUser) as createdby_user,

UPPER(LastChangedByUser) as lastchangedby_user,

to_date(CreationDate, ‚yyyyMMdd‘) as creation_date,

UPPER(SoldToParty) as customer_id,

to_date(SalesOrderDate, ‚yyyyMMdd‘) as salesorder_date,

CAST(TotalNetAmount asDECIMAL(18,2)) as totalnet_amount,

UPPER(TransactionCurrency) as transaction_currency

FROM bronze.I_SALESORDER;

You can find the SQL reference in this Microsoft documentation. It also highlights important limitations and considerations to be aware of when working with Materialized Lake Views.

At the end of this article, I will provide a consolidated list of all relevant links for easy reference.

Microsoft SQL Reference Materialited Lake Views

First Conclusion

As we can see, creating Materialized Lake Views is very straightforward, just like setting up the other required components such as the Lakehouse itself and adding tables.

But what are the advantages of MLVs from my perspective?

After creation, MLVs refresh their data automatically (we will look at refresh behavior in more detail later). The notebook is only required for the initial creation.
A single Lakehouse can be used to cover all layers: Bronze, Silver, and Gold.
Extensive and complex transformations are possible within MLVs.

Step 6: Overview and refresh possibilities

At this point, we are done with creating the Materialized Lake Views, and we can return to the Lakehouse to explore the remaining capabilities and perform additional settings.

Once we are back in the Lakehouse, a table named sys_dq_metrics has been created. This table provides various insights, such as when a refresh was executed, how many rows were written, and more. It is definitely worth taking a closer look at this table.

Let’s go one step further and select Materialized Lake Views, then click on Manage. This view provides an overview of all existing MLVs and shows how they depend on each other or whether they are independent.

It is also worth noting that it is possible to create an MLV on top of another MLV. In addition, this overview shows from which Lakehouse the data is sourced.

Now let’s move on to the refresh options. The default setting is Optimal Refresh. But what does that actually mean?

No refresh
If no new Delta commits are detected in the source tables, Fabric skips the refresh entirely to avoid unnecessary computation.

Incremental refresh
Only the changed data is processed when new Delta commits are detected in the source tables.

Full refresh
The entire Materialized Lake View is rebuilt from the full source dataset. This strategy is used when unsupported expressions are detected, when changes cannot be processed incrementally, or when the source dataset is small enough that a full rebuild is faster than incremental processing.

An important detail to be aware of:
If incremental refresh is intended, it must be explicitly enabled in the MLV by setting the appropriate table property.

Below is a sample SQL statement illustrating this configuration:

CREATE SCHEMA IF NOT EXISTS silver;

CREATE MATERIALIZED LAKE VIEW IF NOT EXISTS silver.cleaned_customer_data

TBLPROPERTIES (delta.enableChangeDataFeed=true)

SELECT

UPPER(Customer) AS customer_id,

UPPER(CustomerName) AS customer_name,

UPPER(PostalCode) AS postal_code,

UPPER(StreetName) AS street_name,

UPPER(CityName) AS city_name

FROM bronze.Z_CUSTOMER3;

If incremental refresh is an option, there are a few additional aspects that need to be taken into account. These are documented in the Microsoft documentation under SQL constructs, where specific limitations and requirements are described.

Optimal Refresh

In addition, it is possible to disable Optimal Refresh and instead use a scheduled refresh. To do this, select Schedule and create a new schedule.

Within the schedule, you can choose whether to refresh all Materialized Lake Views or only specific ones, which makes this approach very flexible.

You can also configure the refresh frequency: daily, hourly, weekly, monthly, or even by the minute. In addition, settings such as time zone, start and end time, as well as multiple execution times per day, can be defined.