Warehouse data not refreshing when pulling from Lakehouse

[marius1106]

Dear Microsoft,

for a Customer's process I have a TSQL-Notebook running which loads data from his Lakehouse to his Warehouse. When I run the notebook manually, the data in the Warehouse is updating perfectly like it should. But when I schedule it, the monitoring hub says that the notebook is running sucessfully but it doesn't pull any data. 

I could not explain why it doesn't pull the data. I added a refresh statement for the Lakehouse table (See first code line) but it didn't help. Is the tsql Notebook maybe the wrong way to go because it doesnt update the lakehouse tables? Should I use Views in the warehouse instead?

The Notebook looks like this:

-- Update Datasource

select top 5 * from (select * from [Bronze_LH].[dbo].[report_fulldump]) a OPTION (LABEL = 'ForceRefresh');

drop table if EXISTS dbo.report_fulldump ;

GO;

Create table dbo.report_fulldump (

    RAW_DATA Varchar(max)

);

GO

INSERT INTO dbo.report_fulldump (RAW_DATA)

SELECT

    CAST(

        ISNULL(CAST([Raw_data1] AS VARCHAR(MAX)), '') + '' +

        ISNULL(CAST([Raw_data2] AS VARCHAR(MAX)), '') + '' +

        ISNULL(CAST([Raw_data3] AS VARCHAR(MAX)), '')

    AS VARCHAR(MAX)) AS RAW_Data

FROM [Bronze_LH].[dbo].[report_fulldump];

GO

Thanks!

[v-pnaroju-msft · Accepted solution]

Hi marius1106,

Based on my understanding, I recommend first copying the raw data into a Warehouse table and then applying T-SQL transformations using views or additional logic. This approach helps avoid delays associated with the SQL endpoint and ensures that you are working with the most recent data in a stable environment that is optimised for transformations.

Please note that Notebooks may fail, as T-SQL Notebooks rely on the Lakehouse SQL analytics endpoint, which can experience metadata synchronisation delays. These delays may affect timely access to data, even for existing tables.

Other alternatives, such as introducing wait or delay logic, may provide some relief but are not entirely reliable. Similarly, using a view over Lakehouse tables is a potential solution, but it may also be impacted by the same metadata sync issues.

If you find this response helpful, we kindly request you to mark it as the accepted solution and consider giving kudos. This will help other community members who may have similar queries.

Thank you.

[marius1106]

Hi @v-pnaroju-msft ,

Thank you very much for your help, now I understood!!! 🙂

[v-pnaroju-msft]

Thankyou, @Akash_Varuna , @ObungiNiels ,for your response.

Hi @marius1106,

Based on my understanding, you should not face the same issue when pulling data from a Lakehouse using a Data Pipeline. Unlike the T-SQL notebook, which depends on the SQL Analytics Endpoint (where delays may occur due to metadata syncing), a Data Pipeline’s Copy activity directly accesses the Lakehouse Delta tables via OneLake. This eliminates the sync delay and ensures that the Warehouse receives the latest data.

Additionally, please refer to the links below for more details:

Better together - the lakehouse and warehouse - Microsoft Fabric | Microsoft Learn
SQL analytics endpoint performance considerations - Microsoft Fabric | Microsoft Learn

If you find our response helpful, kindly mark it as the accepted solution and give kudos. This will help other community members with similar queries.

Thank you.

[marius1106]

Hi @v-pnaroju-msft ,

thank you for your response. Unfortunately I can't migrate all my TSQL-Notebooks into Pipelines so that it all works with the Lakehouse-delay. 

Is there another possibility to implement my TSQL-Transformations and save it into a Table/View like in the Code above and get the actual data from a Lakehouse?

I think of workarounds like: 

A) First copy all the raw data to a Warehouse and than transform it there in a View-definiton

B) Add something like a time.sleep() Command or a refresh-command into the notebook/pipeline

C) Put the code above into a View-definiton instead of a TSQL-Notebook

Is there one working workaround in my 3 ideas? And how would you implement it then?

Thank you!

[ObungiNiels]

Hi @marius1106 ,

since you're pulling data from a lakehouse via the SQL endpoint, you are likely falling victim to the delay of the SQL endpoint of lakehouses. This is know behavior and documented here: SQL analytics endpoint performance considerations - Microsoft Fabric | Microsoft Learn

You can force a sync of the SQL endpoint after your update job is done which will ensure that the warehouse can fetch the new data. This is well described in this article: Workaround for delays in the automatically generated schema in the SQL analytics endpoint of the Lak...

Please let me know if this resolves your issue.

Kind regards,

Niels

[marius1106]

Hi @ObungiNiels 

Between my data-loading process (to the Lakehouse) and the pulling-process from the Lakehouse there are 20 minutes. I dont know if its still the delay to create a table?

[ObungiNiels]

In my experience, it does also affect appended/supserted data in existing tables. Since the SQL endpoint is serverless, it doesn't update immediately and I've seen scenarios where the delays surpasses 20 minutes. 

I'd give an API call to sync the SQL endpoint after ingesting data to the lakehouse a try. 🙂 

[marius1106]

Okay 🙂

Do I have the same Problem when I pull data from a Lakehouse using a Data Pipeline?

[Akash_Varuna]

Hi @marius1106 Since it works while running manually check if the Lakehouse tables are updating before execution. Also try using a Materialized View or directly querying the Lakehouse.

[marius1106]

The Update-Job for the Lakehouse data runs before pulling from it so it should be acutal at that time. 

I think it could be a problem of the tsql-notebook which doesnt take the latest data 🤔