Forum Discussion

FelixL's avatar
FelixL
Advocate II
2 years ago
Solved

Delayed data refresh in SQL Analytical Endpoint

I am currently working on moving from Synapse Serverless SQL to Fabric SQL Analytical Endpoint for reading my external Delta Lake tables (Managed and updated in Azure Synapse, and linked to Fabric Lakehouse using ADLS Gen2 shortcuts). However, I am facing issues with the data not being refreshed in a timely manner in Fabric, where it is always up-to-date the second the underlying Delta Lake table is updated when reading through Synapse Serverless. 

 

When I run my Azure Synapse pipelines and refresh my Delta Lake tables I also trigger Power BI dataset refreshes at the end of every pipeline, to load any updated data into Power BI. This works flawlessly when using Synapse Serverless as source, but fails about 50% of the time when using Fabric SQL Analytical Endpoint. The data being read from Fabric is sometimes the newest data, and sometimes old (yesterdays) data - even though the dataset refresh is triggered by the same pipeline that has updated the data, so i KNOW the data is updated at the time of refresing the dataset.

 

Adding a "wait" activity in my pipelines seem to (mostly) solve the problem. Around 10-15 minutes wait seem to do the trick. But this is never needed when using Synapse Serverless.

 

Does Fabric SQL Analytical Endpoint work differently than Synapse Serverless in some fundamental way? Does Fabric keep some kind of cache for external Delta Lake table data, that could explain why I sometimes see an up to 15 minute lagg before the SQL Analytical Endpoint is refreshed? I was hoping the endpoint would simply read the Delta Lake tables as they are, on the lake, but something else seems to be happening here, and it really is a worse experience than using Azure Synapse Sevrerless SQL for this purpose.

 

Anyone else seen similar things? 

 

Thanks,

Data Engineering
One Lake
Power BI
  • AndyDDC's avatar
    AndyDDC
    2 years ago

    So my info from Microsoft is that it should take a few seconds to a minute for the SQL Endpoint to discover the updated delta logs.  They did have an issue which was fixed apparently.

     

    If your issue persists it would be worth logging that with MS support and give them you workspace ID, lakehouse id, and time it was happening

14 Replies

    • FelixL's avatar
      FelixL
      Advocate II
      2 years ago

      Hi, I am aware of this setting and I enabling it whenever possible. But sometimes, due to data type errors or other refresh issues this setting gets automatically disabled. 

       

      And in this case I dont really case about the default Direct Lake dataset - I only want to query my Delta Lake tables with T-SQL via the SQL endpoint (something that always works flawlessly with no refresh delay in Synapse).

       

      My understanding is that the SQL endpoint is not dependent on the default dataset. Or is it? I should be able to unselect all tables from the SQL endpoint from the default dataset (and end up with an empty dataset), but still be able to query the SQL endpoint.

       

      Are you saying that im wrong, and there is a circle reference going on here? The metadata in the SQL endpoint needs to be updated in order to select what tables to include in the default dataset, but the default dataset needs to be up-to-date (reframed) in order to present data in the SQL endpoint? 😕 That doesnt make much sense, and I hope this is not the case?

       

      Im trying to understand if there is some "reframing" activity going on under the hood even for the SQL endpoint, causing the delay I am experiencing.. 

      • AndyDDC's avatar
        AndyDDC
        Most Valuable Professional
        2 years ago

        The SQL Endpoint isn't dependant on the default dataset (the other way round is true though).  OK, so just to confirm, if you query the delta data via the shortcut table using SQL via the SQL Endpoint can you see the updated data?  I've just tested now where I have a delta table being written to (overwrite mode) using a Synapse notebook, and then querying that using a shortcut in Fabric Lakehouse SQL Endpoint and I can see the updated data as soon as its written to the external data lake folder.

         

        Update:  I've also testing with a report in power bi on the default dataset and I can see the data as soon as it refreshes in the external data lake.

    • wzduniak's avatar
      wzduniak
      Regular Visitor
      1 year ago

      This issue is still there, Microsoft can you just add an ip that we can trigger using service principal credetials to manually refresh sql endpoint, there is an unofficial one but it requiers access via aad account with mfa

  • v-cboorla-msft's avatar
    v-cboorla-msft
    Microsoft Employee
    2 years ago

    Hi FelixL 

     

    Glad that your query got resolved.

    Please continue using Fabric Community for any help regarding your queries.

     

    Thanks

  • Muru_fabric's avatar
    Muru_fabric
    New Member
    1 year ago

    Hello All, 

    i am also facing an same issue. Store Procedure  are processing stale data from the lake house since there appears to be a delay between the completion of the notebooks and the data being fully available in the DW. As suggested by Microsoft support, introduced a 'wait' activity in the pipeline to try and overcome this problem. we could see that problem is wait time is less than 30 min.

     

    any long term solution such as to use Direct Lake Semantic Models in Power BI, instead of SQL endpoint ?

     

    any suggestions ?

     

    Regards,

    Murugan