Deduplicate records without rewriting the table

At a glance

Learning objectives

• Count duplicates per key with one GROUP BY query
• Correlate the first duplicate date with a deploy
• Rewrite affected partitions instead of the whole table

What you need

• The inflated table and its business (grain) key
• Warehouse SQL access to run the grouping queries
• [object Object]

Steps

1. Identify the duplicate key and count duplicates per key. (from runbook-duplicate-records )
2. Run: `SELECT k, count(*) c FROM t GROUP BY k HAVING count(*) > 1 ORDER BY c DESC LIMIT 20;` (from runbook-duplicate-records )
3. Determine the earliest date duplicates appeared → correlate with a deploy. (from runbook-duplicate-records )
4. Check whether the write is append vs merge/overwrite. (from runbook-duplicate-records )
5. Deduplicate on the business key, keeping the latest by event time. (from runbook-duplicate-records )
6. Rewrite the affected partitions (overwrite), not the whole table. (from runbook-duplicate-records )

Check yourself

• After the fix, the duplicates query returns zero rows for the rewritten partitions: `SELECT k, count(*) c FROM t GROUP BY k HAVING count(*) > 1` is empty.
• Duplicate-rate per key is back to 0 and row count vs source count reconciles.

Worked example

SELECT k, count(*) c FROM t GROUP BY k HAVING count(*) > 1 ORDER BY c DESC LIMIT 20;

SELECT min(dt), max(dt) FROM (SELECT dt, k, count(*) FROM t GROUP BY dt, k HAVING count(*)>1);

Deduplicate on the business key, keeping the latest by event time.
Rewrite the affected partitions (overwrite), not the whole table.

Prevention: idempotent writes (partition overwrite or MERGE on the key);
a `unique` test on the grain key in CI and in production.

Sourced from

• Duplicate records

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Common gotchas

• Fix the destination partitions, not the whole table — a full rewrite is both slow and a new risk.
• An append instead of overwrite on retry is the classic maker of duplicates.
• A non-unique 'one' side in a join fans out silently; test the grain, not the totals.

Production considerations

• Idempotent writes: partition overwrite or MERGE on the business key.
• Run a unique test on the grain key in CI and in production.

Diagram

duplicates
   |
   |- count per key --> top duplicated keys
   |
   |- min duplicate date --> correlate with deploy --> fix write mode
   |
   |- dedupe by business key --> keep latest by event time --> overwrite affected partitions

Registry stats