Incremental refresh is one of the most effective ways to improve performance and reduce capacity usage in Microsoft Fabric and Power BI. Instead of reloading all your data with every refresh, incremental refresh updates only the recent data that has changed. This means:
- Faster refreshes
- Lower risk of timeouts
- Reduced load on capacities
- More scalable solutions
But, and this is important, incremental refresh must be configured correctly. If it’s not, it can actually increase capacity usage and slow things down. Let’s go through the key principles and common pitfalls.
Key Principles to Remember
- Partitions – Incremental refresh automatically splits your table into partitions.
- Per-partition queries – Each partition executes its own query against the source.
- Date filtering support – The source must support filtering by date.
- Filter at the source – Filtering must be processed at the source system (via source queries or query-foldable steps).
- File-based sources – Filtering must use file metadata (file or folder name). Don’t rely on modification date.
- One source per table – Avoid merging multiple sources in the same incremental-refresh table.
Common Mistakes and How to Avoid Them
1. File-based sources – don’t filter inside the file
If your data source is a set of files (e.g., SharePoint or Data Lake), avoid filtering by a date column inside the file. Why?
Power BI will need to open every single file for each partition query, just to apply the filter. On large file sets, this destroys performance.
✅ Best practice: Base filtering on file metadata (e.g., date in the file name or folder name). That way, Power BI pre-filters and opens only the relevant files.
2. Dataflows with transformations – apply incremental refresh on the source layer
A common architecture splits dataflows into two:
- Source dataflow – pulls data from the original system.
- Transformation dataflow – applies business logic, shaping, and modeling.
Incremental refresh should be applied on the source dataflow, not the transformation one. Why?
By default, dataflows don’t support query folding. That means filters may not be pushed down to the source, and instead everything gets processed in Power Query, which is inefficient. It is possible to configure folding for some dataflows, but you need to be very careful.
3. Apply filters as early as possible
Filtering should happen as close to the source as possible.
- If using a database (like SQL), add the filter in the query itself.
- If using Power Query, put the filter step early in the transformation chain so it folds.
💡 Always check that query folding is working (see last point).
4. Don’t create too many partitions
Remember: every partition means one query.
Refreshing 50 days of data means 50 queries. Refreshing 2 months of data might mean only 2 queries.
✅ Best practice: Use fewer, larger partitions when possible. Too many small partitions increase overhead and slow refreshes.
5. Avoid merges in incremental refresh tables
Merges (joins) are a classic performance killer in incremental refresh.
Here’s why: even if filtering is correctly applied to the first source, the merge may force Power BI to load the entire second table for each partition.
On large tables, this completely negates the benefits of incremental refresh and can cause capacity overload.
✅ Best practice: Avoid merges in incremental-refresh tables. If necessary, handle joins at the source or in staging.
6. Ensure query folding is applied
This principle underpins many of the above points.
If query folding is broken before your filter step, the filter won’t be pushed down to the source. Instead, Power Query will load all the data and then filter locally, which is exactly what you don’t want.
✅ Best practice: Always confirm folding is working for your filters. In Power BI Desktop, right-click a step and check if “View Native Query” is available.
Key Takeaways
Incremental refresh is powerful, but only when applied with care:
- Filter at the source.
- Be mindful of file-based sources.
- Don’t over-partition.
- Avoid merges.
- Validate query folding.
Done right, it dramatically reduces refresh times, stabilizes capacity usage, and lowers costs. Done wrong, it can become a hidden source of overload.
#MicrosoftFabric #PowerBI #Optimization #MicrosoftFabricOptimization #SelfService #SelfServiceBI #CitizenDeveloper #FabricMonitoring #FabricObservability #FabricManagement #FabricGovernance #BestPractices
Leave a Reply