Large-Scale Incremental Refresh: RangeStart/End Parameters & Hybrid Tables

[!NOTE] Refreshing a 500-million row model every morning via full data reload consumes hours of network bandwidth and hits gateway timeouts. Incremental Refresh partitions the table into immutable historical segments and refreshes only recently changed partitions.

1. Incremental Refresh Mechanics

1. Define Parameters: Create two reserved datetime parameters in Power Query: RangeStart and RangeEnd.
2. Filter Fact Table: Filter the datetime column: [TransactionDate] >= RangeStart and [TransactionDate] < RangeEnd
3. Configure Policy in Power BI Desktop:
   • Store historical data for: e.g. 5 Years.
   • Refresh data in the last: e.g. 10 Days.
   • Detect data changes using a LastModifiedDate column.
4. Publish: Power BI Service automatically splits the table into year/month/day partitions. Historical partitions are archived in read-only compressed state, while only the active 10-day partition is queried during refresh.

Staff Architecture: Incremental Refresh, Hybrid Tables & Deployment Pipelines

• RangeStart / RangeEnd Partition Pruning: Configure Power Query parameters RangeStart and RangeEnd (strict DateTime type) on a single filtered date column (>= RangeStart and < RangeEnd — watch out for Query Folding!). Once published to Power BI Service, Incremental Refresh splits your 5-year table into static historical partitions (5 years untouched) and refreshes solely the rolling 7-day hot slice (10x faster refreshes!).
• Hybrid Tables (Hot DirectQuery + Warm Import): On Premium / Fabric capacities, toggle "Get the latest data in real time with DirectQuery" inside Incremental Refresh so historical years stay blazing fast in Import VertiPaq while today’s live partition queries source tables in real time!
• Deployment Pipelines (Dev -> Test -> Prod) + XMLA Endpoints: Promote .pbip / Fabric Git-tracked semantic models safely via Deployment Rules switching parameter server/DB strings without manual Power BI Desktop re-uploads, and use Tabular Editor / XMLA to refresh single corrupted partitions selectively.