SQL, data modeling, data quality, EDA, visualization · insight-1061
Deduplicate the incoming batch before an upsert, because one statement cannot update a row twice
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A load runs INSERT ... ON CONFLICT DO UPDATE from a staging table that contains two versions of the same booking, and the job fails or, in another engine, keeps an arbitrary version.
Action
Reduce the staging data to one row per key first (for example ROW_NUMBER over the key ordered by the update timestamp and a tie-breaker), then upsert, and assert key uniqueness on the staged set. Count rows dropped in this step as a data quality metric.
Reason
An upsert is atomic per target row and deterministic only when each target row is touched once per command.
Boundary
Incremental loads, change data capture merges and dimension updates.
Symptom or risk
The conflict clause expects at most one proposed row per target key; a duplicate in the batch raises an error or lets order decide which value wins, so results change between runs.
Exceptions
Pure insert-only logs, where duplicates are handled by a downstream key.
Trade-offs
An extra pass over the batch, small compared with debugging a flaky merge.
Pitfalls
Deduplicating by DISTINCT on all columns, which keeps both versions when any value differs.
Lessons and concepts
Claims and their sources
In PostgreSQL ON CONFLICT DO UPDATE guarantees an atomic insert-or-update outcome and may not affect the same existing row more than once in one command, raising a cardinality violation otherwise.