SP in warehouse is not fetching the updated table from Lakehouse during the pipeline run

[Alwin-Raj]

Hi,

As mentioned in the subject, when the Stored Procedure gets executed in the pipeline run, it is not fetching the updated table from Lakehouse. When I manually execute the Stored Procedure, it is working properly. I am stuck with this issue from the past week. Instead of fetching the updated table, it is retrieving data from the old table, because of this issue the data is not getting updated/inserted properly. It is happening only through pipeline run. Can someone help ? does anyone faced the same issue ?

[V-yubandi-msft]

Hi @Alwin-Raj ,

@giupegiupe ,has shared a valid link, and everything is functioning correctly now. Could you please review it and confirm.

If the issue is resolved, we kindly request you to share the solution or key insights here to assist others in the community. If we don’t receive an update, we will proceed with closing this thread.

For any future assistance, feel free to reach out via the Microsoft Fabric Community Forum and create a new thread. We would be happy to help.

Thank you for your cooperation and participation.

[giupegiupe]

Hi V-yubandi-msft

The solution for me:

• Run a data consistency check in the Lakehouse before the stored procedure execution to ensure that the data is in the expected state.

Fix SQL Analytics Endpoint Sync Issues in Microsoft Fabric – Data Not Showing? Here's the Solution!

The thing I find “disarming” is that the same thing does not happen between lakehouses in the same workspace but happens between a lakehouse and a warehouse connected by shortcuts.

I have not tried with gen2 and paython to see if it is possible to have the same kind of problem but I imagine that if the lakehouse remains in “process” these two "solutions "could also fail if one does not apsect the lakehouse to be “consistent”

[Alwin-Raj]

Hi, we have tried so many ways to tackle the issue, but nothing really helped. We have implemented a fix suggested by the MS support team as well, it has significantly reduced the issue but not solved the problem fully. Still sometimes this issue is coming. I can see a link related to the fix shared by you,but the link seems to be broken, could you share the link again ?

[giupegiupe · Accepted solution]

Hi Alwin-Raj 

Sorry....I will try again to post the link
The code
Fix SQL Analytics Endpoint Sync Issues in Microsoft Fabric

and the Video
https://youtu.be/toTKGYwr278?feature=shared

I am checking the situation and I also had days when the datalake update process (still under capacity free) took 9 minutes. I am trying to figure out if the shortcut link between a warehouse and a lakehouse have this kind of problem and therefore it is better to deal with the warehouse update by other methods.

[V-yubandi-msft]

Hello @Alwin-Raj ,

we wanted to check in as we haven't heard back from you. Did our solution work for you? If you need any more help, please don't hesitate to ask. Your feedback is very important to us. We hope to hear from you soon.

Thnak You.

[V-yubandi-msft]

Hi @Alwin-Raj ,

As mentioned by @AlexanderPowBI , it appears there may be an issue with the SQL endpoint, which is currently experiencing performance-related problems.

@giupegiupe , you mentioned that you ["put in 5 minutes, and it seems to be working, but I'll wait a few days to confirm it."] Could you please provide an update on this?

@Alwin-Raj , if you are still encountering any issues, please inform us. If your issue has been resolved, kindly share your solution here to assist other members. Additionally, marking it as the Accepted Solution will make it easier for others with similar issues to find the answer.

Regards,

Yugandhar.

[giupegiupe]

If the problem was in the delay (I put in 5 minutes and it seems to be working), but I'll wait a few days to confirm it, then it would mean that there should be something telling me that the lakehose update is finished (the "History table select" seems to me to be a little too shallow a log).

The uncertainty of the update leads to time dilation and is probably due to asynchronism of activities that remain in the queue either due to capacity or internal fabric (azure) cause.

It seems that the termination of a pipeline does not indicate certainty of the termination of tasks that have been requested, particularly on data whether Lakehouse, Warehouse, or SQL DB, that give the impression of being asynchronous with respect to the pipeline task that has them

[giupegiupe]

If the problem was in the delay (I put in 5 minutes and it seems to be working), but I'll wait a few days to confirm it, then it would mean that there should be something telling me that the lakehose update is finished (the "History table select" seems to me to be a little too shallow a log).

The uncertainty of the update leads to time dilation and is probably due to asynchronism of activities that remain in the queue either due to capacity or internal fabric (azure) cause.

It seems that the termination of a pipeline does not indicate certainty of the termination of tasks that have been requested, particularly on data whether Lakehouse, Warehouse, or SQL DB, that give the impression of being asynchronous with respect to the pipeline task that has them

[V-yubandi-msft]

Hi @Alwin-Raj ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we provided for your issue worked for you  or let us know if you need any further assistance?

Your feedback is important to us, Looking forward to your response. 

Thank You.

[AlexanderPowBI]

I've had the same issue, and I'm quite sure it has to do with the SQL endpoint issue. I believe I have solved it in the way i have written here.

