Incremental Refresh Decision

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.