Power BI Incremental Refresh Interview Questions: Partitions, Folding, and Late Data

Power BI incremental refresh divides a table into date-based partitions and refreshes the portion selected by a policy. It can reduce repeated processing, but it is not an automatic guarantee that every historical correction will appear. A successful refresh can still leave an old partition stale if the policy never revisits it.

In an interview, explain four things together: the partition date, the retention period, the refresh window, and the source system's update behavior. Then show how you would verify the result. This guide uses original scenarios to connect setup steps with the failures a BI engineer actually needs to diagnose.

Key takeaways

• RangeStart and RangeEnd define the parameterised partition filter; their names are case-sensitive.
• Use an inclusive boundary at only one end so adjacent partitions do not overlap.
• Stored history and the period reprocessed on each refresh are different choices.
• Verify source-side filtering instead of assuming a fast Desktop preview proves query folding.
• Late updates and deleted rows need an explicit correction strategy.

What are RangeStart and RangeEnd?

In Power Query, create Date/Time parameters named RangeStart and RangeEnd. Use them to filter the table's partition date column. Desktop values let you work with a manageable sample; the service applies policy-driven ranges when processing partitions.

Table.SelectRows(
    Source,
    each [OrderDate] >= RangeStart
      and [OrderDate] < RangeEnd
)

This is an illustrative M filter. The source step, date types, and folding behavior must suit your connector. Microsoft specifically warns against including equality at both ends, because a boundary row could satisfy two adjacent partitions. Configuration reference.

How do I explain partition boundaries with an example?

Imagine daily partitions covering June 1 and June 2. Use the convention [start, end): start included, end excluded. A row at exactly June 2, 00:00 belongs to the second partition, not both.

Row timestamp	June 1 partition	June 2 partition
June 1, 00:00	Included	Excluded
June 1, 23:59:59	Included	Excluded
June 2, 00:00	Excluded	Included

Test exact midnight, not just an arbitrary midday record. Also decide which time zone the source timestamps represent. Converting UTC timestamps to local dates inconsistently can shift rows between periods even when the comparison operators are correct.

A partition test should compare distinct business keys, row counts, and a meaningful total. Equal row counts alone can hide one missing row and one duplicate row.

What is the difference between archive and refresh periods?

The archive or storage period controls how much history the model retains. The refresh period controls which recent data is reprocessed. A policy that stores five years and refreshes ten days does not rescan five years on every ordinary refresh.

That policy is a claim about the data: changes older than ten days must either be impossible, unimportant, or handled by another correction process. Ask the business how far back cancellations, refunds, and adjustments can arrive before choosing the number in the dialog.

The initial service refresh is different from a steady-state refresh because it has to establish historical data and partitions. An initial load taking much longer than a later refresh is not, by itself, evidence of a broken policy. Plan and measure both stages separately.

Why does query folding matter for incremental refresh?

Folding means Power Query can translate supported transformations into work performed by the data source. For incremental refresh, the key question is whether each partition request restricts source work to its intended range. If a full table is retrieved and filtered locally for every partition, the policy may create repeated expensive scans.

Do not rely on one visual indicator without understanding the connector. Inspect the generated query where available, use query diagnostics, and examine source-side traces when needed. A small Desktop range can look fast because of caching or limited preview data while the service workload is much larger.

1. Choose a narrow test period with known rows.
2. Check that the source query includes the intended start and end predicates.
3. Inspect transformations before the filter for operations that may prevent pushdown.
4. Compare source rows read and processing duration with the expected range.
5. Repeat in the deployed environment with the real gateway and credentials.

Changing a column's type, invoking a custom function per row, or applying a non-folding transformation can alter what the connector can push down. The effect is connector-specific. Explain how you would measure it rather than declaring that every particular transformation always breaks folding.

What happens to late-arriving data and historical corrections?

Consider an order dated January 15 that arrives at the warehouse in April. If partitions use OrderDate and the policy refreshes only recent days, the January partition may not be processed. The same issue occurs when a January order's amount is corrected in April while its order date remains unchanged.

