Forum Discussion

mariussve1's avatar
mariussve1
Solution Sage
5 months ago
Solved

Possible delay between Lakehouse updates and SQL endpoint in Fabric pipeline?

Hi,

I’m experiencing something that looks like a timing / consistency issue in a Microsoft Fabric pipeline and I’m curious if others have seen the same behavior.


The pipeline pattern is roughly like this:

  1. REST API → load data into a Lakehouse Delta table

  2. Query the data via a SQL endpoint view in the Lakehouse

  3. Copy the result into a Warehouse table

The pipeline runs sequentially, so step 2 starts immediately after step 1 finishes.


The issue is that some columns appear with older values in the Warehouse, even though they are already updated in the Lakehouse.


To troubleshoot this, I checked the following:

  • The raw Delta table in the Lakehouse contains the correct updated value

  • The Lakehouse SQL view also returns the correct value when I query it manually after the pipeline run

  • However, the Warehouse table created by the pipeline still contains the previous value

There are no missing rows and no transformation logic involved. The mapping is a simple column-to-column copy.

Because of this, I’m starting to suspect that the pipeline step reading from the SQL endpoint might be reading an older snapshot of the Delta/Parquet data, even though the previous pipeline activity already finished writing the updated data.

In other words, it feels like the SQL endpoint might not always be fully synchronized with the Lakehouse immediately after a write operation.


My questions:

  • Has anyone experienced something similar in Fabric pipelines?

  • Is there a known delay between Lakehouse Delta updates and the SQL analytics endpoint?

  • Are there recommended patterns to avoid this (for example waiting, forcing metadata refresh, or reading directly from the Delta table instead of the SQL endpoint)?


Any insights or experiences would be appreciated.

Thanks!

data engineering
data pipeline
lakehouse

2 Replies

  • tayloramy's avatar
    tayloramy
    Super User
    5 months ago

    Hi mariussve1, 

     

    This is a known thing, the SQL endpoint of the lakehouse does not update immediately.

     

     

    From the Microsoft docs: 

    SQL Analytics Endpoint Performance Considerations - Microsoft Fabric | Microsoft Learn

    Tables in the SQL analytics endpoint are created with a minor delay. Once you create or update Delta Lake table in the lake, the SQL analytics endpoint table that references the Delta lake table is created/refreshed automatically.

    The amount of time it takes to refresh the table is related to how optimized the Delta tables are. For more information, review Delta Lake table optimization and V-Order to learn more about key scenarios, and an in-depth guide on how to efficiently maintain Delta tables for maximum performance.

     

    You can use the REST API to force an update to the lakehouse in your pipeline, and that should resolve your issue: 
    Refresh SQL analytics endpoint Metadata REST API (Generally Available) | Microsoft Fabric Blog | Microsoft Fabric

     

     

     

    • mariussve1's avatar
      mariussve1
      Solution Sage
      5 months ago

      Hi tayloramy,

      Thanks for the clarification and for pointing me to the documentation.


      I ended up implementing the       Refresh SQL analytics endpoint metadata API as part of the pipeline to make sure the SQL endpoint is synchronized before the next step reads from it.


      The pattern I used is roughly:

      1. Call the refreshMetadata endpoint using a Web activity (POST).

      2. The API returns a long-running operation id.

      3. The pipeline then polls the operation status using GET /v1/operations/{operationId} inside an Until activity.

      4. When the status becomes Succeeded, the pipeline continues.

      So essentially the pipeline now waits until the SQL analytics endpoint refresh is completed before the next activity reads from it.


      High level it looks like this:

       

       


      This seems to resolve the issue where the SQL endpoint sometimes returned older values immediately after the Lakehouse write.



      Thanks again for the help!