Deduplication

Concepts covered: Window Deduplication

Production tables collect duplicates from the machinery that fills them: a pipeline run retried after a timeout writes its batch twice, a message queue delivers a payment event at least once rather than exactly once, a backfill overlaps the day the daily load already wrote. The rows are wrong in a way no single row reveals, so dedup is a question about groups of rows that share a key, which is a window's partition.

Exact duplicates and key duplicates

The table received 6 rows for 3 payments. The last 2 rows copy the p2 and p3 rows column for column: exact duplicates, the mark of a retried batch. The 2 p1 rows share a key and differ in status and ingested_at: key duplicates, 2 versions of one fact. DISTINCT removes the first kind and leaves the second untouched, because to DISTINCT the 2 p1 rows are different rows.

SELECT
  COUNT(*) AS raw_rows,
  COUNT(DISTINCT payment_id) AS distinct_keys,
  COUNT(*) - COUNT(DISTINCT payment_id) AS extra_rows
FROM payments_raw

The gap between COUNT(*) and COUNT(DISTINCT key) is the number of rows dedup should remove, and it is the first number to compute, before and after, because a dedup that removes a different count has either missed copies or deleted real rows.

ROW_NUMBER picks one row per key

Partition by the business key, order so the row you want to keep comes first, number the rows, keep number 1. Ordering by ingested_at DESC keeps the newest version, which turns p1 from pending into settled; for p2 and p3 the 2 copies tie and either one is the right answer, since they are identical.

WITH numbered AS (
  SELECT
    payment_id,
    status,
    amount,
    ingested_at,
    ROW_NUMBER() OVER (PARTITION BY payment_id ORDER BY ingested_at DESC) AS rn
  FROM payments_raw
)
SELECT
  payment_id,
  status,
  amount,
  ingested_at
FROM numbered
WHERE rn = 1
ORDER BY payment_id

The query returns 3 rows, matching the distinct key count, and p1 carries its settled status. The rn = 1 filter sits in the outer query for the reason the previous section gave: inside the CTE the numbering has not happened yet when its own WHERE would run.

Ties decide whether the result is stable

When 2 copies of a key share the ordering value and differ elsewhere, ROW_NUMBER still hands out 1 and 2, in an order the engine does not promise to repeat. The same query can keep the pending row today and the settled row tomorrow, and a dashboard built on it moves without any data changing. The fix is an ORDER BY that cannot tie: add the source file name, a load sequence number, or a column that prefers the more complete row, such as status = 'settled' DESC. RANK is the wrong tool here because tied rows both get rank 1 and both survive the filter.

Do

• Partition by the business key, never by the surrogate id the loader generated
• Order by a column that cannot tie, with a second key as the tie-breaker
• Compare COUNT(*) minus COUNT(DISTINCT key) before and after the dedup
• Dedup at the table's grain: one row per payment, or per payment per status change

Avoid

• Rely on DISTINCT when any column can differ between copies
• Use RANK or DENSE_RANK to pick one row, since ties keep more than one
• Leave the tie-break to the engine and expect the same row back tomorrow
• Dedup downstream in every consumer instead of once in the curated table

Payment p9 arrived twice with the same ingested_at, once with status pending and once with status settled. What does this query return for p9?

WITH numbered AS (
  SELECT
    payment_id,
    status,
    ROW_NUMBER() OVER (PARTITION BY payment_id ORDER BY ingested_at DESC) AS rn
  FROM payments_raw
)
SELECT
  payment_id,
  status
FROM numbered
WHERE rn = 1

• Two rows, because tied rows share the top position and both get rn = 1.
• One row, always the settled copy, because settled sorts after pending.
• One row, and which status it carries can change from one run to the next.
• Zero rows, because the tie makes rn NULL for both copies.

Dedup once, in the curated table, and let every consumer read clean rows. The alternative is 5 dashboards each carrying their own ROW_NUMBER with slightly different tie-breaks and slightly different totals, and a week of meetings to find out why.

About this lesson

This section is part of the Window Functions: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.