Change	Why it can be missed	Possible response
Late insert with an old event date	Target partition is outside the refresh window	Wider reprocessing horizon or targeted repair
Historical amount correction	Existing old partition is not refreshed	Refresh affected history and reconcile totals
Transaction date changes	Old and new partitions may diverge	Refresh both affected ranges; review key design
Hard delete at the source	No surviving row carries the deletion signal	Explicit deletion/reconciliation strategy

A good design begins with a lateness distribution and a business tolerance. If almost all corrections arrive within thirty days but a few occur much later, a thirty-day rolling refresh plus a controlled historical repair process may be more appropriate than pretending the exceptions do not exist.

An immutable ingestion timestamp can simplify some workflows, but switching the partition key changes the semantics and may create other update issues. Explain the trade-off; do not prescribe a new column as a universal fix.

Does Detect data changes catch every update or delete?

No. Treat change detection as an optimisation with documented limits, not a complete change-data-capture system. The selected modification column and the refresh policy must match the actual update process. It does not automatically widen the historical period being considered.

A reliable modification timestamp should change whenever a relevant business value changes. If a source update leaves that timestamp untouched, the signal is unreliable. Hard deletes require particular care because a removed row does not remain available to announce its deletion.

In a design discussion, ask whether the source can represent a soft-delete flag, whether that row remains queryable during reconciliation, and how historical partitions are repaired. Merely filtering deleted records out before examining changes may remove the very evidence you need. Validate the complete pipeline on a small example.

How would you troubleshoot a slow refresh?

First separate “too much source data read” from “the expected data is expensive to process.” Review refresh history, source query duration, gateway behavior, and capacity pressure. Increasing a timeout before understanding which stage is slow can conceal the real problem.

1. Verify parameter spelling, types, and the actual date column used.
2. Check non-overlapping boundaries and source-side range predicates.
3. Compare initial-load work with steady-state partition work.
4. Inspect transformations, source indexes or partition pruning, and row volume.
5. Check credentials, gateway connectivity, concurrency, and resource limits.
6. Reconcile business totals after any policy or query change.

Feature availability, hybrid/real-time options, and advanced partition-management tooling depend on the model and capacity. Do not promise a particular management operation without checking the deployed setup. Microsoft's troubleshooting guide is the place to verify environment-specific restrictions.

Interview scenario: refresh succeeded, but the revenue is wrong

Scenario: the warehouse corrected a January order from 100 to 80 in April. The model stores three years and refreshes thirty days. The refresh status is successful, but the report still shows 100.

Answer: establish the partition key and locate that order's partition. If the key remains in January, the ordinary April policy may never have revisited it. Compare the source record, modification time, relevant partition, and model value. Correct the affected historical range through the supported process, then change the operating policy so future late corrections are detected and repaired.

Do not begin by rewriting a DAX measure. The measure may be correctly aggregating stale imported data. Trace the record through ingestion, partition processing, and report context before deciding which layer is wrong.

What should a production validation checklist contain?

• Rows exactly at each boundary and around time-zone transitions.
• A late insert and an update both inside and outside the rolling window.
• A changed partition date, with reconciliation of both old and new periods.
• A deletion case matching the source's real deletion policy.
• Initial refresh duration, steady-state duration, and source-query evidence.
• A documented historical repair procedure with an accountable owner.

Frequently asked interview questions

Is incremental refresh just appending new rows? No. The policy refreshes selected partitions; existing records within processed ranges can be reloaded. The exact behavior depends on the configured policy and model.

Why are there duplicates at midnight? Check whether adjacent ranges both include the shared endpoint, then check source keys and any timestamp conversions. Boundary overlap is one possible cause, not the only one.

Can a successful refresh still leave incorrect data? Yes. Execution success does not prove the policy covers every business change. Reconcile known records and totals.

How should I prepare? Build a tiny order dataset with boundary rows and historical corrections. Predict which partition each row belongs to, then explain which refresh would include each change. That exercise is more useful than memorising the setup dialog.