Warehouse using a shortcut on a Lakehouse Table. Issues with Refresh

[grisensko]

Hi,

I have a Pipeline filling a Lakehouse table using a Copy Activity : Lakehouse File -> Lakehouse Table

On success, I query a Stored Proc in a Warehouse that use the Lakehouse Table. to fill a warehouse table : Example : select * into WarehouseTable from LakehouseTable

However, when the Pipeline execute and doesn't wait between both operation, the Warehouse Stored Proc see an Empty Table via it's link with a row count of 0.

Il I add a wait 60 sec. the Warehouse can then see the Lakehouse Table contents.

Why I am seing old data?

How are the shortcut propagation handled? Is there a way to force refresh the warehouse link?

We are trying to build automated  future proof ETL... adding random wait is not a real solution...

[AndyDDC]

Hi @grisensko is the Warehouse loading procedure selecting from the Lakehouse SQL Endpoint? If so then there can be a delay synchronising the lakehouse table metadata with the lakehouse sql endpoint.  Eg loading a lakehouse table triggers a metadata sync with the lakehouse sql endpoint and this could take a few seconds or more. The lakehouse sql endpoint may not instantly see the lakehouse table changes (which I believe is what you are seeing).

[grisensko]

Yes, this is exactly what I'm doing. I tested directly into the SQL Endpoint and I have the same behavior than in the Warehouse.

I did a select count(*) in my table. The table is created and appear when the copy activity end, but return 0 records, it take aroung 30 sec. in my current setup for the select count(*) on the endpoint to return the new records count.

Is there a way to sync/wait for the data to be synched? To me it's a bug and there is propably a ton of persons right now trying to orchestrate bronze/silver/gold transformation in different layers and not realizing they run their SQL query in the Gold Layer on their "yesterday" Silver snapshot.

I'll test with A Warehouse instead of using a Lahouse Table.

We started the Fabric Journey with all Lakehouse, and Notebook. But with Notebook startup time, capacity limit and now SQL Endpoint delay, i have the feeling we will end with All Warehouse and SQL.

[v-cboorla-msft]

Hi @grisensko 

Thanks for using Microsoft Fabric Community.

Apologies for the inconvenience.

Following up to see that if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. 

Please do let us know if the issue still persists.
Otherwise, will respond back with the more details and we will try to help.

Thanks.

[v-cboorla-msft]

Hi @grisensko 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread.

Thanks.

[grisensko]

I couldn't find a way around the issues by using Lakehouse. I have replaced most/all Lakehouse component by Warehouse. So far, it appear Warehoouse offer ACID properties.

However I'm facing the other problem where Warehouse link need to come from the same workspace.. so I have no choice to rely on Lakehouse to integrate data from other data domain. And rely on schedule / long wait to trigger next activity because I cannont rely on lakehouse synchronisation.