Combining Multiple Parquet files into a table in Dataflows Very Slow

[sjrrkb123]

I have two dataflows pointing to the same set of data. Dataflow A is on SQL server (on prem) and dataflow B is in multiple .parquet files that need to be combined into a single table.

When I run the dataflow A, it takes about 1minute and 30seconds to do a select * from the table. Below is a screenshot of the refresh time

When I run dataflow B, it takes 9-10minutes to get the same amount of data. Below is the screenshot of the power query:

    Below is the refresh time (the top is using [HierarchicalNavigation=true] and the bottom query is without it)

A couple of things:

1. The parquet files are stored in an ALDS gen 2 datalake
2. They are the output of processing from a spark notebook in Azure Synapse
3. The sql server table has 3.3million rows
4. This is using an On Prem gateway to manage the datasources 

Why is my ALDS gen 2 load so much slower than on prem?

[lbendlin]

Don't use an on-prem gateway for your Azure data sources. You may not even need a VNet gateway.

[sjrrkb123 · Accepted solution]

Turns out our best solution will be to spin up a vm, install a gateway on it, and use it for the dataflow refresh from alds gen2 datalakes.

[sjrrkb123]

FYI you cannot use VNet gateway for power bi dataflows.
What is a virtual network (VNet) data gateway (Preview) | Microsoft Learn

I will look into other options and get back t oyou