Wicked Smart Data
LearnInsightsAboutContact
Sign InLet's Build
LearnInsightsAboutContact
Sign InLet's Build
Wicked Smart Data

Intelligence, automation, and expert execution — plus an elite library of free knowledge. We turn complexity into competitive advantage.

Start a conversation

Platform

  • Learning Paths
  • Insights
  • RSS Feed

Company

  • About
  • Contact
  • Work With Us

Legal

  • Privacy Policy
  • Terms of Service

© 2026 Wicked Smart Data. All rights reserved.

Intelligence · Automation · Advantage

All Insights
Power BI

Implementing Power BI Dataflow Incremental Refresh with Change Data Capture to Optimize Enterprise ETL Pipeline Performance and Reduce Source System Load

Full-load ETL patterns collapse under enterprise data volumes, hammering source systems and bloating refresh windows. This lesson teaches you to combine SQL Server Change Data Capture with Power BI dataflow incremental refresh to build an ETL pipeline that processes only changed rows — slashing refresh times and source load in a single architectural move.

🌱 Foundation18 min readNov 18, 2026Updated Nov 18, 2026
Implementing Power BI Dataflow Incremental Refresh with Change Data Capture to Optimize Enterprise ETL Pipeline Performance and Reduce Source System Load
On this page
  • Introduction
  • Prerequisites
  • Why Full-Load ETL Breaks at Enterprise Scale
  • Understanding Change Data Capture at the Database Level
  • Enabling CDC on Your Source Table
  • Building a CDC Staging View
  • How Power BI Dataflow Incremental Refresh Works
  • Configuring Incremental Refresh on a Power BI Dataflow Entity
  • Step 1: Create the Required Parameters in Power Query Online
  • Step 2: Connect to the CDC Staging View
  • Step 3: Apply the Date Filter Using the Parameters
  • Step 4: Enable the Incremental Refresh Policy
  • Step 5: Validate the First Refresh
  • Integrating the CDC Pattern with Downstream Datasets
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Summary & Next Steps

Implementing Power BI Dataflow Incremental Refresh with Change Data Capture to Optimize Enterprise ETL Pipeline Performance and Reduce Source System Load

Introduction

Picture this: your enterprise's sales reporting dataflow processes 50 million transaction records every morning. It takes two hours to complete, your SQL Server source system crawls under the query load, and your Power BI capacity is pegged at near-maximum resource consumption during the refresh window. Your sales team starts their day with stale data, your DBA is sending you angry emails, and your capacity admin is questioning whether you need to upgrade your Premium SKU. Sound familiar?

This scenario plays out in enterprises of every size, and the root cause is almost always the same: a full-load ETL pattern where your dataflow pulls every row from the source on every refresh cycle, even though only a tiny fraction of that data actually changed. The solution is incremental refresh — but not just the basic kind. When you combine Power BI Dataflow Incremental Refresh with Change Data Capture (CDC) at the source system level, you get a genuinely surgical approach to ETL that processes only the rows that actually changed, dramatically reducing both refresh duration and source system stress.

By the end of this lesson, you'll understand exactly how dataflow incremental refresh works under the hood, how CDC feeds it the precise change set it needs, and how to configure the entire pipeline from SQL Server through to your Power BI dataflow. You'll walk away able to design and implement this pattern in a real enterprise environment.

What you'll learn:

  • What incremental refresh is and why the full-load pattern fails at scale
  • How Change Data Capture works at the database level and what data it produces
  • How to configure a Power BI dataflow with incremental refresh policies
  • How to wire a CDC-sourced staging table into a dataflow incremental refresh pattern
  • How to validate that your incremental refresh is working correctly and troubleshoot common failures

Prerequisites

Before diving in, you should be comfortable with the basics of Power BI Dataflows: Centralized ETL for the Enterprise — specifically how to create a dataflow, build entities with Power Query Online, and schedule a refresh. You should also have a working understanding of relational databases and T-SQL at a beginner level (SELECT statements, WHERE clauses, timestamps). Access to a Power BI Premium or Premium Per User workspace is required, since dataflow incremental refresh is a Premium feature.


Why Full-Load ETL Breaks at Enterprise Scale

Let's build intuition before touching any configuration screens.

A full-load ETL pattern is conceptually simple: every refresh cycle, truncate the target and reload everything from the source. When your fact table has 500,000 rows and your refresh runs nightly, this is perfectly fine. When that table grows to 50 million rows and you need hourly refreshes, you've got a serious problem.

