An ADF pipeline ran twice and attempted to insert the same rows on the second run. Duplicate records in the source data before the target constraint was validated. Upsert logic in ADF or dbt is not correctly identifying existing rows to update vs. insert

Step 1: Identify the duplicate: the error message includes the constraint name and the duplicate key value. Find the rows: SELECT , COUNT(*) FROM GROUP BY HAVING COUNT(*) > 1;. Step 2: For ADF copy activity, switch the write behavior from 'Insert' to 'Upsert' and configure the key columns in the sink settings.. Step 3: For idempotent pipelines, use MERGE or INSERT ... WHERE NOT EXISTS: IF NOT EXISTS (SELECT 1 FROM target WHERE id = @id) INSERT INTO target VALUES (...);

Medium severitydata integrity

Power BI Refresh Error:
2627

What does this error mean?

An INSERT or UPDATE attempted to insert a duplicate value into a column with a PRIMARY KEY or UNIQUE constraint.

Common causes

1An ADF pipeline ran twice and attempted to insert the same rows on the second run
2Duplicate records in the source data before the target constraint was validated
3Upsert logic in ADF or dbt is not correctly identifying existing rows to update vs. insert

How to fix it

1Step 1: Identify the duplicate: the error message includes the constraint name and the duplicate key value. Find the rows: SELECT <key_columns>, COUNT(*) FROM <table> GROUP BY <key_columns> HAVING COUNT(*) > 1;
2Step 2: For ADF copy activity, switch the write behavior from 'Insert' to 'Upsert' and configure the key columns in the sink settings.
3Step 3: For idempotent pipelines, use MERGE or INSERT ... WHERE NOT EXISTS: IF NOT EXISTS (SELECT 1 FROM target WHERE id = @id) INSERT INTO target VALUES (...);

Frequently asked questions

What is the difference between error 2627 and error 2601?

Error 2627 is raised for PRIMARY KEY and UNIQUE constraints. Error 2601 is raised for unique indexes that are not backing a constraint. Both indicate a duplicate key but are enforced at different levels.

How do I make an ADF copy activity upsert instead of insert?

In the sink settings, set Write behavior to 'Upsert', then specify the key columns to match on. ADF generates a MERGE statement using those columns.

Can I ignore duplicate key errors in SQL Server?

Yes — for a table with a UNIQUE index (not a constraint), you can use INSERT IGNORE equivalent: INSERT INTO target SELECT * FROM source WHERE NOT EXISTS (SELECT 1 FROM target WHERE target.id = source.id).

Official documentation: https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-2627-database-engine-error