A table is supposed to hold exactly one row per (customer, product) pair, but duplicates keep appearing. How would you decide whether duplicate elimination belongs in the schema, in the write path, or in every read - and what does each choice cost?

## The decision starts with semantics Ask what a second identical row *means*. If it means the same fact was recorded twice, the table's intended shape is a set and duplicates are corruption. If it means the same event happened twice, or that an at-least-once pipeline delivered a message twice, the table is legitimately a bag and duplicates must be tolerated somewhere downstream. These two answers lead to opposite designs, and getting them backwards is the real failure mode - teams put a dedup in the read path of a table that should have had a constraint, and put a constraint on an ingest table that then rejects perfectly normal retries. ## Schema enforcement A declared unique constraint on (customer_id, product_id) is the strongest option. It holds under concurrency because enforcement happens inside the engine's index maintenance, so two simultaneous inserts cannot both win. Creating it requires cleaning existing duplicates, which surfaces the size of the problem immediately. And it is the only option that pays a dividend to readers: because the planner may rely on enforced constraints, it can prove that joining on that pair cannot fan out and can eliminate duplicate-removal steps. Costs to weigh honestly: an extra index maintained on every insert, update and delete of the key columns, plus its storage; write amplification on hot tables; and on very high-throughput ingest, index contention. Writers also need a strategy for violations - most commonly an atomic upsert or merge, which is not merely convenient but the correct concurrent formulation, and which itself relies on the constraint. A trap worth naming: if a key column is nullable, uniqueness usually does not bite for rows where it is absent, because such rows are typically treated as distinct from one another. A unique constraint over a nullable column is a partial guarantee at best; make the columns NOT NULL or model the optional case explicitly. ## Write-path enforcement Deduplicating in application or pipeline code - look up, then insert if absent - fails under concurrency. Two workers can interleave between the check and the insert and both succeed. Serializable isolation or explicit locking closes the window, but you are then re-implementing what a unique index does natively, more slowly and with more code. Write-path logic is still useful as a first line of defence, to give friendly errors and avoid pointless constraint violations, but it should sit on top of a constraint, never instead of one. There is one legitimate write-path-only variant: an idempotency or dedup key attached to incoming messages, checked against a store of recently-seen keys. That is still a uniqueness constraint - it has simply moved to a different table. ## Read-time deduplication For an append-only landing table fed by at-least-once delivery, duplicates are expected and the raw table should stay a bag - it is the audit record of what arrived. The set is produced when building the curated table: group by the business key and select a deterministic winner (latest event timestamp, then highest sequence number as a tiebreak). Two properties matter: it must be deterministic so reruns give identical results, and the business key must be right, because deduping on the whole row only catches byte-identical repeats and misses copies that differ in an ingest timestamp. The cost of read-time dedup is that every consumer pays a sort or hash forever, and consumers who forget it get wrong numbers. That is why the deduplicated result should be materialized once, not left as a convention each analyst is expected to remember. ## How I usually land it Declare the constraint wherever the pair is an identity, make the key columns NOT NULL, use an atomic upsert in writers, and reserve read-time deduplication for the ingest boundary where duplicates are part of the delivery contract. Then treat any duplicate-elimination step appearing in a report over a constrained table as a bug report about a join, not as normal practice.