Lakehouse Data Not Reflected via SQL Analytics Endpoint - Solution 1

[odtJitendra]

Problem Description

Every Fabric Lakehouse automatically provisions a SQL Analytics Endpoint (SAE) that allows the data written into Delta tables to be queried using T-SQL. In our pipelines, we write data into a Lakehouse (via notebooks, Dataflows Gen2, or pipeline Copy activities) and then immediately trigger a downstream activity — typically a Copy or Stored Procedure activity — that reads from the Lakehouse through the SQL Analytics Endpoint to insert that data into a Fabric Warehouse. We have repeatedly observed that when the downstream activity runs immediately after the Lakehouse write, it either inserts stale (old) data, misses newly added rows, or fails to pick up schema changes such as new/renamed columns. The Warehouse ends up out of sync with the Lakehouse even though the pipeline reports success on every activity.

Workaround A — Force an on-demand metadata sync before reading

Before the downstream Warehouse-load activity runs, explicitly trigger a metadata refresh on the SQL Analytics Endpoint (via the Fabric REST API, or the 'Refresh' action in the SAE explorer) and poll until the sync status reports complete, rather than assuming the data is ready.

Anyone facing the same ?

[ShivekMaharaj]

Hi @odtJitendra,

Yes, I have seen this pattern as well. The upstream Lakehouse write can complete successfully before the SQL analytics endpoint has finished synchronizing the latest Delta metadata and data state.

For a production pipeline, I would avoid relying on an arbitrary delay where possible. I would structure the orchestration as:

1. Write or transform the Lakehouse table.
2. Run the Refresh SQL Endpoint activity against that Lakehouse SQL analytics endpoint.
3. Continue only after the refresh activity completes successfully.
4. Then run the Copy activity that reads from the SQL analytics endpoint into the Warehouse.

The Refresh SQL Endpoint activity is intended for exactly this type of dependency, where a downstream step must see a deterministic Lakehouse state rather than waiting for the normal background synchronization.

I would also distinguish between two cases:

• Schema or metadata changes, such as new columns, removed columns or new tables: refresh the SQL analytics endpoint metadata before the downstream read.
• Data-only changes to an existing table: these should normally become visible automatically, but an explicit refresh still gives the pipeline a controlled synchronization point when immediate consistency is required.

A few additional checks may help if the refresh still does not expose the expected state:

• Confirm that the write transaction has fully committed before the refresh starts.
• Make sure the source is a registered Delta table under the Lakehouse Tables area, rather than only Parquet files under Files.
• Check the Refresh SQL Endpoint activity output. A successful pipeline activity and a successful endpoint synchronization are related but not identical; the activity output indicates whether unsynchronized changes were actually processed.
• Avoid running the refresh while another notebook or ingestion process is still writing to the same table, because concurrent writes can cause an intermittent refresh failure.
• For troubleshooting, compare the row count and schema directly in the Lakehouse and SQL analytics endpoint before starting the Warehouse copy.

If you are using the REST API instead of the pipeline activity, I would also wait for the long-running refresh operation to reach a terminal success state rather than treating the initial API response as proof that synchronization has completed.

So your workaround is valid, but I would formalize it as an explicit pipeline dependency:

Lakehouse write → Refresh SQL Endpoint → Warehouse load

That is more reliable than assuming the SQL endpoint is immediately consistent or adding a fixed sleep interval.

[odtJitendra]

Thank you, yes you are right and we are folowing the same. 

[Murtaza_Ghafoor]

Hi @odtJitendra,

Based on your scenario, i recommend using sql enpoint refresh activity in your pipeline and apply on success instead of completion step, this will give you the opportunity to get the latest data in your lakehouse.Thanks  

If this helps, ✓ Mark as Kudos | Help Others

[tayloramy]

Hi @odtJitendra, 

This is known behaviour. After loading data to a lakehouse, I recommend you do a metadata sync, either though the REST API, or using the Pipeline activity to ensure that your SQL Endpoint is up to date.  

[v-aatheeque]

Hi @odtJitendra 

Yes, this behavior has been observed by multiple users. The SQL Analytics Endpoint (SAE) does not always synchronize immediately after data is written into a Lakehouse. Even though the upstream write activity completes successfully, there can be a short delay before newly added rows, schema changes, or metadata updates are reflected in the SAE. As a result, downstream T‑SQL operations may occasionally read stale data or miss schema changes if they execute right after the write.

Your workaround of triggering an explicit SQL Analytics Endpoint metadata refresh and waiting for the synchronization to complete before running the downstream activity is a valid approach. Alternatively, adding a retry mechanism or a short delay before querying the endpoint can also help mitigate the issue.

If this behavior continues to impact your production workloads despite these workarounds, it's recommended to open a Microsoft Fabric Support and Status | Microsoft Fabric  so the Fabric team can investigate whether the synchronization delay is longer than expected for your environment.

Useful Links : SQL Analytics Endpoint Metadata Sync - Microsoft Fabric | Microsoft Learn