Launch Deal · 50% off 1st month ·

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

  1. In Power Query, create two Date/Time parameters named exactly RangeStart and RangeEnd.
  2. 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.
  3. 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.
  4. 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).
  5. 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.

See plans

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.