Advanced Incremental Refresh with Fabric Lakehouses: Late Facts, Backfills, and Safe Historical Reprocessing

Scenario: Retail Orders on a Fabric Lakehouse

You’re supporting a retail analytics workspace:

• Order facts land in a Fabric Lakehouse table factOrders partitioned by OrderDate.
• A Power BI semantic model (import mode, not Direct Lake) uses incremental refresh on that table.
• Business users rely on daily refreshed reports for margin and fulfillment metrics.

Pain points:

1. Late‑arriving facts: Orders sometimes appear 2–3 days after the actual OrderDate (system delays, external integrations).
2. Backfills: Data engineering occasionally rewrites several months of history (pricing corrections, tax changes).
3. Historical reprocessing: Once or twice a year, the team wants to re-run transformations for a specific historical window, not the entire dataset.

You want:

• Fresh data every morning.
• Confidence that late and backfilled rows are included.
• Predictable refresh times even when history changes.

Everything below assumes:

• Power BI semantic model stored in Fabric (or Power BI service) using incremental refresh with real-time data disabled (we’ll note when it matters).
• Lakehouse table accessed via warehouse/lakehouse SQL endpoint for ingestion, but the semantic model itself is import.

Core Mechanics: How Incremental Refresh Actually Works

Before tweaking patterns, you need the mental model.

Incremental refresh for import models (including in Fabric) works roughly like this when you first publish and refresh:

1. Power Query defines parameters RangeStart and RangeEnd (type DateTime).
2. Your query filters the fact table on a single date/time column using those parameters.
3. In Power BI Desktop, the filter is only used for preview. After publishing and running the initial refresh, the service:
   • Creates partitions based on the policy: e.g. 5 years of historical data, 3 days of incremental.
   • Applies the filter logic to build each partition.
4. Subsequent refreshes only reprocess incremental partitions and optionally a “rolling” window of historical partitions depending on your settings.

Key constraints (as of late 2026):

• Incremental refresh requires a single filterable column of type Date/DateTime in the fact table.
• The filter must use RangeStart and RangeEnd in Power Query.
• For import models, partitions are created and managed in the service; you don’t see them in Desktop.
• When you change the incremental policy in the model (e.g. years of history), you must republish and run a refresh for the new partitioning to take effect. The first refresh after a policy change will re-evaluate the partitioning and can be as expensive as a full refresh depending on the amount of data covered.

With Fabric Lakehouses, the same rules apply; the difference is in how you source the data and how fast the lakehouse can serve partition reads.

Designing the Date Filter for Late‑Arriving Facts

Our retail scenario uses OrderDate as the partitioning column.

Problem: late‑arriving rows with an OrderDate inside a past partition won’t be picked up if that partition is never refreshed.

Pattern 1: Overlapping Incremental Window

The simplest pattern is to intentionally overlap the incremental window with recent history.

Example policy for factOrders:

• Store rows from the last: 5 years
• Incrementally refresh rows from the last: 30 days

This means:

• Partitions are created by year (or month, depending on volume) for the 5‑year window.
• Each refresh reprocesses all partitions that intersect the last 30 days.
• Late rows up to 30 days old get picked up naturally.

Implementation in Power Query (simplified):

let
    Source = Sql.Database("MyWorkspace-SqlEndpoint", "RetailLakehouse"),
    Orders = Source{[Schema = "dbo", Name = "factOrders"]}[Data],
    // Ensure OrderDate is DateTime
    Typed = Table.TransformColumnTypes(Orders, {{"OrderDate", type datetime}}),
    // Incremental filter
    Filtered = Table.SelectRows(
        Typed,
        each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd
    )
in
    Filtered

In the service, you set the incremental refresh policy on OrderDate with a 5‑year storage window and a 30‑day incremental window.

Why this works:

• Any row with OrderDate in the last 30 days lands in a partition that is refreshed on each run.
• Late‑arriving facts that fall into that window are included even if they arrive days after the fact.

