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 😊
Hi tomp This usually happens when the refresh is running in append mode, so every refresh just inserts the same records again. Adding an index or incremental column by itself won’t prevent duplicates if the destination table isn’t using a merge logic.
The usual way to fix this is:
Define a real unique key (for example the Salesforce Id).
Load the data using an UPSERT / MERGE strategy:
If the record already exists → update it
If it doesn’t exist → insert it
How you implement this depends a lot on the warehouse you’re loading into (BigQuery, Snowflake, Postgres, SQL Server, etc.), since each one handles MERGE a bit differently.
If you’d rather not manage this manually, some data sync tools already handle it. For example, with Windsor.ai, when syncing Salesforce to SQL-based warehouses, you can define “columns to match” and the refresh runs as an UPSERT, so records are updated instead of duplicated.
The key takeaway is that as long as the process is pure append, duplicates are expected. You’ll need a proper unique key and a MERGE/UPSERT-based refresh to avoid them
Hope this helps