Think of it like a librarian who, every hour, reads every book in the library to check if any pages changed, instead of just checking the returned books pile. The work scales with the total collection size, not with the volume of actual changes. That's the full-load pattern.

The costs are real and compound in multiple directions:

Source system load. A full table scan on a 50-million-row transactional table competes with your production OLTP workload. During peak hours, that query can degrade application response times, trigger lock contention, and alarm your DBA team. Even with read replicas, you're generating unnecessary I/O.

Refresh duration. Processing 50 million rows through Power Query Online takes time — time spent on network transfer, query folding execution, transformation logic, and writing the result to your dataflow's Azure Data Lake Storage Gen2 backing store. Long refresh windows mean stale data for longer.

Capacity resource consumption. Premium capacity memory and CPU are finite. A heavy dataflow refresh that monopolizes resources can degrade interactive query performance for report users running concurrently.

Key insight

The incremental refresh pattern inverts the scaling relationship. Instead of refresh time growing with total data volume, it grows with the volume of changes in the refresh period. On a mature dataset where daily changes represent 0.5% of total rows, that's a 200x potential efficiency gain.


Understanding Change Data Capture at the Database Level

Change Data Capture is a feature built into SQL Server (and Azure SQL Database, Azure Synapse, and others) that tracks every INSERT, UPDATE, and DELETE operation on a table at the database engine level, writing those changes to a set of system tables in a dedicated cdc schema.

Here's the key distinction: CDC is not application-level logging. It operates at the transaction log level, meaning it captures changes regardless of which application, user, or process made them. It's authoritative, low-overhead (the work happens asynchronously from your transactions), and produces a structured, queryable record of every change.

When CDC is enabled on a table — say, dbo.SalesOrders — SQL Server begins writing changed rows to a corresponding capture table like cdc.dbo_SalesOrders_CT. Each row in that capture table includes:

  • __$start_lsn: the Log Sequence Number of the transaction that made the change (essentially a precise timestamp in log-space)
  • __$operation: a numeric code indicating whether this was an INSERT (2), DELETE (1), or the before/after images of an UPDATE (3 and 4)
  • All the original table columns with their values at the time of the change

Additionally, CDC provides helper functions like cdc.fn_cdc_get_all_changes_dbo_SalesOrders() and sys.fn_cdc_map_time_to_lsn() that let you query changes within a specific time window — which is exactly what we need for incremental refresh.

Note

CDC is distinct from SQL Server's simpler "Change Tracking" feature. Change Tracking only tells you which rows changed; CDC tells you what the values were before and after the change. For most ETL scenarios, CDC gives you more flexibility, though it does consume more storage for the capture tables.

Enabling CDC on Your Source Table

Before configuring anything in Power BI, you need to enable CDC on the source database and the specific tables you want to track. Connect to your SQL Server instance and run the following:

-- Step 1: Enable CDC on the database
USE SalesDB;
EXEC sys.sp_cdc_enable_db;

-- Step 2: Enable CDC on the specific table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'SalesOrders',
    @role_name     = NULL,  -- NULL means no gating role; adjust for your security needs
    @supports_net_changes = 1;

The @supports_net_changes = 1 parameter is important — it tells CDC to also generate a "net changes" function that consolidates multiple operations on the same row within a time window into a single net result. If a row was updated three times in one hour, the net changes view shows you the final state rather than three intermediate states. For most Power BI ETL scenarios, net changes is exactly what you want.

After enabling CDC, SQL Server creates the capture table and starts populating it. Give it a few minutes and then verify:

-- Confirm CDC is enabled on the table
SELECT name, is_tracked_by_cdc 
FROM sys.tables 
WHERE name = 'SalesOrders';

-- Preview some CDC records
SELECT TOP 100
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS change_time,
    __$operation,
    OrderID,
    CustomerID,
    OrderAmount,
    ModifiedDate
FROM cdc.dbo_SalesOrders_CT
ORDER BY __$start_lsn DESC;

Building a CDC Staging View

Here's a practical technique that makes connecting CDC to Power BI much cleaner: create a SQL view that presents CDC changes as a simple, timestamp-filtered table that Power Query can fold against efficiently.

CREATE VIEW dbo.vw_SalesOrders_Changes
AS
SELECT
    OrderID,
    CustomerID,
    OrderAmount,
    OrderStatus,
    ModifiedDate,
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS CDC_CaptureTime,
    __$operation AS CDC_Operation
