Data Source Processing fails at the ODS append stage

15 February 2022 15:24
Updated

Data Source Processing fails (at the ODS append stage) with the following error message: Violation of PRIMARY KEY constraint ‘PK_RMC_1_1_Jobs’. Cannot insert duplicate key in object ‘dbo.ObjectName’. The duplicate key value is (XXX).
The statement has been terminated.
TableNameSource: SOURCE
TableNameDestination: [DESTINATION]

Answer
The reason for this error is a duplicate key in the ODS table. There could be several root causes for this, check the following

Corrupt surrogate key table
Inconsistent relationship to other tables. E.g. A change in a PK in another related table, without changing the relationship columns can result in duplicate hits when joining to that related table in a FK surrogate key lookup.