Forum Discussion
Dataflow Incremental Refresh Causing Duplicate Records
Hello Thorns,
Thank you for reaching out to the Microsoft fabric community forum.
The duplicate records in your warehouse table are likely due to the incremental refresh feature in Dataflow Gen2 not supporting query folding for Salesforce objects. This causes the full dataset to be pulled per bucket refresh, leading to improper replacement and duplicates, especially for modified records.
To fix this:
- Disable incremental refresh to stop further duplicates.
- Manually implement incremental load:
- Add a reference query to get the max LastModifiedDate from your warehouse table.
- Filter Salesforce data where LastModifiedDate > that value in your main query.
- Use "upsert" (with Id as primary key) or "append" as the destination update method.
- If handling deletes, include the IsDeleted field and add logic to remove corresponding rows and Test with a manual refresh, then schedule.
For existing duplicates, run a one-time deduplication before applying the new setup. This should resolve the issue effectively.
Best regards,
Ganesh Singamshetty.
Thanks for the information! I will stop using incremental refresh.
Where in the Gen2 Dataflow does it allow for upsert? I do not see that as an option.
Hello Thorns,
Thanks for the update. glad you're pausing incremental refresh to avoid more duplicates.
I apologize for the confusion in my earlier suggestion upon double-checking the Dataflow Gen2 documentation, "upsert" isn't directly available as an update method for any destination, including Warehouse. For Warehouse specifically, the only supported option is replace, with no append or upsert in the UI.
To achieve an incremental upsert-like behavior (adding new records and updating changed ones without duplicates):
- Switch your destination to a Fabric Lakehouse table (which supports both append and replace). This allows more flexibility