FROM cdc.dbo_SalesOrders_CT
WHERE __$operation IN (2, 4)  -- INSERT and UPDATE after-image only
-- DELETE handling requires separate logic depending on your semantic layer design

By filtering to operations 2 (insert) and 4 (after-image of update), you're presenting only the latest state of rows that were added or changed. This view becomes your dataflow's source — not the original SalesOrders table.

Warning

CDC capture tables have a retention window configured by your SQL Server agent cleanup job (typically 3 days by default). If your dataflow fails and doesn't run for longer than that retention window, you'll miss changes and need to fall back to a full reload. Always configure refresh failure alerting to catch this scenario early. See Implementing Power BI Scheduled Refresh and Refresh Failure Alerting for Enterprise Dataset Reliability for guidance.


How Power BI Dataflow Incremental Refresh Works

Now that you understand what CDC produces, let's understand what Power BI dataflow incremental refresh expects to consume.

Power BI dataflow incremental refresh is a policy you define on a dataflow entity that tells the refresh engine: "Instead of reloading all rows, only query rows where ModifiedDate falls within a rolling window, then merge those results into the stored entity data." The engine uses two special Power Query parameters — RangeStart and RangeEnd — as the boundaries of that query window.

Here's the mental model: your dataflow entity's data is stored in partitioned Parquet files in Azure Data Lake Storage Gen2. When incremental refresh runs, it only rewrites the partitions that fall within the recent window (say, the last 3 days), leaving historical partitions untouched. The cumulative total keeps growing as new partitions are appended, but each individual refresh only touches a small slice.

The incremental refresh policy has two key settings:

  • Store rows from the last N years/months/days/hours: This defines the total historical data you want to retain in the entity. Everything older gets pruned.
  • Refresh rows from the last N days/hours: This defines the rolling window that gets re-queried and merged on each refresh cycle. It should be wide enough to catch delayed data arrival (late-arriving facts) and to handle any retry scenarios.

Tip

Set your incremental refresh window wider than you think you need. If your refresh interval is hourly and your CDC pipeline has up to 15-minute latency, a 1-day refresh window (re-processing the last 24 hours) gives you plenty of safety margin while still being dramatically more efficient than a full load.


Configuring Incremental Refresh on a Power BI Dataflow Entity

Let's walk through the full configuration. You'll be working in the Power BI Service, inside a Premium workspace.

Step 1: Create the Required Parameters in Power Query Online

Navigate to your workspace, open your dataflow for editing (or create a new one), and open the Power Query Online editor. You need to create two parameters with exact, case-sensitive names.

In Power Query Online, select "Manage parameters" from the Home ribbon. Create the first parameter:

  • Name: RangeStart
  • Type: Date/Time
  • Current Value: set a reasonable test date, such as the first day of the current month

Create the second parameter:

  • Name: RangeEnd
  • Type: Date/Time
  • Current Value: set to today's date

These parameter names must be exactly RangeStart and RangeEnd — Power BI recognizes them by convention and uses them to inject the partition window boundaries at refresh time.

Step 2: Connect to the CDC Staging View

Add a new entity to your dataflow. Choose your SQL Server connection and navigate to the vw_SalesOrders_Changes view you created earlier. Load it into Power Query Online.

Your initial M query will look something like this:

let
    Source = Sql.Database("your-server.database.windows.net", "SalesDB"),
    dbo_vw_SalesOrders_Changes = Source{[Schema="dbo", Item="vw_SalesOrders_Changes"]}[Data]
in
    dbo_vw_SalesOrders_Changes

Step 3: Apply the Date Filter Using the Parameters

Now you need to add a filter step that uses RangeStart and RangeEnd to limit the rows fetched. This is the step that enables query folding — when Power Query can translate this filter into a SQL WHERE clause, it pushes the predicate down to the source and only transfers matching rows across the network.

In Power Query Online, select the CDC_CaptureTime column, then apply a custom row filter. In the advanced editor, modify the M code to add the filter step:

let
    Source = Sql.Database("your-server.database.windows.net", "SalesDB"),
    dbo_vw_SalesOrders_Changes = Source{[Schema="dbo", Item="vw_SalesOrders_Changes"]}[Data],
    FilteredRows = Table.SelectRows(
        dbo_vw_SalesOrders_Changes,
        each [CDC_CaptureTime] >= RangeStart and [CDC_CaptureTime] < RangeEnd
    )
in
    FilteredRows

Warning

