Forum Discussion

Scott_Powell's avatar
Scott_Powell
Advocate IV
2 years ago

Gen2 Dataflow to Warehouse - really slow performance

Hi, I'm hitting an issue with loading data from on-prem SQL server to a Fabric Warehouse using dataflows gen2. I'm only moving 34k rows (about 30 columns), and it's taking almost 13 minutes to do thi...
Tips and Tricks
v-cboorla-msft's avatar
v-cboorla-msft
Microsoft Employee
2 years ago

Hi Scott_Powell 

 

Apologise for the delay in response and for the inconvenience that you are facing here.

 

Just as a work around, instead of loading on prem data to Dataflow Gen2 and then to warehouse (On-Prem -> DFg2 -> Warehouse) can you please try to test by loading the data from on-prem to Dataflow Gen2 and then to Lakehouse.(On-Prem -> DFg2 -> Lakehouse) (files or tables, would test both)
Then create a Data Pipeline to move data from Lakehouse to Warehouse.

Just like the medallion architecture we see often, we want to do a quick load of the source into a Raw layer, then work our way to the Warehouse which would be Silver/Gold. 


The issue which you are facing while loading the data into warehouse is just temporary. This won't be a thing once Data Pipelines can use On Prem Data Gateway which is in development.

 


Hope this helps. Please let me know if you have any further questions.

 

  • Scott_Powell's avatar
    Scott_Powell
    Advocate IV
    2 years ago

    hi v-cboorla-msft , I'm actually in the process of getting this ready to test right now. I was able to successfully load about 1 billion rows of transactions into a lakehouse using Gen2 dataflows (running multiple dataflows in parallel across several gateway clusters). Now I'll try copying those across to a DWH using the copy utility. I'll update this post as soon as I have results to report (this is a side project so might be a couple of days).

     

    One question / idea - if really feels to my like the underlying Dataflow Gen2 "engine" should be able to tap into the spark compute just like notebooks do. Similar to how it "pushes down" SQL queries to a database. Wish it could "push down" some of this type of work to spark - seems like it would be much faster.

     

    Thanks!

    Scott 

    • v-cboorla-msft's avatar
      v-cboorla-msft
      Microsoft Employee
      2 years ago

      Hi Scott_Powell 

       

      Thanks for the feedback.

      If you want to enable spark compute please upvote this idea: Microsoft Idea

       

      Thanks