Skip to content
BENIBIX Learn

Data Deduplication

Data⚙️ ProcessingData Transformation

Also known as Deduplication, Dedup, Dedupe

It takes out the extra copies of a row.

Think of it like

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

Example

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

Why it matters

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

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

Common mistake

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

Key takeaways

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