Forum Discussion
Advocate IIWhat actually influences CU consumption in Dataflows Gen2?
Hi mrbartuss,
Yes, exactly and the saving is actually proportional to your table count.
Without query referencing (no "Reference" step):
Each of your 19 output tables would independently evaluate its own query all the way to the source. That means 19 separate HTTP requests to SharePoint + 19 full Excel file parses through the Mashup engine. Each one is billed at 12 CU/s for up to 10 minutes.With query referencing (your current setup) :
The SharePoint read happens once (the base "Source" query), and all 19 output tables branch off that single evaluation. Only the filtering/column selection steps run 19 times which are near-instant in-memory operations.So in your Dimensions dataflow specifically, query referencing could be saving you up to ~18× the cost of a full source re-read. You're already doing the right thing there.
The remaining optimization lever (point 1) would remove the SharePoint dependency entirely once the file is in OneLake as a Delta table, even that single source read becomes a local Delta scan instead of an HTTP call to SharePoint.
Advocate II1. I'll test this approach
2. That's exactly my set up right now - I'm already using 'Reference query'. What you are saying is that if I did not do it this way, even move CUd were consumed?
Super UserHi mrbartuss,
Yes, exactly and the saving is actually proportional to your table count.
Without query referencing (no "Reference" step):
Each of your 19 output tables would independently evaluate its own query all the way to the source. That means 19 separate HTTP requests to SharePoint + 19 full Excel file parses through the Mashup engine. Each one is billed at 12 CU/s for up to 10 minutes.
With query referencing (your current setup) :
The SharePoint read happens once (the base "Source" query), and all 19 output tables branch off that single evaluation. Only the filtering/column selection steps run 19 times which are near-instant in-memory operations.
So in your Dimensions dataflow specifically, query referencing could be saving you up to ~18× the cost of a full source re-read. You're already doing the right thing there.
The remaining optimization lever (point 1) would remove the SharePoint dependency entirely once the file is in OneLake as a Delta table, even that single source read becomes a local Delta scan instead of an HTTP call to SharePoint.