Forum Discussion
View suddenly got brocken
Hello fellows, we use Views in our Fabric lakehouses in order to overcome some PowerBI limitations and for better data management. Today we noticed a view that produces bad results. Using the query from the view definition has no any issues. Recreating the View with the same definition from the "CREATE or ALTER" query in the SQL Endpoint repairs the view. This is happening for second time in a 12 months period and we don't have any explanation what causes it since we have not touched the lakehouse at all since its creation apart of fixing those issues.
Does any of you experienced such problems?
Thankyou, svenchio for your response.
Hi skirchev,Thankyou for the followup.
Based on my understanding, since your data is ingested via Synapse Link using near real time replication and not via a pipeline, there is no deterministic “ingestion completed” point at which a refresh can be reliably triggered. The behaviour you are observing might be caused by read consistency timing between ongoing Synapse Link Delta commits and the SQL Analytics Endpoint, which reads snapshot data from the Lakehouse. During or shortly after continuous updates, persisted views can temporarily evaluate against an intermediate snapshot and return unexpected results such as NULLs, even though the same logic functions correctly in ad hoc queries.
Recreating the view forces recompilation, which explains why it resolves the issue. The Refresh SQL Analytics Endpoint Metadata API might be intended for schema changes only (for example, new columns or data type changes) and is not designed to be called after continuous Synapse Link data updates, as there is no supported automatic trigger for those events.
If this behaviour recurs or impacts production workloads, we kindly request you to open a support ticket so that the backend engineering team can review SQL Endpoint metadata behaviour using the link:Microsoft Fabric Support and Status | Microsoft Fabric
Additionally, please refer to the links provided below:
What is the SQL analytics endpoint for a lakehouse? - Microsoft Fabric | Microsoft Learn
SQL Analytics Endpoint Performance Considerations - Microsoft Fabric | Microsoft LearnWe hope the information furnished herein helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.
Thank you.
6 Replies
v-pnaroju-msftCommunity Support
Thankyou, svenchio for your response.
Hi skirchev,Thankyou for the followup.
Based on my understanding, since your data is ingested via Synapse Link using near real time replication and not via a pipeline, there is no deterministic “ingestion completed” point at which a refresh can be reliably triggered. The behaviour you are observing might be caused by read consistency timing between ongoing Synapse Link Delta commits and the SQL Analytics Endpoint, which reads snapshot data from the Lakehouse. During or shortly after continuous updates, persisted views can temporarily evaluate against an intermediate snapshot and return unexpected results such as NULLs, even though the same logic functions correctly in ad hoc queries.
Recreating the view forces recompilation, which explains why it resolves the issue. The Refresh SQL Analytics Endpoint Metadata API might be intended for schema changes only (for example, new columns or data type changes) and is not designed to be called after continuous Synapse Link data updates, as there is no supported automatic trigger for those events.
If this behaviour recurs or impacts production workloads, we kindly request you to open a support ticket so that the backend engineering team can review SQL Endpoint metadata behaviour using the link:Microsoft Fabric Support and Status | Microsoft Fabric
Additionally, please refer to the links provided below:
What is the SQL analytics endpoint for a lakehouse? - Microsoft Fabric | Microsoft Learn
SQL Analytics Endpoint Performance Considerations - Microsoft Fabric | Microsoft LearnWe hope the information furnished herein helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.
Thank you.
To narrow down the issue: the problematic part of the view is "CASE WHEN b.dt_clientid IS NULL THEN 'No' ELSE 'Yes' END AS intragroup" which was producing sometimes NULLs, while it shouldn't. Recreating the view with exactly the same definition did the magic. The underlying tables are from custom static table and tables from synapse link to dynamics F&O over Power Platform.
- svenchio
Super User
Hi skirchev, I did some research about what happends between SQL Analytics Endpoint and Lakehouse as your description & fix suggest some sort of "unsync", and yes — you’re not the only one seeing this weird view behavior in Microsoft Fabric lakehouses!
There isn’t an official documented bug publicly listed yet, but based on what we know and typical patterns in SQL/analytics engines, this is what's likely happening: The SQL endpoint sits on top of the Lakehouse and periodically syncs table schemas/metadata. It’s read‑only over your Delta tables, and there can be lag or even temporary discrepancies after upstream writes, schema drift, or shortcut updates, in other words, the endpoint may be a few seconds to minutes behind the Lakehouse and can require an explicit refresh to catch up.
This could also explain why MSFT is introducing a Refresh SQL analytics endpoint Metadata REST API (Preview) "We’re excited to announce that the long-awaited refresh SQL analytics endpoint metadata REST API is now available in preview. You can now programmatically trigger a refresh of your SQL analytics endpoint to keep tables in sync with any changes made in the parent artifact, ensuring that you can keep your data up to date as needed."
With Link to Fabric / Synapse Link, the Lakehouse relies on shortcuts to Dataverse/Dynamics and since shortcut layer and its metadata sync can change independently, If that updates while your SQL endpoint is idle or not yet synchronized, you can see mismatches until the endpoint catches.Perhaps it would it be a good idea to include as part of your ingestion routine a call the Refresh SQL Endpoint Metadata API right after Dataverse/Dynamics batches land or after your Fabric pipeline finishes writing to the Lakehouse 😉 to prevent this happening in the future 😁Hope you find this information useful, if so, I would appreciate a kudos and to mark this as a solution to your questions... wish you the best of lucks and reach out if there's any additional observations that supports/rejects there conjectures.
Hi svenchio , first thank you for the answer. I thought over your idea to include as part of my ingestion routine a "call the Refresh SQL Endpoint Metadata API right after Dataverse/Dynamics batches land or after the Fabric pipeline finishes writing to the Lakehouse". But we do not have any pipeline to ingest data. The data is being ingested over a synapseLink and as of my knowledge the process is a kind of a near real time updates to fabric tables, then at which point should we call the Refresh API endpoint.
- v-pnaroju-msft
Hi skirchev,
We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.
Thank you. - v-pnaroju-msft
Hi skirchev,
We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.
Thank you.