Trade‑offs:

• Larger incremental window → more data scanned each refresh.
• You tune the window length based on how far back late data typically arrives.

Pattern 2: Business vs System Date

Sometimes late arrivals are more than 30 days old. For example, a partner sends a batch of corrections for orders from two months ago.

If you partition on OrderDate (business date), those rows land in older partitions that may not be refreshed.

An alternative is to partition on a system ingestion date column, e.g. LoadTimestamp.

Pros:

• Late‑arriving rows always land in recent partitions (based on ingestion time).
• You can keep a smaller incremental window (e.g. 7 days) and still capture late data.

Cons:

• Report filters on OrderDate are independent of partitioning; that’s fine, but you must ensure LoadTimestamp exists and is reliable.

Power Query filter using LoadTimestamp:

let
    Source = Sql.Database("MyWorkspace-SqlEndpoint", "RetailLakehouse"),
    Orders = Source{[Schema = "dbo", Name = "factOrders"]}[Data],
    Typed = Table.TransformColumnTypes(Orders, {{"LoadTimestamp", type datetime}}),
    Filtered = Table.SelectRows(
        Typed,
        each [LoadTimestamp] >= RangeStart and [LoadTimestamp] < RangeEnd
    )
in
    Filtered

You still expose OrderDate for reporting; the incremental refresh cares only about LoadTimestamp.

In practice, many teams:

• Use LoadTimestamp for incremental refresh.
• Keep OrderDate purely as a business attribute.

This is robust for late‑arriving facts, but you need good data engineering discipline to populate LoadTimestamp consistently in the lakehouse.

Handling Backfills Safely: Don’t Blow Up Refresh Times

Backfills are different from late arrivals. A backfill can rewrite an entire year of data, including rows that already existed.

With incremental refresh, you have three main options.

Option 1: Controlled Historical Reprocessing Window

If backfills are limited to recent history (e.g. “we occasionally reprocess the last 90 days”), you can extend the incremental window to cover that period.

Example policy:

• Store: 5 years
• Incremental: 90 days

Behavior:

• Any partition overlapping the last 90 days is reprocessed on each refresh.
• If data engineering rewrites the last 60 days in the lakehouse, the next refresh will rebuild those partitions from the new source data.

This works as long as:

• Backfills stay within the defined window.
• You accept the higher refresh cost of reprocessing 90 days each time.

Option 2: Manual Historical Refresh Trigger

If backfills can affect older periods (e.g. a tax logic change for last year), you generally don’t want to permanently increase the incremental window.

Instead, you:

1. Keep a moderate incremental window (e.g. 30 days).
2. When a backfill occurs for a specific historical range (e.g. Jan–Mar last year):
   • Use the Power BI service or Fabric workspace to trigger a full refresh of the dataset.

For import models, a full refresh:

• Rebuilds all partitions from the lakehouse.
• Ensures backfilled history is reflected.

This is the safe pattern for infrequent, large backfills.

To limit impact:

• Schedule the full refresh during a low‑usage window.
• Temporarily increase capacity or refresh concurrency if you have control over Fabric capacity settings.

Option 3: Targeted Partition Refresh (Advanced)

For teams comfortable with XMLA and scripting, you can refresh only the partitions affected by the backfill.

High‑level steps:

