Deduplication

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:

Copy code
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:

Copy code
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.