# Deduplication

> Deduplication removes repeated records so each entity or event appears once, using a deterministic rule to choose which version to keep.

Deduplication removes repeated records so each entity or event appears once. Exact duplicates are easy; the hard part is choosing which of several versions of a record to keep. A sound rule is deterministic: for example, the latest by update time, with a fixed tie-breaker, so the same input always gives the same output.

For example, a source sends customer records twice a day, and a customer who changes their address in the morning appears in both files. The silver table should hold one row for that customer. A rule such as "keep the row with the latest `updated_at`; if two rows share it, keep the one from the later file" picks the same row every time. In SQL this is usually written with `ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC, file_name DESC)` and a filter that keeps row number one.

Without the tie-breaker, the result can change from run to run, because databases do not promise an order for equal values. That kind of bug is hard to find: totals move slightly, tests pass on most days, and nobody can reproduce the failure. Exact duplicates, where every column matches, are often the sign of a file delivered twice and can be removed by comparing a hash of the whole row.

Deduplication needs a key: the [natural key](https://emolysis.com/glossary/natural-key) for entities such as customers, or an event id for events. Events without a reliable id are harder, because two identical clicks a second apart may both be real. Where duplicates come from a [change data capture](https://emolysis.com/glossary/change-data-capture) feed, keeping the latest change per key is the same rule as above. See also [silver layer](https://emolysis.com/glossary/silver-layer).

On Databricks and Snowflake, the `QUALIFY` clause can filter on the row number directly, which keeps the rule in one short statement. See also [merge](https://emolysis.com/glossary/merge-upsert).

Emolysis's [Dedupe / conform](https://emolysis.com/shapes/dedupe-conform) shape applies such a rule, and its golden test feeds duplicates to prove the result does not change between runs. Deduplicating before a [merge](https://emolysis.com/glossary/merge-upsert) avoids ambiguous updates.

---

Page: https://emolysis.com/glossary/deduplication. Emolysis is a product of Bandpey GmbH, Munich, Germany. It is in private development, and nothing is released yet.