Forum Discussion
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.
Continued ContributorHi bj132175,
One option nobody's mentioned yet is a Python notebook instead of PySpark. Spark doesn't read Excel natively, so you'd end up using pandas or openpyxl inside Spark anyway and paying for a cluster you don't really need. A pure Python notebook runs on a small single node and is usually much cheaper for Excel files, especially if they're in the MB range rather than GB. Read them with pandas or polars from the shortcut path, transform, and write to Delta with the deltalake library.
The other thing that often saves more than the engine choice is only processing files that changed. Keep a small table of file names and modified dates, and skip anything you've already loaded. Reprocessing the whole folder on every run is where a lot of CU quietly goes.
Agree with the others though, run both on a real batch and compare in the Capacity Metrics app before committing.