Forum Discussion
Building an Incremental Loading Solution in Microsoft Fabric - Challenges with Custom SharePoint Nav
Yes incremental refresh in fabric dataflow gen2 requires a direct query folding compatible data source and SharePoint.Contents() breaks folding. Once folding is broken fabric cannot automatically track deltas or apply range filters which makes incr refresh unavailable.
1. REST API based SharePoint access:
you can implement incr refresh if you switch to the sharePoint REST API or Graph API via Web.Contents(). This approach allows you to control pagination and filtering (?$filter=Modified ge ...), making the source foldable-like and enabling fabric’s incremental refresh to work.2. Fabric native alternative:
more maintainable architecture is:
Use a dataflow gen2 (staging) that calls the sharePoint REST API and lands metadata (file name, modified date, path) in Lakehouse in replace mode.
Use a 2nd dataflow or data Pipeline to perform incremental merge (using ModifiedDate as watermark) into a curated Lakehouse table.
Use notebook or data pipeline activity only for business logic, not deduplication. Fabric’s delta merge in Lakehouse (via Spark SQL MERGE INTO) can handle updates natively.
In short I can advise the following, you have to decide which would be apt to your scenario:
Incremental refresh = not supported with SharePoint.Contents() or custom recursive functions
Switch to REST API + metadata staging + Lakehouse merge for a Fabric native automated, and scalable incremental load.
MY HYPOTHESIS: Incremental refresh requires direct data source connections, not custom functions with external fetches. Is this correct?"requires" is a strong word. "prefers" describes it better. But yes, a Direct Query source is better at limiting the data that needs to travel over the network for the processing of each partition.
Don't use Excel files. Use CSV archives.
Don't use recursive functions. There's really no need for them other than the academic joy.
Use the standard SharePoint.Contents and filter your Folder Path and File Name patterns early
Avoid any merges or "Combine binaries". Do this yourself. I have a blog entry on that option : Streamlined process for appending multiple files w... - Microsoft Fabric Community
Don't use Excel files. Use CSV archives. Is there CSV archives of Excel files in Sharepoint? Can you explain?
Excel ist more costly to ingest due to the Meta data overhead.
Incremental refresh against non-folding sources will require ALL source data to be fetched before the partition filtering. Using ZIP files makes that much more palatable.
