Data sometimes shows in Power BI and sometimes not

[Philipexsitec]

Hello!

Im working with the following setup:

Extract of data from Business Central ERP (Cloud)
-Dataflows Gen2

-Moves this data into a staging lakehouse

-In a Warehouse i am using stored procedures where i change and add some columns to the data, i query the lakehouse sql end point in the warehouse sql stored procedure

-I then end the store procedures by dumping the "transformed" data into tables in the warehouse then i create views ontop of these for Power BI to use.

All this is orchestrated by a data factory pipeline:

Dataflows -> Stored procedures -> Semantic Model Refresh

However sometimes during the day when this is running data is missing in the Power BI report, i can clearly see it in the Lakehouse but not in the tables after the stored procedures, the problem is there is no error or anything and suddenly everything works again.

Anyone knows what can be the issue here? Is there any latency from Dataflows to the lakehouse that i need to think of before the stored procedures are run?

[ToddChitt · Accepted solution]

Yes, I have run into this in the past. If you load data into a Lakehouse table (like from a Dataflow or Pipeline) then it can take a couple of minutes before that data is persisted enough to actually query it by something like you are using (T-SQL stored procedure in a Warehouse).

Remeber, Lakehouses, even though they support a SQL Endpoint, ARE NOT SQL DATABASES!

We solved it by putting in a Wait task in the Data Factory pipeline between the Lakehouse loading and the operations that consume that data. 5 minutes was sometimes not even enough 😞

Hope that helps.

[Philipexsitec]

Thanks Todd,

I will add the Wait activity and see if that helps..