Microsoft Fabric Materialized Lakehouse Views

Materialized Lakehouse Views (MLV) is a new feature (available in public preview) that allows you to build declarative data pipelines using SQL, with built-in data quality checks, performance optimisation and automated visualisation and monitoring of the transformation process.

Let’s assume we have a data platform using the medallion architecture. To move the data through the different layers, a data engineer would traditionally create data pipelines to trigger/ orchestrate the data movement, develop notebooks for each stage, implement data quality checks and monitor the process through the Monitor Hub. Even if you leverage accelerators and metadata driven approaches, this process can be time-consuming. MLV aims to simplify this process by providing the features below.

Using a declarative SQL statement, the data engineer defines the transformation logic by applying aggregations, projections or filters. In the example below, we provide the logic required to clean and enrich customer data.

It is possible to build the data quality checks as part of the pipeline. Based on the configuration it can drop the records that violate the constraint or fail by flagging the MLV as having errors. In the example below we have configured the MLV to drop the records that contains customers without a name.

As we can see, 38 records have been dropped.

Fabric understands the dependency between the MLV and the source tables and the dependencies between bronze, silver and gold layers. It automatically generates a directed acyclic graph (DAG) of the pipeline and tracks its performance and status. As seen in the screenshot below, the DAG is automatically created and shows how the data flows.

Fabric MLVs stay updated based on the refresh cycle defined by you in the Schedule.

Every time a MLV refreshes based on the schedule, Fabric automatically logs the status and quality metrics.

MLVs also generates a visual report that shows trends for easy troubleshooting.

Any changes to the bronze data will be reflected in Silver and Gold after the next refresh.

Before refresh

Refresh occurred at 8:47 AM

We can drop, refresh and rename MLVs. We can also list all MLVs available in a particular schema. Please note that the changes will reflect only in the next refresh and if the changes have an impact on other MLVs, it will break the lineage hence it’s important to update the dependencies before the next refresh.

MLV introduce a transformative approach to Microsoft Fabric’s data platform. They allow you to build sophisticated data pipelines using simple SQL, with automated handling of the underlying complexity. The result: faster development for data engineers, more reliable data for analysts, and accelerated insights for the business.