3.8 — Implement Incremental Refresh
DP-600 Domain 3 · Skill 3.8
What You Need to Know
Incremental refresh splits an Import table into date-based partitions. A refresh then reloads only the recent ones, so large models refresh faster, more reliably and with less load on the source.
Setting it up
- In Power Query, create two Date/Time parameters named exactly
RangeStartandRangeEnd. - Filter the table's date column with them, with one inclusive and one exclusive boundary:
[OrderDate] >= RangeStart and [OrderDate] < RangeEnd. This avoids duplicating or losing boundary rows. - Make sure the filter folds to the source, so that each partition sends its own filtered query. Without folding, every partition would read the whole table.
- Define the policy on the table:
- Archive data starting N years (or months or days) before the refresh date: the history that's kept.
- Incrementally refresh data starting N days before the refresh date: the window that's reloaded each time.
- Optional: Detect data changes (only reload partitions whose max(ModifiedDate) changed), Only refresh complete days, and Get the latest data in real time with DirectQuery (a hybrid table, Premium/Fabric).
- Publish. The first refresh in the service builds every partition and loads the history. Later refreshes touch only the incremental window.
Behavior worth knowing
- Partitions are created and merged automatically (days, then months, then years) as time moves on.
- After publishing, you can manage individual partitions through the XMLA endpoint (SSMS or Tabular Editor), for example to refresh one old partition after a correction.
- Deployment pipelines keep the policy, and the target's partitions and data aren't wiped on redeploy.
- The model grows in the service, so pair it with the large semantic model storage format when it passes the default size.
- Dataflow Gen2 has its own incremental refresh, and Direct Lake models don't need one: their freshness comes from the Delta tables and framing.
Picking the windows
| Requirement | Policy |
|---|---|
| Keep 5 years for trend reports; source rows can change for up to 10 days after the order date | Archive 5 years, refresh 10 days |
| Reports must include today's orders as they happen | Add real-time DirectQuery (a hybrid table) for the newest partition |
| Most refresh-window rows rarely change | Detect data changes on a LastModified column |
Key Concept: RangeStart and RangeEnd (Date/Time) filter a folding query. The policy archives history and refreshes only a recent window. The first service refresh loads everything, and later refreshes load only recent partitions.
Exam Traps
- Naming the parameters differently, or using the Date type instead of Date/Time
- Using
>=on both boundaries, which duplicates rows at the partition edges - A source query that doesn't fold, so every partition rescans the whole table
- Setting the refresh window shorter than the period in which source rows can still change
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 17 DP-600 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Incremental refresh and real-time data for semantic models, Microsoft Learn
- Configure incremental refresh and real-time data, Microsoft Learn
- Advanced incremental refresh with the XMLA endpoint, Microsoft Learn
- Incremental refresh in Dataflow Gen2, Microsoft Learn