Building reliable data pipelines often involves multiple transformation steps, from ingesting raw data to cleaning it, applying business rules, and preparing it for reporting. Traditionally, data engineers manage these steps using notebooks, Spark jobs, and orchestration pipelines.
Microsoft Fabric introduces Materialized Lake Views (MLVs) to simplify this process. With MLVs, data engineers can define transformations declaratively using SQL or PySpark, while Fabric manages materialisation, refreshes, dependencies, and data quality.
In this article, we'll explore how Materialized Lake Views work, their key features, and how they can help simplify data engineering in Microsoft Fabric.
What Are Materialized Lake Views?
A Materialized Lake View is a persisted data transformation in a Fabric Lakehouse. Unlike a standard view, which typically executes its underlying query when accessed, an MLV stores the transformation results as a Delta table.
This means downstream users and applications can query the materialised results without repeatedly executing the original transformation.
For example, imagine you have a raw sales dataset containing thousands or millions of transactions. You want to clean the data, standardise column types, remove invalid records, and calculate sales totals by region.
Instead of manually orchestrating every transformation through separate Spark jobs, you can define a series of Materialized Lake Views and let Fabric manage their execution and refresh dependencies.
How Materialized Lake Views Work
Materialized Lake Views follow a declarative approach. You describe the transformation you want, and Fabric handles the underlying processing and storage.
The process involves four main stages:
1. Create
Define the transformation using supported SQL statements or PySpark authoring capabilities. Fabric stores the definition and materialises the output as a Delta table.
2. Refresh
When the source data changes, Fabric determines the appropriate refresh strategy. Depending on the circumstances, it can process only changed data, rebuild the results, or skip refreshing when no changes are detected.
3. Query
Once materialised, the output can be queried like a regular Lakehouse table by supported Fabric engines and downstream consumers with the necessary permissions.
4. Monitor
Use Fabric's monitoring and lineage capabilities to inspect refresh history, execution status, data quality metrics, and dependencies between views.
This approach reduces the amount of custom orchestration code data engineers need to maintain.
Materialized Lake Views and the Medallion Architecture
One of the most useful applications of Materialized Lake Views is implementing the medallion architecture.
In a typical medallion architecture, data progresses through three layers: Bronze, Silver, and Gold.
- Bronze layer: Ingests raw data from source systems or files.
- Silver layer: Cleans, validates, and transforms the data into a consistent format.
- Gold layer: Produces business-ready datasets, aggregations, and reporting tables.
With Materialized Lake Views, you can define these transformations as a connected series of views.
For example, a Bronze view could ingest raw sales files, a Silver view could validate transaction amounts and standardise data types, and a Gold view could calculate total revenue by product and region.
Fabric tracks the dependencies between these views and manages their refresh order. When an upstream view changes, the dependent views can be processed in the correct sequence.
This makes the medallion architecture easier to maintain and helps reduce the need to build custom orchestration logic for every transformation.
Automatic Refresh Optimisation
Refreshing large datasets can consume significant processing resources, particularly when transformations involve complex joins or aggregations.
Materialized Lake Views include an automatic refresh decision engine that selects an appropriate strategy based on source changes and supported query patterns.
The three main strategies are:
- Incremental refresh: Processes new or changed data rather than rebuilding the entire result.
- Full refresh: Recalculates the materialised result from the source data.
- Skip refresh: Avoids unnecessary processing when no relevant changes are detected.
For incremental refresh, Delta Change Data Feed (CDF) must be enabled on the relevant source tables. Without CDF, Fabric can choose between a full refresh and skipping the refresh.
This optimisation can help reduce unnecessary computation and improve the efficiency of recurring data transformations.
One important distinction is that PySpark-authored Materialized Lake Views currently support full refresh only, according to the documented capabilities.
Built-in Data Quality Rules
Data quality is another important feature of Materialized Lake Views.
Rather than writing separate validation logic in every notebook, you can define constraints directly in your transformation. These rules help enforce data requirements consistently throughout your pipeline.
For example, consider a sales table where every transaction must have a positive sales amount.
A constraint could be defined as follows:
CONSTRAINT valid_sales
CHECK (sales_amount > 0)
ON MISMATCH DROP
This rule checks that sales_amount is greater than zero and drops rows that fail the constraint.
Depending on the configured mismatch behaviour, invalid records can be dropped or cause processing to fail.
Applying data quality rules at the transformation stage helps prevent invalid records from flowing into downstream datasets and reports.
Ingesting Files Directly from OneLake
Materialized Lake Views can also simplify data ingestion.
You can use them to ingest supported CSV and Parquet files directly from OneLake into managed Delta tables. This can remove the need for a separate file-loading step implemented through a notebook, pipeline, or COPY operation.
For example, you could define a Bronze Materialized Lake View that reads raw sales files from a OneLake folder. The view could also cast columns to appropriate data types, select the required fields, and identify malformed CSV records during ingestion.
This is particularly useful when building repeatable ingestion and transformation workflows that need to fit into a medallion architecture.
Fabric also supports schema-handling options that determine how the ingestion process responds when the structure of incoming files changes.
Monitoring and Dependency Management
As data pipelines grow, understanding dependencies and identifying failed refreshes become increasingly important.
Materialized Lake Views provide built-in monitoring capabilities that help data engineers investigate pipeline behaviour.
You can inspect refresh execution status, review recent runs, monitor data quality metrics, and explore lineage between dependent views.
Fabric also provides Analytics and Insights views for examining trends such as refresh duration, success rates, recurring errors, and potential regressions.
These capabilities help teams understand what is happening across their transformations without relying entirely on custom logging and monitoring code.
For programmatic analysis, Fabric also exposes system tables under the _mlv_system schema. These tables provide operational metrics for supported monitoring scenarios and should not be modified manually.
When Should You Use Materialized Lake Views?
Materialized Lake Views are particularly useful when you have frequently accessed aggregations, complex joins, repeatable data quality rules, or multistage transformations that need consistent refresh management.
They are a good fit for SQL-centric Lakehouse pipelines where the transformation logic can be expressed declaratively.
However, they are not the right tool for every workload. One-off queries, complex Python processing, machine learning inference, external API calls, and high-frequency streaming scenarios may be better handled by notebooks, other pipeline tools, or Fabric Real-Time Intelligence capabilities.
Choosing the right tool depends on the nature of the workload and the refresh requirements.
In conclusion, Materialized Lake Views provide a practical way to simplify data engineering in Microsoft Fabric. By combining persisted transformation results with automatic refresh optimisation, dependency management, data quality constraints, and monitoring, they reduce the amount of custom pipeline logic that data engineers need to maintain.
For teams building Bronze, Silver, and Gold layers in a Lakehouse, MLVs offer a declarative approach to creating reliable and maintainable data pipelines.
Join the conversation! Your thoughts help the community grow.