We have some ETL jobs that feed our data warehouses. If you try to insert a duplicate primary key, the primary key constraint should cause the update to fail. Do you have a primary key on the destination table?

What we are currently working on is having the ETL update continue in the event of a duplicate primary key and write the offending row to an alert log / email notification. Our current ETL process fails once a duplicate primary key is encountered. When it is restarted it begins from the failed row.

Would you want this process to continue or stop once a duplicate is encountered? If you want it to stop, make sure a primary key is on the destination table.