Best Practices for Handling Incremental Data Loads in Dataflow Gen2

Hi codeautomation​,

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

Hi codeautomation​ ,

Using notebooks is better option than Dataflows gen2.If you want to go for dataflows gen2 only then you can refer this documentation.

 https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-incremental-refresh

Hi codeautomation​,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

Hi codeautomation​,

I agree with tayloramy​. I also have made better experience with notebooks or depending on your requirements even with the copy job item (be aware of the limitations for this item!).

Probably also worth checking out the Microsoft decision guide for data integration:
Decision Guide for Data Movement and Transformation - Microsoft Fabric | Microsoft Learn

Best regards!

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

Hi codeautomation​,

Personally I would (and do) use notebooks to handle incremental data loads. 

Dataflow Gen 2 can do it, but not in the way you expect. 

https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-incremental-refresh

Dataflow gen 2 will essentially partition your data, and then refresh everything in the newest partition, meaning that if a row from a previous partition was modified you could end up with duplicate records.