SharePoint Excel ingestion, Dataflow Gen2 vs shortcut + notebook CU efficiency

Hi bj132175​ , 

I wouldn’t assume that Shortcut + Notebook will automatically consume fewer CUs than Dataflow Gen2. It really depends on the file size/count, transformation complexity, and Spark overhead.

For smaller Excel files and relatively simple Power Query transformations, I’d generally stick with Dataflow Gen2 since it’s simpler and avoids bringing Spark into the picture.

If you’re dealing with a larger number of files or more complex transformations and the data is ultimately going into a Lakehouse, then Shortcut + Notebook/PySpark can be a better fit.

If CU consumption is the deciding factor, I’d benchmark both with the same workload. Spark startup and capacity usage can make the notebook approach more expensive for smaller workloads, so there isn’t really a blanket “shortcut + notebook uses fewer CUs” rule.

I wouldn't assume shortcut + notebook will automatically consume fewer CUs. It depends on the number and size of the Excel files, the transformation complexity, and how long the Spark processing runs.

For a smaller set of Excel files with standard transformations like filtering, joins, type changes and column cleanup, I would normally use Dataflow Gen2. It's simpler to build and easier to maintain.

If there are many files or the transformation logic becomes more complex, shortcut - Lakehouse - PySpark - Delta can be a better approach because it gives more control over the processing. But Spark also consumes Fabric capacity, so I wouldn't choose the notebook approach purely for CU savings.

I would also watch for SharePoint throttling if multiple jobs are accessing the folder in parallel.

For the CU comparison, the best option is to run both approaches with the same files and schedule and compare the actual consumption in the Fabric Capacity Metrics app.

For a typical Excel-folder ingestion, I would start with Dataflow Gen2 and move to the notebook approach when the file volume or transformation complexity justifies it.

Hi bj132175​ , 

yes, Lakehouse shortcut + notebook (PySpark) is generally the more Capacity Unit-efficient approach. Multiple benchmarks show Dataflow Gen2 consistently consumes significantly more CU than code-first approaches like notebooks, in some tests, 55% to 700% more depending on data volume and transformation complexity, since Gen2 bills across Mashup, SQL, and FastCopy engines simultaneously rather than a single execution engine. For SharePoint Excel-folder ingestion specifically, shortcut + notebook avoids the Dataflow Gen2 overhead entirely and gives you more control over transformation logic, at the cost of needing PySpark skills on your team instead of a no-code interface. If your files/transformations are relatively lightweight, this gap may matter less, but at scale the notebook approach is the more CU-efficient recommended pattern.