Materialized view in fabric

[DiKi-I]

Hi, 
I want some suggestion related to materilized view in fabric? Can someone provide some guidance and benefit of using it and any limitation. Will it be effective to use it for silver to gold medallion transaformation? 

[v-tsaipranay · Accepted solution]

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 .

[v-tsaipranay · Accepted solution]

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.

[v-tsaipranay]

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.

[v-tsaipranay]

Hi @DiKi-I ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

[v-tsaipranay · Accepted solution]

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 .

[tayloramy]

Hi @DiKi-I, 

I think this might be possible, according to the docs. They don't explicitly say it is possible, but they also don't state it is not possible.

What the docs do say:

• Mirroring gives you a SQL analytics endpoint that is read-only for data changes; you can add regular SQL views there, but not data-bearing objects like materialized views. See Microsoft: Use the SQL analytics endpoint and Mirroring Azure SQL Database.
• You can create a OneLake shortcut from your mirrored database into a Lakehouse and then work with those tables in Spark. See Microsoft: Explore mirrored data with Lakehouse shortcuts and notebooks and OneLake shortcuts overview. 
• Materialized lake views are a Lakehouse feature created with Spark SQL. See Microsoft: Overview of materialized lake views and Get started: create an MLV.
• Caveat: lineage to tables reached via shortcuts is not currently supported in the MLV lineage UI. See Microsoft: Materialized lake views lineage.

Suggested pattern to try:

1. Create a Lakehouse
2. Add a shortcut to your mirrored tables
3. Define an MLV over the shortcut tables

CREATE SCHEMA IF NOT EXISTS silver;

CREATE MATERIALIZED LAKE VIEW IF NOT EXISTS silver.customer_orders_mv AS
SELECT  CustomerID, COUNT(*) AS order_count
FROM    bronze.orders
GROUP BY CustomerID;

Can you give it a try and return here with your results so the community knows? If it works in your workspace, we can add a short 'how we set it up' snippet for others to follow.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this as the solution.

[DiKi-I]

is it possible to create mlv on shortcuts? If I create a shortcut of mirror bb in lakehouse, can I create MLV?

[tayloramy]

Hi @DiKi-I, 

I don't think that is possible. I see another forum post here that seems to say it is not: 
Solved: Re: Create Materialized Lake View out of Mirrored ... - Microsoft Fabric Community

The only documentation I can find is for creating materialized views in lakehouses and KQL databases. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this as the solution.

[DiKi-I]

can it be created for mirror databases in fabric?

[tayloramy]

Hi @DiKi-I, 

When you say in Fabric, are you talking about lakehouses, warehouses, KQL databases, or SQL Databases? 

For Lakehouses, you can use materialized lake views: 
Get Started with Materialized Lake Views in a Lakehouse - Microsoft Fabric | Microsoft Learn

For KQL databases, you can also create materialized views: 
Create and edit materialized views - Microsoft Fabric | Microsoft Learn

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this as the solution.