Forum Discussion
Duplicate rows in dataflow
Hi tomperro
Thanks for reaching out to Fabric Community.Your dataflow is currently appending new rows each time, which is why you end up with duplicates.
Here are Best approaches pick the one that best fits your scenario:
-
In your Dataflow destination settings (Gen2), disable the automatic settings and under Update methods choose Replace. This will truncate and reload the target table on every refresh, eliminating duplicates completely. Reference link: https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-data-destinations-and-managed-settings
- Stage the refreshed data in a temporary table, then run a T-SQL MERGE statement against your warehouse target using the Salesforce record ID as the key. This upserts new rows and updates existing ones without duplication
Reference link: https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16
Choose the method that aligns with your performance and audit requirements.
If this answer solves your issue, please give us a Kudos and mark it as Accepted Solution.
Kind regards,
Community Support Team _ C Srikanth.-
Hi tomperro ,
It sounds like the issue you're encountering is due to the dataflow performing an append operation rather than a full refresh or upsert into your warehouse table. Adding an index column alone won't prevent duplicates unless you're using it as part of a deduplication step. To avoid duplicates, you’ll need to implement logic in your dataflow that either deletes existing data before each refresh or identifies and removes duplicates based on a unique key, such as a Salesforce record ID.
Another approach is to stage the incoming data in a temporary table and then use a transformation (like a merge or deduplicate step) before writing to the final destination. If you're using Microsoft Fabric or a similar platform with dataflows, you might also consider enabling incremental refresh or configuring the destination settings to overwrite the table on refresh, if that option is available.
Ok, those make sense, but how do I do that 😊