Deduplication strategies for records: exact rows, keep-latest by key and bounded windows

What it is

Three different problems hide under "duplicates". Exact duplicates are rows identical in every column, usually produced by a rerun that appended or a retry that succeeded twice. Versions are rows sharing a business key but differing in other columns, produced by change feeds or repeated extracts of a mutable table; only one of them is wanted, normally the latest. Redeliveries are the same message received more than once from an at-least-once transport. Each needs a different rule. The PostgreSQL documentation (cited) describes DISTINCT ON (expressions), which keeps only the first row of each set of rows where the expressions evaluate equal, and warns that "first" is unpredictable unless ORDER BY places the desired row first. Amazon SQS FIFO queues (cited) deduplicate on a MessageDeduplicationId so that within a 5-minute window only one instance of a message with that ID is delivered. PySpark's dropDuplicates (cited) drops duplicate rows in a batch, but on a streaming DataFrame keeps all data across triggers as state unless a watermark bounds how late a duplicate may arrive.

Why it matters

A dedup rule without an ordering silently picks a version at random and changes results between runs. A dedup window that is too short lets duplicates through; one that is unbounded grows state until the job fails. Deduplicating in the wrong place hides an upstream fault (a producer retrying without a key) that keeps costing elsewhere.

How to apply

• Exact duplicates: SELECT DISTINCT or a group by all columns; better, fix the append so that it becomes a partition replacement.
• Versions: DISTINCT ON (key) ... ORDER BY key, updated_at DESC, ingest_id DESC or ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) = 1, with a deterministic tie-breaker after the timestamp.
• Redeliveries: give every message a stable idempotency key at the producer, store it with a unique constraint at the consumer, and treat a conflict as "already processed".
• Streaming: deduplicate on the key within a watermark-bounded window and document the bound; duplicates older than the bound are handled by a periodic batch pass.
• Fuzzy matches (same customer, different spelling) are record linkage, not deduplication: normalise, match with explicit rules, and keep both originals with a link.

Pitfalls

Hashing the whole row as the key changes the hash whenever a column is added. Keep-latest by updated_at fails when clocks differ between sources; prefer a source sequence number. Deduplicating before a join hides which side fanned out.