Power BI incremental refresh

A semantic model with 10 years of transactional data shouldn't refresh all 10 years every day. Incremental refresh in Power BI partitions data by time, refreshes only recent partitions, and leaves historical partitions untouched. For any model with > 1M rows or significant data refresh latency, incremental refresh is essential.

The concept. Data is partitioned by a date column:

• Historical partitions — older data; refreshed rarely or not at all.
• Incremental partitions — recent data; refreshed every cycle.
• Active partition — current period; may be refreshed.

The model query has filter parameters that determine the date range; refresh dynamically computes which partition to refresh.

Setup. In Power BI Desktop:

1. Add two parameters: RangeStart (datetime) and RangeEnd (datetime).
2. Apply filter in Power Query: Source[Date] >= RangeStart and Source[Date] < RangeEnd.
3. Right-click the table → Incremental refresh.
4. Configure refresh policy:
   • Archive data starting — how far back to store. E.g., "5 years".
   • Incrementally refresh data starting — refresh window. E.g., "10 days".
5. Save and publish.

On first publish, Power BI partitions the data; subsequent refreshes only update the recent partition.

The refresh policy.

• Archive period — how far back history is kept (5 years).
• Refresh period — how recent the refresh window (10 days).
• Detect data changes — optional column tracking modifications; refresh only modified records.

The window between archive and refresh is "cold" — stored but not refreshed.

Detect data changes. Smarter incremental:

• Specify a "last modified" column.
• Refresh only checks rows modified since last refresh.
• Most efficient for slowly-changing dimensions.

Real-time data. Combine incremental with DirectQuery:

• Recent partition in DirectQuery for real-time.
• Older partitions in Import.
• Hybrid Tables feature in Power BI Premium.

Hybrid combines the best of both: real-time recency, performant import for history.

Performance benefits.

• Refresh time — minutes instead of hours.
• Memory — partitions loaded on demand.
• Storage — efficient compression per partition.

For a 100M-row table, incremental refresh can cut refresh time by 95%+.

Partition strategy.

• Yearly — for slowly changing data, longer history.
• Monthly — most common; balances granularity and refresh cost.
• Daily — for very recent / fast-changing data.

The refresh policy determines partition granularity.

Limitations.

• DirectQuery and Live Connection — partial support; DirectQuery to many sources fully supports.
• Service-side scheduled refresh — must be enabled.
• Source must support range filter — pushdown to source for efficiency.

Source pushdown. Critical for performance:

• Power BI sends the range filter to source.
• Source returns only matching rows.
• Source-side filter avoids transferring full data.

Without pushdown, even incremental refresh reads everything.

Premium / Premium Per User. Incremental refresh requires:

• Power BI Pro for basic incremental (limited).
• Premium / PPU for full feature including detect data changes and hybrid tables.

For meaningful data sizes, Premium is the practical choice.

Initial load. First refresh is expensive:

• Reads full history per archive period.
• Can take hours.
• Often broken into manual partitions to manage.

Plan the initial load carefully; may need to schedule it manually first.

Partition troubleshooting. Tabular Editor and SQL Server Management Studio can connect to the Premium dataset and show partitions:

• Per-partition row count.
• Refresh timestamp.
• Memory footprint.

Visibility helps when refresh seems wrong.

Common pitfalls.

• No source pushdown. Each refresh reads all source data; defeats incremental.
• Filter not on date column. Filter on derived date; pushdown fails.
• Detect data changes column wrong. Misses updated rows.
• Archive period too long. Wastes storage on unused history.
• Initial load timeout. First refresh exceeds limits; partitions incomplete.
• Cross-table dependencies. Two tables with different partition strategies; joins inefficient.

Best practices.

• One date column per fact table — incremental aligns.
• Pushdown verified — check query trace.
• Archive period justified. Don't archive 10 years if 3 suffice.
• Refresh window short. 5–10 days typical; longer if source data changes far back.
• Monitoring — refresh duration trends.

Schema changes.

• Adding a column requires full refresh.
• Changing data types may invalidate partitions.
• Major changes: deploy then full refresh; subsequent refreshes incremental again.

Operational rhythm.

• Daily / hourly — scheduled refresh.
• Monthly — refresh metrics review.
• Quarterly — full refresh to reset partitions if drift.

Strategic positioning. Incremental refresh is essential for any Power BI semantic model with material data volume. The setup investment is moderate (defining parameters, configuring policy); the operational benefit is substantial (faster refreshes, predictable resource usage, scalable to billions of rows). For new models with growing data, design with incremental from the start; retrofitting later is harder. The pattern is mature; the discipline of using it is what separates production-grade models from prototypes.