Incremental Refresh Decision

Hi krishnakanth240​ 

What is the current and expected data volume/row count: current -1 million rows, expected 100-500 rows per day 

How much historical data needs to be retained : last 5 years

How frequently is the data refreshed: Daily

What is the source system: Databricks

Is query folding supported for the date/time filter: Yes

Is the data append-only or can historical records be updated/deleted: historical can be updated/deleted

Hi powerbiexpert22​ ,

Good to think this through before prod. Given your numbers:

1. Adding incremental refresh later won't break anything. It doesn't touch your model/measures — the engine just re-partitions and does a one-time full reload (a few mins). Only caveats: you can't re-download the .pbix after, and republishing from Desktop can reset partitions. Safe to add later, just do it deliberately.
2. At your volume it's overkill. Incremental earns its keep at tens of millions of rows / several million per month. You're at ~1M total — a small model full refresh handles easily.
3. Refresh time isn't your problem. Databricks → Power BI, ~1M rows folds in about 2–5 mins. Nothing to optimize.
4. Your mutable history actually argues against it. Incremental only re-queries the recent window and freezes older partitions. Since your records can be updated/deleted across all 5 years, those edits sit in archived partitions incremental won't refresh — so you'd show stale data. That's a correctness risk. Full refresh always reflects updates/deletes.
5. If you want proof, pilot both. One full, one incremental in QA for a week with real historical edits — compare refresh time and accuracy before deciding.

Bottom line: start with full refresh — simpler, fast enough, and the only option that stays accurate with editable history. Revisit incremental only if volume grows into the millions and history becomes append-only. Adding it later costs you nothing.

Thanks 
Natarajan Manivasagan

If you found this helpful, please consider giving it a Kudos and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

🏆Best Solution for Enterprise BI — 2026 Microsoft Fabric Semantic Link Developer Experience Challenge
👉Microsoft announcement · View the winning notebook

For more Power BI tips and discussions, let's connect on LinkedIn.

Cheers!

Hi powerbiexpert22​,

Given the numbers you shared, I think incremental refresh is worth testing, but I would make the decision based on how far back historical changes can occur, not only on the current row count.

You currently have around 1 million rows and are adding only 100–500 rows per day. If a normal full refresh from Databricks already completes quickly and comfortably within your refresh window, there is nothing wrong with keeping the model simpler initially.

On the other hand, since your RangeStart / RangeEnd filter folds to Databricks and you intend to retain five years of history, this is a good model to test incremental refresh on before it grows much further. Microsoft's incremental refresh guidance is designed around that pattern.

The part I would be careful with is the statement that historical rows can be updated/deleted.

Once a partition moves outside the configured incremental refresh period, Power BI does not normally refresh that historical partition again. So, for example, if you configure:

Store = 5 years
Refresh = last 30 days

and a record from two years ago is changed, that change would not automatically be picked up simply because you are using incremental refresh.

The optional Detect data changes setting can reduce unnecessary processing by checking an audit/LastModified datetime column, but there are two important limitations:

• it evaluates changes within the configured incremental range;
• hard-deleted rows are not detected. Microsoft only supports detecting a soft delete where the row still exists and its change-tracking column is updated.

So before choosing the refresh window, I would identify the business rule for historical changes:

• If records normally stop changing after, say, 30 or 90 days, configure the refresh window to cover that period.
• If records can be updated several years later, a narrow incremental window can leave stale historical partitions.
• If hard deletes can occur anywhere in the five-year history, standard Detect data changes is not enough by itself.

For Premium-capacity models, Microsoft also documents more advanced XMLA/custom polling-expression patterns that can selectively flag older partitions when backdated changes occur, but I would only introduce that complexity if the business actually needs it.

So my approach would be:

1. Benchmark the current full refresh first.
2. Confirm the maximum realistic lookback period for updates.
3. Confirm how deletes are represented in Databricks - hard delete versus soft-delete flag.
4. Configure incremental refresh with a refresh window large enough to cover normal historical changes.
5. Validate it in Dev/Test before promoting it to Production.

With only 100–500 new rows per day, incremental refresh should be a performance/maintainability decision, not something you need simply because the table has reached 1 million rows.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.