Forum Discussion
Incremental Refresh on Dataflow Gen 2 creating duplicate records
Hi all,
I have a transaction table in a Dataflow Gen 2 for which I'm trying to enable incremental refresh. I first did a full refresh, then I set up the incremental refresh, but when I run it, it creates duplicate records for the date range of the incremental refresh.
My table is a transaction table with two available date columns: Transaction Date and Journal Date. Neither of these values ever change after the record is created. The table has a composite primary key consisting of six fields (all fields are included in the dataflow and are written to the destination).
I have set up the incremental refresh as follows:
- Column to filter by: Transaction Date
- Extract data from the past: 3 days
- Bucket size: days
- Only extract new data when the max value in this column changes: Journal Date
I am writing the data to a Lakehouse, and the method is "Replace".
I have struggled with this for months, but I can't figure out how to get this to work without duplicating records. I assume that perhaps the issue is the composite primary key, so Fabric is not able to correctly identify existing rows by a unique value.
Does anyone have any suggestions for what else to try? And/or, has anyone successfully gotten incremental refresh to work using two data columns and a composite primary key in the data source?
Thanks.
Hi craigmday,
Thank you for reaching out to the Microsoft Fabric Community Forum.The duplicates occur because Dataflow Gen2 incremental refresh does not perform row-level deduplication or upsert based on primary/composite keys. When the incremental refresh runs, it reprocesses the defined time window (last 3 days) and writes those records again to the Lakehouse. Since Lakehouse tables do not enforce primary keys, the same rows can be inserted again. In your setup the filter column is Transaction Date, but change detection uses Journal Date. Using different columns can cause the same incremental partition to be processed again, which results in duplicate rows.
To resolve this, configure the incremental refresh to use the same column for both filtering and change detection (e.g., Transaction Date) and ensure the incremental filter step supports query folding and is applied early in Power Query. If duplicates can still occur, implement deduplication using the composite key columns in the Dataflow or use a Lakehouse MERGE (upsert) process with a staging table so existing rows are updated instead of inserted again. This ensures the incremental refresh only loads new data and prevents duplicate records.
Hope this clarifies. Let us know if you have any doubts regarding this. Please feel free to reach out to us. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
2 Replies
Hi.
Thank you very much for the detailed explanation. This is the first time someone has been able to explain the reasons clearly to me.
I did test as well using the same date column and still got duplication, so I will explore the deduplication options.
Thanks again for the quick and informative reply.
Hi craigmday,
Thank you for reaching out to the Microsoft Fabric Community Forum.The duplicates occur because Dataflow Gen2 incremental refresh does not perform row-level deduplication or upsert based on primary/composite keys. When the incremental refresh runs, it reprocesses the defined time window (last 3 days) and writes those records again to the Lakehouse. Since Lakehouse tables do not enforce primary keys, the same rows can be inserted again. In your setup the filter column is Transaction Date, but change detection uses Journal Date. Using different columns can cause the same incremental partition to be processed again, which results in duplicate rows.
To resolve this, configure the incremental refresh to use the same column for both filtering and change detection (e.g., Transaction Date) and ensure the incremental filter step supports query folding and is applied early in Power Query. If duplicates can still occur, implement deduplication using the composite key columns in the Dataflow or use a Lakehouse MERGE (upsert) process with a staging table so existing rows are updated instead of inserted again. This ensures the incremental refresh only loads new data and prevents duplicate records.
Hope this clarifies. Let us know if you have any doubts regarding this. Please feel free to reach out to us. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
