Deduplication (Deduping)

Concept

Deduplication is reducing multiple rows that describe the same business reality — retried webhooks, double-fired pixels, overlapping backfills — to exactly one canonical row per business key, typically via ROW_NUMBER() partitioned by order_id or event_id.

Layman Explanation & Analogy

The same parcel scanned twice at the depot is still one parcel. Deduping is the scan that notices: one row per order_id survives, the photocopies are quarantined, and every revenue report reads the deduped view instead of the raw table.

Worked real-world example

Joining 210 real orders to 10 same-day campaigns on date yields 2,100 rows and 10x revenue — the fan-out bug. Aggregating each side to one row per day before joining reports the true ₹4.2L instead of ₹42L.

What people get wrong & common traps

Deduplicate on business identity (order_id, event_id), never on full-row equality — re-ingested rows carry new timestamps and are never byte-identical, so SELECT DISTINCT silently fails. And never join tables at different grains then aggregate: aggregate first, join second, no exceptions.

Related terms