However, as stated in that script, its "unsupported, don't use it" etc. However, I decided to use in the way described in my post, as I see no other solution until MS comes with the official API for this.

//Alexander

[nilendraFabric]

Hello @Alwin-Raj 

Could you please tell me the full flow of your pipeline.

The issue is likely caused by synchronization delays between Lakehouse updates and their reflection in SQL Analytics Endpoints. Implementing a delay or forcing metadata refreshes are effective solutions to ensure that your stored procedure fetches updated data during pipeline runs.

but before I commit something please let me know the full flow.

[Alwin-Raj]

it is medallion architecture based pipeline where the bronze layer will have raw data in parquet after some validations and cleansing. then this data will be upserted into silver in tables and it is consumed by gold after running a SP with necessary transformations. the problem is when we run the SP through pipeline it is not fetching the updated data from silver.

Already tried implementing delay, isolation levels, execution plan recompile, CTEs to retrieve latest data from silver etc. but nothing helped

[V-yubandi-msft]

Hi  @Alwin-Raj , 

Thank you for reaching out to the Microsoft community and providing the details. Thanks to @nilendraFabric  , for gathering more information from the user.It seems like you're facing an issue where the stored procedure in your pipeline isn't fetching the updated data from the silver layer.

Here are some steps you can try to fix this.

• Run the command REFRESH TABLE silver table before executing the stored procedure. This helps in making sure the stored procedure recognizes the latest data in the silver layer.
• Introduce a brief delay before running the stored procedure in the pipeline. This allows the updates in the silver layer to sync properly.
• Use the command precompile on the stored procedure to avoid any issues with cached execution plans.
• Ensure the updates to the silver layer are fully committed before executing the stored procedure. This helps in maintaining data consistency.
• Check the execution order in your pipeline to make sure all steps are executed in the correct sequence without any dependencies causing delays.

 Medallion Architecture Overview:

If my response solved your query, please mark it as the Accepted solution to help others find it easily.

And if my answer was helpful, I'd really appreciate a 'Kudos'.

[Alwin-Raj]

I have tried everything except your 4th point, but nothing helped. 4th one, I can't do because it will be break the pipeline flow. It was working fine with when I was doing historical load even with prod, but now it's not working when I started the incremental load

[V-yubandi-msft]

Hi @Alwin-Raj ,

Thanks for your response. Since this issue started after switching to incremental load, it’s possible that the stored procedure is running before the silver table updates are fully committed.

Here are some additional checks you can try.

• Ensure Data is Available Before Execution - If your stored procedure runs before the incremental load completes, it might be fetching old data. Try introducing a verification step before executing it.
• Force Query Recompilation - If the stored procedure is using a cached execution plan, it may not recognize the latest data. You can try using OPTION (RECOMPILE) in your stored procedure to force it to run with the latest data.
•  Transaction Isolation Level - If concurrency issues are causing the stored procedure to read old data, try setting SET TRANSACTION ISOLATION LEVEL READ COMMITTED before execution.
•  Pipeline Dependency Order - Double-check the execution order in your pipeline to ensure the stored procedure runs only after the incremental load process has fully completed.

If the issue persists, could you provide more details on how the incremental load is structured? That will help in pinpointing the root cause more accurately.

Regards,

Yugandhar.

[giupegiupe]

It does not refresh the data with store procedure via pipeline instead it refresh executed manually works.

My process involves a store (multiple stores) updating data, via pileline, in a warehouse taking it from a lakehouse in the same workspace.

The table it takes from, at least in my process is communally always full, so at the limit it should load old data, if it were a refresh problem, instead it seems to be pretending to read and write data. A 5-minute wait doesn't help it either.

Any ideas besides the refresh ?

[V-yubandi-msft]

Hi @giupegiupe ,

Thank you for providing more details on the issue. It seems like there might be some additional factors at play. Let's try a few more steps to troubleshoot and resolve the problem.

• Verify that the data in the Lakehouse is fully committed before the stored procedure runs to prevent accessing outdated data.
• Adjust the transaction isolation level of the stored procedure to ensure it reads the latest committed data. Try setting it to "READ COMMITTED" or "SNAPSHOT."
• Enable detailed logging for the pipeline to capture more information about the execution process and identify where the issue might be occurring.
• Run a data consistency check in the Lakehouse before the stored procedure execution to ensure that the data is in the expected state.
• Implement a mechanism to force a data refresh in the Lakehouse before running the stored procedure, using a refresh command or a custom script.
• Review the logic in your stored procedure to ensure there are no conditions or filters that might be causing it to access old data.

Please try these steps and let me know if they help resolve the issue.

Regards,

Yugandhar.

[Alwin-Raj]

Already tried adding delay, but the result was just random. Sometimes it works properly sometimes not. For the past 2 days, I didn't encounter the same issue. I didn't implement anything new, it's all same as before, I think the issue was related to the metadata sync in SQL endpoint of the Lakehouse, Looks like now they have fixed that issue