Query folding is non-negotiable for incremental refresh to work correctly. If this filter step doesn't fold — meaning Power Query downloads all rows and filters in memory — you've gained nothing in terms of source load reduction, and the incremental refresh policy may not apply correctly. Verify folding by right-clicking the FilteredRows step in Power Query Online and checking if "View Native Query" is available and shows a SQL WHERE clause with your date boundaries.

Step 4: Enable the Incremental Refresh Policy

Save your entity and return to the dataflow authoring view. Right-click the entity name in the entity list on the left panel and select "Incremental refresh." A configuration panel appears.

Configure the policy:

  • Enable incremental refresh: toggle on
  • Choose the date column: select CDC_CaptureTime
  • Store rows from the last: set to 3 years (adjust to your retention requirements)
  • Refresh rows from the last: set to 3 days

Save the policy and then save and publish the dataflow.

Step 5: Validate the First Refresh

The very first refresh after enabling incremental refresh is always a full historical load — Power BI needs to populate all the historical partitions up to the retention boundary. This is expected and normal. For a large table, this initial refresh will take longer than your steady-state incremental refreshes will.

After the first refresh completes, trigger a second refresh manually and time it. You should see the duration drop significantly — from the time it takes to process all 50 million rows to the time it takes to process only the rows captured by CDC in the last 3 days.


Integrating the CDC Pattern with Downstream Datasets

Your dataflow entity now contains a merged, incrementally refreshed history of all inserts and updates tracked by CDC. But there's an important architectural consideration: the CDC view produces change events, not a fully deduplicated current-state table.

If a single OrderID was updated three times in the last 3 days, all three change records may be in your refresh window. Before this entity feeds downstream reports or semantic models, you likely want a second dataflow entity — a "computed entity" in Power BI dataflow terminology — that deduplicates to the latest record per key.

Create a new entity in the same dataflow that references the first entity and applies deduplication:

let
    Source = #"SalesOrders_Changes",  // reference to your incremental entity
    SortedByDate = Table.Sort(Source, {{"CDC_CaptureTime", Order.Descending}}),
    DedupedByOrderID = Table.Distinct(SortedByDate, {"OrderID"})
in
    DedupedByOrderID

This computed entity gives you the clean, current-state dimension or fact table that your semantic model needs. For more on how this fits into a broader enterprise architecture, see Power BI and Azure Synapse: Building Enterprise Analytics Architecture from Scratch.

Key insight

The two-entity pattern — one incremental entity consuming CDC changes, one computed entity presenting the deduplicated current state — is the production-grade approach. It separates the concerns of capturing changes from presenting current state, and it gives you an audit trail of changes in the first entity that you can query independently if needed.

You can connect your semantic models to the computed entity using live connections across workspaces, as described in Implementing Power BI Dataset Sharing and Cross-Workspace Live Connections to Build a Reusable Enterprise Semantic Layer.


Hands-On Exercise

Work through this sequence to build a complete working prototype of the pattern:

Setup (15 minutes): On a SQL Server instance you have access to — a local developer instance or Azure SQL Database free tier works fine — create a simple Orders table with columns OrderID INT, CustomerID INT, Amount DECIMAL(10,2), OrderStatus VARCHAR(20), and ModifiedDate DATETIME2. Populate it with 1,000 rows using a simple INSERT loop. Enable CDC on the database and table using the T-SQL from the earlier section. Insert 100 new rows, then update 50 existing rows and confirm records appear in the CDC capture table.

Staging View (10 minutes): Create the vw_Orders_Changes view following the pattern from earlier. Run a SELECT against it and confirm you see INSERT and UPDATE records with correct CDC_CaptureTime values.

Dataflow Configuration (20 minutes): In a Premium or PPU workspace, create a new dataflow. Create the RangeStart and RangeEnd parameters. Add an entity sourced from vw_Orders_Changes. Apply the date range filter. Add an incremental refresh policy with a 1-year store window and a 7-day refresh window. Save and publish.

Validation (10 minutes): Trigger a manual refresh and observe the duration in the dataflow refresh history. Then insert 20 more rows into the source Orders table, wait a minute for CDC to capture them, and trigger another refresh. Compare the second refresh duration to the first. Navigate to the dataflow entity's data preview and confirm the new rows appear.

Stretch goal: Examine the refresh history in the workspace's dataflow management view and confirm that the second refresh queried a fraction of the rows the first refresh did. Use Monitoring Power BI Performance with Premium Metrics: A Complete Guide to Proactive Optimization to observe the capacity resource usage during both refreshes.


Common Mistakes & Troubleshooting

