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 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 feed, keeping the latest change per key is the same rule as above. See also 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.
Emolysis's 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 avoids ambiguous updates.