Pipeline - Copy Data (Lakehouse ) --> Dataflow (Warehouse)

[Byzza]

Hi Brains Trust.

The last couple of months I've been having a lot of issues with my pipelines.

I have a lot of simple pipelines that use Copy Data to copy data from our onPrem SQl Server to a lakehouse, then a Dataflow to clean to put into a warehouse and a final dataflow to merge into our Gold level Warehouse.

the Copy Data works perfectly, but the first dataflow is always grabbing yesterdays data.  I figured it was to do with lag in updating the Lakehouse SQL engine, so I put in a wait (60sec), which worked for a while, then stopped again, so I increased to 120sec, which again worked for a while, but the last 2 days, its stopped working again.

any help, suggestions would be greatly appreiated.

Thanks in advance

Byron

[Avyaktha · Accepted solution]

Hi Byzza ,

Good to hear from you again

If you’re already doing manual refreshes and still seeing delays, then the most reliable fix is to move the Bronze layer to a Warehouse instead of a Lakehouse. The Warehouse doesn’t rely on the SQL analytics endpoint in the same way, so you won’t run into these metadata refresh lags.

Alternatively, if sticking with Lakehouse is a must, the only other option is to build in a more dynamic check  before running the Dataflow, since static waits and manual refreshes won’t guarantee consistency.

Thank you 

Avyaktha 

[v-lgarikapat]

Hi @Byzza ,

Thanks for reaching out to the Microsoft fabric community forum.

@Avyaktha 

Thanks for your prompt response.

@Byzza 

I wanted to follow up and confirm whether you’ve had the opportunity to review the information  provided by @Avyaktha   If you have any questions or need further clarification, please don’t hesitate to reach out.

We appreciate your collaboration and support!

Best regards,
Lakshmi.

[v-lgarikapat]

Hi @Byzza ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

We appreciate your engagement and thank you for being an active part of the community.

Best Regards,
Lakshmi.

[Avyaktha]

hi Byzza , 

What you’re observing is really a known feature of the Lakehouse SQL analytics endpoint in Microsoft Fabric. The files are written almost instantly when you use Copy Data to load into a Lakehouse table, but it may take a little while for the SQL analytics endpoint—which Dataflows frequently use—to refresh its metadata and indexes.

Your Dataflow is just reading from the SQL endpoint before it has refreshed, which is why it often pulls up "yesterday's" data. Even adding a static Wait activity will occasionally help, the most recent rows will still occasionally be missed because there is no guaranteed SLA on the refresh time. you can refer the documnet below. 

Microsoft documents this as a known issue here:
https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-analytics-endpoint-performance?utm_sourc...

[Byzza]

Hi Avyaktha,

Yes, I'm aware of this limition.

What I was hoping to find out, is why its getting worse and/or any tips and tricks to avoid this happening?

should I just move away from a Lakehouse in the bronze layer and just use a Warehouse?