Best Practices for Handling Incremental Data Loads in Dataflow Gen2

hi codeautomation​ 

I would suggest starting with this step-by-step guide: Pattern to incrementally amass data with Dataflow Gen2 - Microsoft Fabric | Microsoft Learn

It shows how to read the maximum OrderID already loaded into a Lakehouse, use that value to filter the source, and append only records with a higher ID. This keeps the basic incremental logic inside the dataflow.

A few considerations for your questions:

1. Identifying new versus changed records

The tutorial is a useful starting point for append-only data where IDs increase as records become available. It does not, by itself, capture updates to existing records, deletions, or late-arriving records with IDs below the saved maximum.

For mutable data, use a reliable change indicator, such as a last-modified timestamp or a source change feed, and define how those changes should update the destination. Filtering changed rows and appending them is not the same as updating existing rows.

2. Dataflow versus pipeline

You can keep the source filtering and transformations in Dataflow Gen2. Use a pipeline when you need to coordinate multiple steps, manage dependencies and retries, or run downstream processing after a successful load. The tutorial also includes an optional notebook-and-pipeline pattern for reloading data.

3. Failed runs and duplicates

Design recovery so that replaying a batch does not create another copy of its records. A maximum ID alone is not a recovery guarantee if a partially completed load leaves gaps.

For a more controlled implementation, load into a separate staging table, deduplicate by business key, and apply the batch through a SQL procedure or notebook using upsert logic. Advance your persisted watermark only after the destination update succeeds.

4. Performance as history grows

Make sure the incremental filter is pushed down to the source where supported. Returning fewer rows from Power Query does not necessarily mean fewer rows were read from the source. Keep only the columns you need, and use suitable source indexes where available.

As an alternative, Dataflow Gen2 also has built-in incremental refresh. This checks for changes within time buckets and replaces affected buckets in supported destinations. It can avoid custom watermark management when that model fits your data, but it is not row-level upsert or CDC. Choose a refresh window that covers expected late arrivals and corrections.

Incremental refresh in Dataflow Gen2 - Microsoft Fabric | Microsoft Learn