Incremental Refresh
Stop refreshing entire datasets. Incremental refresh loads only new and changed data — essential for large semantic models with billions of rows.
Why incremental refresh?
Think of restocking shelves in a supermarket.
Without incremental refresh, you empty the entire shelf every night and restock everything from scratch — even items that have not changed. With incremental refresh, you only restock the new items and replace the expired ones. The shelf (your model) stays current with a fraction of the effort.
For a 5-year sales dataset with 2 billion rows, full refresh processes ALL 2 billion rows every time. Incremental refresh processes only the last few days — maybe 10 million rows. The refresh goes from 3 hours to 5 minutes.
How incremental refresh works
The partition model
Incremental refresh divides your table into date-based partitions:
fact_sales (5 years of data)
├── 2022-Q1 (historical — never refreshed)
├── 2022-Q2 (historical)
├── ...
├── 2026-Q1 (historical)
├── 2026-04-01 to 2026-04-15 (refresh window — refreshed daily)
├── 2026-04-16 to 2026-04-21 (refresh window — refreshed daily)
└── [future partition — for real-time data if enabled]Refresh behavior
| Partition Type | When Refreshed | Example |
|---|---|---|
| Historical | Never (unless manually triggered) | Data older than the refresh window |
| Refresh window | Every scheduled refresh | Last 10 days of data |
| Real-time (optional) | On every query (using DirectQuery) | Current day’s data |
Setting up incremental refresh
Step 1: Create parameters
In Power BI Desktop, create two DateTime parameters:
- RangeStart — the start of the date filter
- RangeEnd — the end of the date filter
Step 2: Filter the source query
In Power Query, filter your date column between RangeStart and RangeEnd:
= Table.SelectRows(Source, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)Step 3: Configure the refresh policy
In Power BI Desktop, right-click the table → Incremental refresh and real-time data:
| Setting | What It Controls | Example |
|---|---|---|
| Archive data starting | How far back to keep data | 5 years |
| Incrementally refresh data starting | The refresh window | 10 days |
| Get the latest data in real time (optional) | Adds a DirectQuery partition for current data | Enabled for near-real-time |
| Only refresh complete periods | Prevents partial day data from being cached | Recommended for daily grain |
| Detect data changes | Re-refresh historical partitions when source data changes | Based on a date/timestamp column |
Step 4: Publish to the service
When published, Power BI creates the partitions automatically. The first refresh is a full refresh (loads all historical data). Subsequent refreshes only process the refresh window.
Exam tip: RangeStart and RangeEnd
The exam tests your understanding of the parameters:
- Parameters must be named exactly
RangeStartandRangeEnd(case-sensitive) - They must be of type DateTime
- They are used in the Power Query filter — NOT in DAX
- In Desktop, they are set to test values (e.g., last 30 days). In the service, Power BI overrides them with the actual partition ranges.
- The filter must use
>=for RangeStart and<for RangeEnd (not<=)
Incremental refresh with real-time data
When you enable “Get the latest data in real time”, Power BI adds a DirectQuery partition for the current (incomplete) period:
fact_sales
├── 2022 to 2026-04-10 (Import partitions — fast)
├── 2026-04-11 to 2026-04-20 (Import — refreshed daily)
└── 2026-04-21 (DirectQuery — always live)This hybrid approach gives you historical performance (Import) with current-day freshness (DirectQuery). The DirectQuery partition is small (one day) so queries are fast.
Scenario: Dr. Sarah's patient data refresh
Dr. Sarah at Pacific Health has 3 years of patient outcome data (50 million rows). A full refresh takes 2 hours and puts heavy load on the hospital’s database.
She configures incremental refresh:
- Archive: 3 years
- Refresh window: 7 days
- Real-time: Enabled (today’s admissions are always current)
- Detect data changes: Enabled (corrections to historical records trigger re-refresh)
Result: daily refresh takes 4 minutes instead of 2 hours. The hospital database handles 7 days of queries instead of 3 years.
Incremental refresh and Direct Lake
In Direct Lake mode, incremental refresh behaves differently from Import mode:
| Aspect | Import Incremental Refresh | Direct Lake |
|---|---|---|
| Partitions | Created by Power BI | N/A — reads Delta log |
| Historical data | Loaded once, cached in VertiPaq | Read from OneLake on demand |
| Refresh window | Re-imports recent data | Re-frames from Delta tables |
| Real-time partition | DirectQuery to source | Reads latest Delta version |
| Benefit | Reduces refresh time and memory | Minimal — Direct Lake already avoids full refresh |
Key insight: Incremental refresh is most valuable for Import mode, where it prevents expensive full refreshes. Direct Lake already reads data on demand, so the benefit is smaller — but incremental refresh with large format is still useful for managing very large models.
Dr. Sarah at Pacific Health has a 50-million-row semantic model with 3 years of data. Full refresh takes 2 hours. She wants to reduce refresh time while keeping data current. Which approach is best?
An exam question states: 'You configure incremental refresh with RangeStart and RangeEnd parameters. The filter in Power Query uses OrderDate >= RangeStart AND OrderDate <= RangeEnd.' Is this correct?
🎬 Video coming soon
This completes Domain 3. Head back to Domain 1: Maintain a Data Analytics Solution for security, governance, and lifecycle management.