Materialized view in fabric

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.

can it be created for mirror databases in fabric?

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.

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

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.

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.

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.