Duplicate keys with conflicting values

• A record was changed in one copy of the data and not in another, and the copies were later combined.
• An update was stored as a new row instead of replacing the old one.
• The same identifier was issued twice, so the rows describe two different things.

• Joins multiply rows, so every total downstream is inflated.
• Which value a report shows depends on row order, and can change from one run to the next.
• Loading into a table with a unique key either fails or silently keeps whichever row came last.

• Group by the key and count. Any key with more than one row is a candidate.
• Within each group, list the columns where values differ. One changed field suggests an update, many suggest two different entities.
• Check for a timestamp or version column that orders the rows.

• Work out whether the rows are versions of one record or two records sharing an identifier by mistake. The repairs are different.
• For versions, state the rule that picks the surviving value, such as the most recent update or the most trusted source, and apply it field by field.
• Where no rule settles a field, send it to a person instead of keeping whichever row happens to come first.
• Keep the rows that lost. They are the only record of what the conflict was.

• Exact duplicate rowsThe same row appears more than once, identical in every column.
• Near-duplicate recordsTwo rows describe the same real thing but differ enough that an exact comparison misses them.
• The same entity in two systemsOne person or organization exists in two systems under different identifiers, with nothing linking the records.

TermsPrimary key, Survivorship, Golden record, Source of truth