1. Identify the date ranges impacted by the backfill (e.g. OrderDate from 2025‑04‑01 to 2025‑06‑30).
2. Use Tabular Editor or a script (PowerShell or C# via TOM) to:
   • Connect to the dataset’s XMLA endpoint.
   • Issue a Refresh command for only those partitions.

This pattern:

• Avoids rebuilding the entire model.
• Keeps refresh times predictable.

It requires:

• Premium/Fabric capacity with XMLA read/write enabled.
• Operational discipline to maintain scripts and align them with backfill events.

Historical Reprocessing: Re‑Running Transform Logic

Sometimes the lakehouse data hasn’t changed, but you change your transformation logic in Power Query (e.g. new currency conversion, updated status mapping) and need it applied to history.

Incremental refresh won’t automatically reprocess historical partitions when you change the query.

Pattern: Temporary Expansion of Incremental Window

For our retail factOrders model:

1. Current policy:
   • Store: 5 years
   • Incremental: 30 days
2. You update transformation logic in Power Query.
3. To apply it to the last year of data without a full 5‑year refresh:
   • In Desktop, change the incremental refresh policy to:
     • Store: 5 years
     • Incremental: 365 days
   • Publish the model.
   • Run a refresh.

Behavior:

• The service updates partitions and reprocesses all data for the last 365 days using the new query.

After that refresh completes:

• Optionally revert the policy back to 30 days and republish.

Caveats:

• Changing the policy requires republishing.
• The first refresh after a policy change can be expensive; plan capacity and timing.

For larger historical reprocessing (multi‑year), a full refresh is usually simpler operationally.

Lakehouse‑Specific Considerations

Fabric Lakehouses bring a few nuances to incremental refresh.

Partitioning and Performance

The lakehouse table itself may be partitioned by a column (e.g. OrderDate or LoadTimestamp). Incremental refresh partitions in Power BI are independent of lakehouse partitions, but you can align them for performance.

Practical tips:

• If the lakehouse is partitioned by month on OrderDate, choose an incremental window that maps cleanly to months.
• Avoid extremely granular incremental windows (e.g. 1 day) on very large tables unless you’ve validated performance.

Schema Changes

Lakehouse schema changes (adding/removing columns, changing types) can break refresh.

When you plan backfills or historical reprocessing:

• Keep the schema stable during the period you expect to refresh.
• If you must change types or column names, update Power Query and test a full refresh on a smaller test dataset first.

Direct Lake vs Import

This article assumes import mode with incremental refresh.

If you use Direct Lake:

• Incremental refresh for import partitions is not the main mechanism; Direct Lake reads directly from the lakehouse.
• Late arrivals and backfills are visible as soon as the lakehouse data updates (subject to caching behavior and any semantic model refresh needed for metadata changes).

For mixed scenarios (Direct Lake for some tables, import for others), treat each table according to its storage mode and avoid assuming incremental refresh controls Direct Lake behavior.

Putting It Together for the Retail Orders Team

Let’s apply a concrete pattern to our scenario.

Current Situation

• Orders sometimes arrive up to 20 days late.
• Data engineering backfills the last 60 days roughly once a quarter.
• Occasionally, transformations change and need to be applied to the last year.

Recommended Setup

1. Partition on LoadTimestamp
   • Add LoadTimestamp to factOrders in the lakehouse (if not already present).
   • Use LoadTimestamp for the incremental filter in Power Query.
2. Incremental Policy
   • Store: 5 years.
   • Incremental: 60 days.
   • This covers both typical late arrivals and regular backfills.
3. Operational Rules
   • For backfills beyond 60 days (rare): schedule a full dataset refresh.
   • For transformation changes affecting more than 60 days but less than a year:
     • Temporarily increase incremental window to 365 days.
     • Publish and refresh once.
     • Revert to 60 days afterwards.
   • For very large logic changes (e.g. new grain or major joins): plan a full refresh and capacity headroom.

Result:

• Late‑arriving facts and routine backfills are automatically handled.
• Historical reprocessing is explicit and controlled, not accidental.
• Refresh times are predictable because you know exactly how much data is reprocessed each run.

One Practical Takeaway

Before you tune anything else, sit down with your data engineering team and map out how far back data can change (late arrivals, backfills, and logic updates), then set your incremental window and partitioning column to match that reality; most of the pain around advanced incremental refresh patterns disappears once your policy reflects how your Fabric lakehouse actually behaves.