# Data Deduplication

> It takes out the extra copies of a row.

**Field:** Data Engineering  
**Category:** Processing  
**Also known as:** Deduplication, Dedup, Dedupe

## Think of it like

It is like a class list where one child got written down twice. You cross out the extra name.

## Definition

Data deduplication removes rows that stand for the same thing more than once. Copies come from retries, double loads or the same customer typed twice. Exact copies are easy to drop. Near copies need a rule, such as the same email, to say they match. A common way keeps the newest row for each key and drops the rest.

## Example

A retry loaded the same 400 orders twice. Deduplication on the order ID brings sales back to the true number.

## Example code

```sql
SELECT *
FROM orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY order_id ORDER BY loaded_at DESC
) = 1;
```

Keep only the newest row for each order. QUALIFY works in Snowflake, BigQuery and DuckDB.

## Why it matters

Copies make counts and totals too high. With at-least-once delivery, they are sure to happen.

## Common mistake

Using SELECT DISTINCT on rows that differ in one column, such as load time. The copies stay.

## Three principles

- **Pick a key.** What makes two rows the same.
- **Keep one.** Often the newest row.
- **Expect copies.** Retries always make some.

## Related terms

- [Data Cleaning](https://learn.benibix.com/term/data/data-cleaning)
- [At-Least-Once Delivery](https://learn.benibix.com/term/data/at-least-once-delivery)
- [Data Transformation](https://learn.benibix.com/term/data/data-transformation)
- [Idempotency](https://learn.benibix.com/term/development/idempotency)

---

Source: https://learn.benibix.com/term/data/data-deduplication
Author: Behnia Zarei (a developer in Vienna, Austria, who builds and teaches data, AI and business intelligence at BENIBIX)
Publisher: BENIBIX Learn (https://learn.benibix.com/)
Last updated: 2026-11-19
Licence: CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Quote freely with attribution.
