An analyst finds the same customer, vendor, account, filing, or transaction ID on two rows. The column is called ID, perhaps even Unique ID. The natural impulse is to fix it quickly: delete one row, merge the two, or choose the record with more complete fields.
That impulse can create a larger error than the repeated value.
Deleting a row can erase valid history. Merging can combine two different entities. Keeping both without understanding them can double a count. A repeated identifier is not yet a diagnosis. It is a question about the promise that identifier was meant to make.
The useful question is: unique within which namespace, for what kind of thing, and across what period of time?
Uniqueness has boundaries
“Unique” is often treated as a visible property of a column. It is not. It is a rule with a scope.
A database primary key is designed to identify a row uniquely within a table. It can also be made from more than one column. That matters because a single value may not be sufficient to identify the row the dataset actually contains.
Consider an order export. If each row represents an order line, the order ID may repeat correctly across several rows. The row-level identifier might be the pair of order ID and line number. Treating the order ID as a row key would make legitimate detail look like duplication.
The same issue appears when datasets combine systems. Customer ID 4821 from a CRM is not necessarily the same as customer ID 4821 from a billing system. Each system may have issued its own identifiers. In that case, the meaningful key may be source system plus customer ID.
An identifier also may persist while its descriptive information changes. A company can change names while retaining an external filing identifier. A DOI can remain the same while associated metadata is updated. The identifier is stable; the fields beside it may not be.
None of this proves that a repeated ID is correct. It shows why the repeated value alone cannot tell you what happened.
Pause before changing the records
When a supposedly unique ID appears twice, start with four questions:
1. Who issued this ID? Was it assigned by a source system, regulator, vendor, internal process, or a data-preparation step? 2. What does it identify? A row, a business entity, an account, a transaction, a filing role, or a version of a record? 3. What is its stated scope? Is it unique globally, only within one system, within a program, or only when paired with another field? 4. What kind of dataset is this? A current-state table, an event log, a transaction file, or periodic snapshots?
These questions take little time compared with unwinding a mistaken merge later.
Before normalizing values, removing duplicates, or imposing a database constraint, preserve the raw identifier and the source name. Also retain the extract date or reporting period where available. Leading zeros removed during import, a text-to-number conversion, or a join that dropped source context can manufacture an apparent collision.
Then compare the full rows, not just the ID. Look at source, timestamps, effective dates, status, related identifiers, and stable attributes such as legal name, tax registration, or country where appropriate.
What the pattern may be telling you
| Pattern | Likely interpretation | Appropriate next step |
|---|---|---|
| Same ID, same source, same extract, and matching substantive fields | Repeated extract or load row | Preserve evidence and remove only the confirmed duplicate copy. |
| Same ID, different source systems | Namespace collision | Test source system plus ID as the candidate key. |
| Same ID, different reporting dates or statuses | Valid history or snapshots | Model or retain the time dimension rather than collapsing rows. |
| Same ID, different line numbers or products | Detail-level data | Check whether the dataset is at order-line, claim-line, or similar grain. |
| Same ID, incompatible stable attributes in the same source and period | Source, transformation, or assignment issue | Flag for source verification; do not silently select a winner. |
The point is to classify the condition before deciding on a remedy. “Duplicate” is a conclusion, not a description of two cells that look alike.
Enforce rules after the rule is known
A unique constraint can be valuable. It can prevent a table from accepting repeated values in the designated column or combination of columns. But it enforces the rule you define; it does not determine whether that rule matches the business meaning of the data.
Adding a uniqueness rule too early can reject legitimate historical records. It can also encourage teams to discard exceptions that were warning them about a missing source field, an incorrect row grain, or an unresolved identity issue.
First establish the expected key from documentation and the dataset’s purpose. It may be a single identifier. It may be source plus identifier. It may be identifier plus effective date. Once that expectation is clear, a constraint becomes a useful guardrail rather than a way to force messy evidence into a convenient shape.
A durable data dictionary should record the identifier’s issuer, format, what it identifies, its expected uniqueness scope, and whether it can be reused or change meaning over time. A column named ID does not provide that information.
When a supposedly unique value appears twice, the best first response is not to make one row disappear. Stop long enough to learn what the identifier was actually promising.
Morrow Creek Data Services handles paid research, cleanup, verification, and structured delivery projects.
Start a data project