Forum Discussion
Append Table action in Data pipeline duplicates all data rows instead of appending the new rows
Hi Anonymous ,
Thank you for engaging with the Microsoft Fabric Communit. and spencer_sa explained it well.
To summarize.
-
Append mode in Microsoft Fabric’s Copy Data Assistant will re-add all source records on each refresh, even if they already exist in the destination.
-
This is expected behavior, as Append mode does not perform deduplication or check for changes.
-
Overwrite mode is your best option if you want to always keep only the latest version of the source data.
-
For more advanced control, you’ll need to use a Notebook with Spark SQL or PySpark.
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
-
Hi Anonymous ,
1. Append mode in Microsoft Fabric’s Copy Data pipelines does not check for existing records, it simply adds all data from the source to the destination each time it runs. Although this method is efficient for data ingestion, it does not prevent duplicates or track changes, which can result in data duplication if the source includes both old and new records.
2. Additionally, duplicated primary keys can disrupt reporting and cause inconsistencies. Since Append mode does not enforce uniqueness or support upserts (updates/inserts), you will need to implement additional logic using Spark Notebooks or SQL MERGE to maintain data integrity in your lakehouse.
3. Conversely, Overwrite mode replaces the entire dataset with each run. While suitable for full refreshes, it is inefficient for frequently updated large datasets. For substantial data updates, utilizing SparkSQL or PySpark in Notebooks is recommended in Fabric.
4. A key distinction between Fabric Pipelines and Power Platform Dataflows is that Dataflows support incremental refresh and change tracking, whereas Fabric Pipelines do not. This can be confusing when working across platforms, so the best choice depends on your specific requirements.
In Simply:
Append mode = fast but duplicates everything. Overwrite mode = clean slate every run, but not scalable for big data. Upserts/deduplication = use Notebooks (Spark/PySpark) or SQL MERGE logic. Cross-platform behavior = yes, inconsistent; choose based on capabilities needed.Did I answer your question? Mark my post as a solution.
Hi V-yubandi-msft ,
I would like to raise a few concerns regarding the behavior of the Append mode in Data Pipelines within Microsoft Fabric:
Data Duplication in Append Mode
Data Pipelines are recommended by Microsoft for uploading large datasets into OneLake. However, if the Append mode re-adds all records during each refresh, even when they already exist in the destination—it leads to exponential growth in dataset size. In that case, what is the benefit of using pipelines for large datasets if the result is duplicated data with every refresh?Impact on Data Integrity
When the primary key is duplicated with each refresh, the resulting dataset becomes unusable for reporting purposes. This requires additional logic in the pipeline to clean or de-duplicate data, which adds unnecessary complexity.Limitations of Overwrite Mode
I understand that the Overwrite option is available, it drops and reloads the entire dataset from the source during each refresh. However, for large datasets—especially where pipelines are recommended—reloading the full dataset each time is highly resource-intensive and time-consuming, contradicting the purpose of using Data Pipelines for large-scale data operations.Inconsistent Behavior Across Platforms
Dataflows created within a Fabric workspace also duplicate data in Append mode. However, Dataflows created via Power Platform behave differently: they append only new and updated records rather than duplicating the entire dataset. This inconsistency across Fabric Data Pipelines, Fabric Dataflows, and Power Platform Dataflows raises confusion and makes it difficult to standardize implementation.
Could you please confirm if there are any plans to improve Append mode in Fabric pipelines to support delta loads or de-duplication?
Community SupportHi Anonymous ,
1. Append mode in Microsoft Fabric’s Copy Data pipelines does not check for existing records, it simply adds all data from the source to the destination each time it runs. Although this method is efficient for data ingestion, it does not prevent duplicates or track changes, which can result in data duplication if the source includes both old and new records.
2. Additionally, duplicated primary keys can disrupt reporting and cause inconsistencies. Since Append mode does not enforce uniqueness or support upserts (updates/inserts), you will need to implement additional logic using Spark Notebooks or SQL MERGE to maintain data integrity in your lakehouse.
3. Conversely, Overwrite mode replaces the entire dataset with each run. While suitable for full refreshes, it is inefficient for frequently updated large datasets. For substantial data updates, utilizing SparkSQL or PySpark in Notebooks is recommended in Fabric.
4. A key distinction between Fabric Pipelines and Power Platform Dataflows is that Dataflows support incremental refresh and change tracking, whereas Fabric Pipelines do not. This can be confusing when working across platforms, so the best choice depends on your specific requirements.
In Simply:
Append mode = fast but duplicates everything.
Overwrite mode = clean slate every run, but not scalable for big data.
Upserts/deduplication = use Notebooks (Spark/PySpark) or SQL MERGE logic.
Cross-platform behavior = yes, inconsistent; choose based on capabilities needed.
Did I answer your question? Mark my post as a solution.