Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
3,826 questions
Azure Synapse Creating random duplicate data while inserting from staging to final table
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(102.4, 32%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMP%3C/text%3E%3C/svg%3E)
Monika Patel
0
Reputation points
I have simple task at hand. I have close to 200 unique million records seating in a staging table on Azure Synapse. The task is to run an insert statement to insert data into final table. Sounds simple, but here is the problem I am running into for which I am scratching my head. Every time I execute insert random rows are duplicated in final table. I have checked multiple times that staging table has all unique values based on combination of composite key of 3 columns.
Has anyone ran into this weird issue before? Any help would be highly appreciated.
{count} votes
-
PRADEEPCHEEKATLA-MSFT 70,581 Reputation points Microsoft Employee2023-05-01T04:25:56.2733333+00:00 @Monika Patel - Thanks for the question and using MS Q&A platform.
It sounds like you are experiencing an issue with duplicate data being inserted into your final table in Azure Synapse Analytics. Here are some steps you can take to diagnose and resolve the issue:
Check the insert statement: Double-check your insert statement to make sure that it is correctly inserting data from the staging table into the final table. Make sure that you are not accidentally inserting duplicate rows or missing any rows.
Check the final table schema: Make sure that the final table schema matches the staging table schema and that there are no missing or extra columns. Also, make sure that the data types and constraints are correctly defined.
Check for triggers or constraints: Check if there are any triggers or constraints on the final table that may be causing duplicate data to be inserted. For example, a trigger may be inserting additional rows or a constraint may be allowing duplicate data.
Check for concurrent inserts: If multiple users or processes are inserting data into the final table concurrently, it is possible that duplicate data may be inserted. You can use locking or transactions to prevent concurrent inserts.
Check for data quality issues: Check if there are any data quality issues in the staging table that may be causing duplicate data to be inserted. For example, there may be data inconsistencies or missing values that are causing duplicate rows to be inserted.
In summary, there are several possible causes for duplicate data being inserted into your final table in Azure Synapse Analytics. You can check your insert statement, final table schema, triggers or constraints, concurrent inserts, and data quality issues to diagnose and resolve the issue.
Hope this helps. Do let us know if you any further queries.
-
PRADEEPCHEEKATLA-MSFT 70,581 Reputation points Microsoft Employee
2023-05-04T05:23:46.2166667+00:00 @Hannah Roberts - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sign in to comment