Issue: Copy Activity Processes 0 Records from Warehouse Query on Lakehouse Data

Hi TanachaiP,

Thanks for reaching out to the Microsoft fabric community forum.

Based on your screenshots and description, you're running a query from a Warehouse that queries data from a Lakehouse, and using that result in a Copy Activity to write to Azure SQL. The fact that the pipeline sometimes runs successfully but copies 0 rows and works perfectly when rerun which points to a known issue with the SQL Analytics Endpoint in Fabric as earlier mentioned by smeetsh.

Basically, there’s a delay between data being ingested into the Lakehouse and that data being available through the SQL endpoint (which the Warehouse uses). This delay can be inconsistent sometimes just a few seconds, but we've observed it can stretch up to 10 minutes.

To fix this Microsoft Fabric added a Wait activity (e.g., 5–10 minutes) between the ingestion step and the Copy Activity. This gives the SQL endpoint enough time to reflect the latest Lakehouse state.  If you're not able to use a delay or want more control, you can also query the Lakehouse directly using a notebook and use DataFrame APIs to write to SQL. This approach avoids the SQL endpoint entirely and gives you stronger guarantees on data consistency though you did mention you'd prefer avoiding notebooks.

I would also take a moment to thank smeetsh, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.