Sporadic Stored Procedure issue - not loading new records

[owen-carnes-sps]

We have been live on Microsoft Fabric for about a year and a half now.  In the past week, we've had an issue come up that we haven't experienced before.  It occurred last Thursday in all three of our Fact tables in our productive Gold warehouse.  It occurred this morning in only one of our three Fact tables.

A brief overview of our relevant landscape to this issue:

We have a single Bronze Lakehouse, we have a Silver Lakehouse in a Dev workspace and a Silver Lakehouse in a Prod workspace, we have a Gold Lakehouse in a Dev workspace (mirrored tables from Silver_Dev) and a Gold Lakehouse in a Prod workspace (mirrored tables from Silver_Prod), and we have a Gold warehouse in a Dev workspace and a Gold warehouse in a Prod workspace.

The issue is that, twice in the last week in the nightly run, our Silver to Gold stored procedures (technically loading from Gold Lakehouse to Gold Warehouse) have run successfully according to the pipeline, but haven't actually updated or loaded any new records.  Both times this has occurred in Prod, whereas our Dev load has been successful in bringing in new records, and all the records are the same, stemming originally from the same Bronze lakehouse.  Our Bronze to Silver load has been successful in bringing in those new records, using a combination of CDC and full loads, depending on the table.

If we rerun the stored procedures later in the morning, without rerunning anything upstream in the pipelines and without changing anything, they successfully pull in the missing records.

Last Thursday, this was an issue in two fact tables that are using CDC to only pull in updated or new records that have been created or changed since the last load.  It also occurred in one fact table that is a daily full load.  This morning, the issue only occurred in the table that is a daily full load.

The issue did not occur on Friday, as all the tables were up to date from the nightly load.  No way of knowing if this issue occurred Saturday or Sunday, as no one was in the system validating as far as I know.

What could the cause of this be?  I want to reiterate that we've been live in these tables for over a year, running these stored procedures daily, and this hasn't been an issue until last week.

Thanks in advance for any ideas you may have!

[v-aatheeque]

Hi @owen-carnes-sps 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[v-aatheeque]

Hi @owen-carnes-sps 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

[v-aatheeque · Accepted solution]

Hi  

Thanks for reaching out to Microsoft fabric Community Forum.

Based on the behavior you described, this doesn’t look like a failure in your pipelines or stored procedures, but rather a timing/visibility issue between the Lakehouse and the Warehouse in Microsoft Fabric.

In Fabric, when data is written to the Lakehouse (Delta tables), it may not always be immediately visible to downstream consumers like the Warehouse or SQL endpoint.

This can sometimes cause a scenario where the pipeline and stored procedure run successfully. But at that exact moment, the latest data is not yet visible to the Warehouse, So the load step processes zero new rows. When you rerun later, the same query can now see the data and loads it successfully.

This aligns with your observations:

Bronze -> Silver is working and data is present.

Dev works fine (usually lower load).

Prod intermittently misses data but succeeds on rerun.

No code or config changes were made.

Recommended steps:

• Add a small delay (2–5 minutes) between Silver and Gold load steps
• Configure retry (2–3 attempts) on the stored procedure activity
• Optionally add a data availability check before running the load

  Reference: 

  Data Warehouse Tutorial: Transform Data with a Stored Procedure in a Warehouse - Microsoft Fabric |…

  Troubleshoot the Warehouse - Microsoft Fabric | Microsoft Learn
  
  

  Additonal Note :
  
  Since this behavior started recently after a long period of stability, it may also be worth raising a Microsoft Fabric Support and Status | Microsoft Fabric  ticket with pipeline run details, as it coul...

[ToddChitt]

I would go with a manual WAIT task in the pipeline between the LOADING of the lakehouse and the QUERYING of it. I usually specify about 5 minutes. It may be overkill, but 3 minutes wasn't long enough once.

Specifying Retry won't help as it only does that if the task FAILS. Sounds like it is not failing, just not finding the data (yet).

There is a new pipeline task available in preview: Refresh SQL Endpoint. You specify the Wordspace and Lakehouse name. I have done limited testing of it so do not yet have any opinion as to its usefullness.

Hope that helps.