Forum Discussion
Incremental Refresh on Dataflow sourced from SharePoint
Hi Odiadri
The reason for putting the range start and range end filter early in the steps is that because all of these subsequent steps thereafter will only be using the filtered data and not the entire data set. The reason for the speed increase is because under the hoods the incremental refresh means it does not have to reload the entire data set every day, but only the new data that is incoming and that is why you are seeing the performance gains.
Ah, we haven't mentioned caches yet, have we? Most likely all of your files are only read once from source, and then fetched from cache for the other partitions.
Super UserThere are a couple of things to unpack here.
In dataflows you do not even get to choose where to place the filter transform. That step will always be placed at the end (where it makes the least sense as GilbertQ mentioned). Rather infuriating.
Next up - query folding. You will most likely not get that with your data store, so this negates one of the big benefits of incremental refresh. This means the dataflow will read all of your source files, and then throw away the ones it doesn't need
So what's left? Storing the results back into the ADLS and/or your specified destination. That is a surprisingly time-expensive operation, and this is where you see your partial savings, starting from the second refresh.
In general incremental refresh on a dataflow is something that you will want to avoid. Better to do that in a semantic model where you have much better control over all the aspects of the process, including proper partition management.
Thank you very much for your insights.
I understand why incremental refresh is faster than full refresh in general, and SharePoint Excel files do not support query folding, and the filter being late is not ideal (yes infuriating!).
However, I still do not fully understand the source side of the process.
My setup is:
- 1 Excel file per day in SharePoint
- Dataflow Gen1: Incremental refresh policy = refresh last 3 days
If the query still has to read all SharePoint files for each partition because there is no folding, then I would expect: Full refresh = read all files once + Incremental refresh for 3 days = read all files 3 times (once per partition).
That should theoretically be slower, not faster, no?
So is the main performance gain simply that only the last 3 partitions are rewritten into storage, while historical partitions are left untouched?
Thank you all!
- lbendlin
Ah, we haven't mentioned caches yet, have we? Most likely all of your files are only read once from source, and then fetched from cache for the other partitions.
