Incremental Refresh + Hybrid Tables for Billion-Row Fact Tables

1. Incremental Refresh Basics

Three Power Query steps enable incremental refresh:

1. Create DateTime parameters called RangeStart and RangeEnd.
2. Filter the source query: Date column >= RangeStart AND Date column < RangeEnd.
3. In Power BI Desktop, right-click the table and select Incremental Refresh. Configure the policy: store N years of data, refresh last N days.

// Power Query M example
let
    Source = Sql.Database(ServerName, DatabaseName),
    Sales = Source{[Schema="dbo",Item="FactSales"]}[Data],
    FilteredByDate = Table.SelectRows(
        Sales,
        each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd
    )
in
    FilteredByDate

Always verify that the filter folds to the source by right-clicking the filter step and selecting View Native Query.

2. Policy Design Patterns

• Store 5 years, refresh last 30 days: typical for sales, finance, operational facts with slowly changing historical data.
• Store 10 years, refresh last 7 days, detect data changes: for compliance-driven retention where you keep a decade of history but need to detect backdated adjustments.
• Store 2 years, refresh last 24 hours, hybrid DirectQuery: for operational dashboards that need both history and real-time state.
• Store 90 days, refresh last 4 hours: for high-frequency operational data that does not need long retention.

3. Query Folding Requirements

Query folding is the translation of Power Query M steps into native SQL (or equivalent) that runs on the source. Incremental refresh requires folding, because otherwise Power BI pulls the entire table and filters in the mashup engine.

Common folding killers

• Adding custom columns with M functions not supported by the source.
• Merging with a table from a different source.
• Using Table.Buffer() which materializes the table in memory.
• Index-based row operations that require materialization.

Push transformation logic into views or stored procedures at the source when possible. A well-designed source view that exposes exactly the shape needed for Power BI is the most reliable way to guarantee folding.

4. Hybrid Tables: Real-Time Current Partition

Hybrid tables extend incremental refresh with a DirectQuery partition for the most recent data. In the incremental refresh dialog, check "Get the latest data in real time with DirectQuery (Premium only)." The most recent partition (typically today or this week) becomes a DirectQuery partition while older partitions remain imported.

At query time, Power BI combines imported partitions and the DirectQuery partition to produce a single result. Users see historical data blazingly fast (from memory) and current data up-to-the-second (from the live source). This pattern replaces the old technique of having two separate datasets (one history, one current) and manually union-ing them.

5. Handling the Initial Load

The first refresh populates all historical partitions. For multi-billion-row tables, this can take 6 to 24 hours. Strategies:

• Temporary capacity upgrade: scale to a larger SKU during initial load, then scale back down. F SKUs support fast scale operations.
• XMLA backfill: use SSMS or Tabular Editor to create partitions manually and issue Process commands partition-by-partition with controlled parallelism. This allows scheduling partition refreshes during off-peak hours over multiple days.
• Parameterized starter data: load a recent 90-day window initially, publish the model, then backfill historical partitions via XMLA over subsequent days.

6. Monitoring Incremental Refresh

• Enable incremental refresh diagnostics to capture per-partition refresh times.
• Track partition row counts over time. Sudden drops can indicate query folding regression or source data changes.
• Monitor detect-data-changes skip rate. A high skip rate confirms the feature is working; a low skip rate suggests ModifiedDate tracking is broken.
• For hybrid tables, monitor DirectQuery partition query count and latency. Unexpectedly high query counts can indicate the DirectQuery partition is too large and should be split.

Frequently Asked Questions