---
title: "Deduplication"
description: "Deduplication is the process of identifying and removing duplicate records from a dataset, keeping only one representative row per logical entity."
canonical: "https://motherduck.com/glossary/deduplication/"
related:
  - title: "DuckLake | MotherDuck Docs"
    url: "https://motherduck.com/docs/concepts/ducklake/"
  - title: "Data loading patterns | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-patterns/"
  - title: "DELETE | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/delete/"
---

# Deduplication

> Deduplication is the process of identifying and removing duplicate records from a dataset, keeping only one representative row per logical entity.

## Overview

Duplicate rows creep into datasets for many reasons: a source system emits the same event twice, an ingestion job is re-run and re-appends data, or two records legitimately represent the same customer with slightly different spellings. Deduplication is the step that resolves this down to one row per real-world entity, which matters because duplicates silently inflate counts, sums, and averages in any downstream aggregation.

There are two related but distinct cases: **exact duplicates**, where entire rows are identical, and **logical duplicates**, where rows share a key (like an order ID or email) but differ in other columns — often because you want to keep only the most recent version.

## Removing exact duplicates

For fully identical rows, `DISTINCT` is enough:

```sql
SELECT DISTINCT * FROM events;
```

## Removing duplicates by key

When duplicates share a key but you want to keep one row per key (e.g. the latest), DuckDB's `QUALIFY` clause combined with a window function is the cleanest approach:

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

`QUALIFY` filters on a window function result without needing a wrapping subquery, so you can compute `ROW_NUMBER()` per `order_id` and keep only the top-ranked row (here, the most recently updated one) in a single statement. The same pattern works for any "latest record wins" or "first record wins" scenario by adjusting the `ORDER BY`.

An alternative for the common "one row per key" case is DuckDB's `DISTINCT ON`, e.g. `SELECT DISTINCT ON (order_id) * FROM orders ORDER BY order_id, updated_at DESC`.

## Deduplicating during ingestion

Deduplication is often applied as a transformation step immediately after landing raw data, before it's used elsewhere — for example, as a dbt staging model that wraps the `QUALIFY` pattern above, so every downstream model can assume one row per key without re-deduplicating itself.

## Why it matters

Undetected duplicates are one of the most common causes of inflated metrics: doubled revenue, doubled user counts, or broken joins that fan out unexpectedly. Deduplication logic should be applied consistently and as early in the pipeline as possible, ideally paired with a downstream test (e.g. asserting a key column is unique) to catch regressions if a source starts emitting duplicates again.