Materialized view in fabric

Hi DiKi-I ,

Thank you for reaching out to the Microsoft fabric community forum regarding materialized views in Fabric and Also thank you tayloramy for the valuable insights already contributed.

In addition to his insight:

Materialized views are currently available in Fabric Lakehouses (Materialized Lake Views) and KQL Databases. They enhance performance by precomputing and storing query results, which is especially useful for Silver-to-Gold transformations in the medallion architecture. However, it’s important to consider factors like increased storage costs, refresh and maintenance requirements, and, in some cases, limited lineage visibility.

Reference : Overview of Materialized Lake Views - Microsoft Fabric | Microsoft Learn

Mirrored databases do not natively support materialized views. For mirrored sources, you can create standard SQL views in the SQL analytics endpoint, but materialized views are not available.

Workaround using shortcuts: You can set up a shortcut from your mirrored database to a Lakehouse. After the mirrored tables are available in the Lakehouse, Spark SQL can be used to create Materialized Lake Views on those shortcut tables. This method is effective, but keep in mind that lineage for shortcut-based tables does not currently show up in the MLV lineage UI.

Reference: Unify data sources with OneLake shortcuts - Microsoft Fabric | Microsoft Learn

The suggested approach is to start by creating a Lakehouse in Fabric, then add OneLake shortcuts that link to the necessary mirrored database tables. Next, define your Materialized Lake Views in the Lakehouse using these shortcut tables. This setup lets you integrate mirrored data into the Lakehouse, where MLVs are available, helping to improve query performance and streamline Silver-to-Gold transformations, while keeping in mind current limitations like lineage visibility.

Hope this helps. Please feel free to rech out for any further questions.

Thank you .

Hi DiKi-I ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.