Incremental refresh policy doesn't appear on the entity. This is almost always a workspace licensing issue — the workspace must be backed by Premium capacity or Premium Per User. Verify the workspace settings show a diamond icon (Premium) rather than the standard Pro icon.

Refresh window captures no rows / entity shows empty data. Check that your CDC_CaptureTime column is typed as DateTime in Power Query, not Text. A type mismatch means the comparison with RangeStart and RangeEnd silently evaluates to false. Also confirm that your SQL Server's CDC cleanup agent hasn't pruned the capture records you're trying to query.

Query folding breaks on the filter step. This often happens when you add transformation steps between the source and the filter. Power Query's folding engine can't always fold through complex intermediate steps. Try moving the date filter to be the first transformation step immediately after the source, before any column renames or type changes.

The initial full refresh times out. For very large tables, the initial historical load can exceed Premium capacity timeout limits. Consider temporarily narrowing the "store rows from the last" window to 6 months for the first load, then extending it in subsequent refresh cycles once the initial partition structure is built.

Duplicate rows appearing after several refresh cycles. This is usually caused by the refresh window being too narrow — late-arriving CDC records fall outside the window and aren't caught. Widen your refresh window (the "refresh rows from the last" setting) to give adequate coverage for your data arrival latency.

Tip

Always test your incremental refresh configuration by explicitly setting RangeStart and RangeEnd parameter values to a known time window and running a preview in Power Query Online before publishing. This lets you verify exactly which rows the query returns before committing to the production refresh policy.


Summary & Next Steps

You've now covered the full pattern: understanding why full-load ETL fails at enterprise scale, enabling CDC at the SQL Server level to produce a precise, queryable change stream, configuring Power BI dataflow incremental refresh with the RangeStart/RangeEnd parameter convention, applying a folding-compatible date filter, and building the two-entity architecture that separates change capture from current-state presentation.

The efficiency gains from this pattern are substantial and compound over time. As your data volumes grow, your refresh times stay roughly constant because they scale with change volume, not total volume. Your source system sees dramatically reduced query load. Your capacity resources are freed up for interactive query workloads and other dataflows.

From here, consider these natural next steps:

  • Explore Designing and Implementing Power BI Incremental Refresh for Large-Scale Enterprise Datasets to apply the same incremental refresh principles directly at the semantic model (dataset) level, working in conjunction with your dataflow.
  • Review your governance posture around dataflows using Power BI Governance: Workspaces, Permissions, and Audit Logging to ensure your CDC-sourced data entities have appropriate access controls and audit coverage.
  • If your source systems include on-premises SQL Server rather than Azure SQL Database, review Connecting Power BI to On-Premises Data Sources Using the On-Premises Data Gateway to ensure your gateway configuration supports the query folding requirements of this pattern.

The combination of CDC and dataflow incremental refresh is one of the highest-leverage techniques available to enterprise Power BI architects. Master it here and you'll have a tool that pays dividends across every large-scale ETL pipeline you build.

Work With Us

From insight to implementation

Reading is the start. When you're ready to build the data, automation, or AI systems behind it, our team turns strategy into shipped results.

Let's Build

Enterprise Power BI

Previous

Implementing Power BI Deployment Pipeline APIs with Azure DevOps Release Gates to Enforce Automated Quality Checks Before Production Promotion

Related Insights

Power BIPractitioner

DAX Table Expansion and Relationship Propagation: How RELATED, RELATEDTABLE, and Filter Context Cross Model Boundaries in Complex Star and Snowflake Schemas

23 min
Power BIFoundation

Adding Tooltips and Drill-Through Actions to Your First Power BI Report: A Beginner's Guide to Interactive Navigation

17 min
Power BIExpert

Implementing Power BI Deployment Pipeline APIs with Azure DevOps Release Gates to Enforce Automated Quality Checks Before Production Promotion

29 min

On this page

  • Introduction
  • Prerequisites
  • Why Full-Load ETL Breaks at Enterprise Scale
  • Understanding Change Data Capture at the Database Level
  • Enabling CDC on Your Source Table
  • Building a CDC Staging View
  • How Power BI Dataflow Incremental Refresh Works
  • Configuring Incremental Refresh on a Power BI Dataflow Entity
  • Step 1: Create the Required Parameters in Power Query Online
  • Step 2: Connect to the CDC Staging View
  • Step 3: Apply the Date Filter Using the Parameters
  • Step 4: Enable the Incremental Refresh Policy
  • Step 5: Validate the First Refresh
  • Integrating the CDC Pattern with Downstream Datasets
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Summary & Next Steps