Why Your Data Doubles: 5 Causes & Proven Deduplication Strategies

1. Why Data Duplicates: Five Common Sources

Before applying deduplication techniques, you must identify where duplicates originate. The article identifies five typical causes:

Source-system duplicate writes : Users double-click submit buttons; without request deduplication, the business system creates two identical orders.

Repeated extraction during sync : Incremental sync jobs that re-run after failure or use overlapping time windows re-insert already-processed rows, especially when using append-only writes without unique-key checks.

Multi-table join explosion : A single order joined to a product table that contains two rows for the same product ID yields two result rows, doubling aggregated amounts.

Duplicate message delivery : Network timeouts, retries, or consumer failures cause the same business event to be processed multiple times; without idempotent consumers keyed by message ID or business key, downstream records are updated repeatedly.

Overlap between historical backfill and incremental sync : When a daily backfill job covers the same date range as the regular incremental job, the same records are written twice.

Pinpointing the exact stage — source, sync, join, messaging, or scheduling — is essential before choosing a fix.

2. Defining What Counts as a Duplicate

Not all duplicates are identical rows. The article distinguishes three categories:

Exact duplicates : Every column matches; detectable via field-by-field comparison or row hashes.

Business duplicates : Rows differ in some columns but represent the same business entity (e.g., same order_id with different statuses). Retention rules (latest, earliest, source priority) must be defined.

Fuzzy duplicates : Records refer to the same real-world entity but differ in representation (e.g., "Shanghai" vs "Shanghai City"). Requires matching rules on phone, name, company, etc., and often similarity algorithms.

Before writing any deduplication logic, answer three questions:

What is the data grain (one row per order, per line item, per payment)?

What is the unique key (single column or composite)?

Which version do we keep (latest, earliest, source priority)?

3. SQL Deduplication: Three Core Techniques

DISTINCT — Remove Fully Identical Result Rows

SELECT DISTINCT order_id, product_id, amount FROM orders;

Deduplicates across all selected columns. Suitable for simple queries and dimension distinct lists. Limitation : Does not respect business keys; two rows with same order_id but different statuses are both kept.

GROUP BY — Aggregate by Business Key

SELECT order_id, SUM(amount) AS total_amount FROM orders GROUP BY order_id;

Groups by the business key (order_id) and aggregates measures. Caution : If multiple legitimate line items exist for one order, summing is correct business logic — do not treat them as duplicates to delete.

ROW_NUMBER() — Keep One Row per Key by Rule

WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_time DESC, ingest_time DESC) AS rn FROM orders) SELECT * FROM ranked WHERE rn = 1;

Partitions by business key, orders by update_time (and tie-breaker ingest_time), and keeps the first row. Allows precise retention policies (latest, earliest, source priority). Add stable tie-breakers to avoid non-deterministic results.

4. Preventing Duplicates at the Source

Unique Constraints — Let the Database Reject Duplicates

CREATE UNIQUE INDEX uk_order_id ON orders(order_id);

Composite keys are also supported. Requirement : The constraint must match the true business key; verify NULL handling per database.

Upsert — Insert or Update on Conflict

Combines INSERT and UPDATE. Syntax varies: MySQL ON DUPLICATE KEY UPDATE, PostgreSQL ON CONFLICT. Ideal for continuous sync of latest business state.

Idempotency — Ensure Re-execution Is Safe

Key practices:

Identify records by business key or message ID.

Use upsert instead of blind append.

Track processed batch watermarks.

Combine transactions with unique constraints.

Distinguish task retry (re-run after failure) from data deduplication (prevent wrong results on re-run); both are needed.

5. Real-Time Deduplication: CDC & Streaming

In streaming, the same primary key appears many times as legitimate state changes (create → pay → ship → receive). Must separate duplicate events from valid updates .

By Event ID

If each event carries a unique event_id, ignore repeats. Handles duplicate delivery and double API calls.

By Business Key + Version/Timestamp

For evolving entities, compare version or update_time; discard older versions arriving after newer ones.

Time-Window Deduplication

In stream processors, define a window (e.g., 10 seconds) and deduplicate by event ID within it. Limitation : Late duplicates outside the window slip through; combine with persisted state, late-data handling, and downstream idempotency.

6. Fuzzy Deduplication & Data Quality Governance

For master data (customers, suppliers, products) where exact keys don't match:

Field standardization : Normalize case, spaces, punctuation, date/address formats.

Composite-field matching : Combine name, phone, ID, company; choose fields per entity type.

Similarity scoring : Edit distance, Jaccard index on text fields; flag pairs above threshold for human review.

Merge rules : Define survivor record, retained attributes, and history preservation.

Shift from one-off cleanup to continuous governance: automated quality rules (uniqueness, completeness, consistency, anomaly detection) + data lineage for root-cause tracing + issue workflow for assignment and verification.

7. Five-Step Implementation Roadmap

Confirm grain : Define what a row represents; establish unique/composite keys.

Locate source : Trace duplicates to source system, sync job, join, messaging, or scheduling overlap.

Pick strategy : DISTINCT for exact dupes; ROW_NUMBER for latest-version retention; upsert for continuous sync; event-id + idempotency for streaming.

Enforce prevention : Add unique constraints, design idempotent APIs/consumers, embed checks in ETL pipelines.

Monitor continuously : Regular uniqueness, completeness, consistency, and growth checks; close the loop via lineage and ownership.

Remember: The goal is not fewer rows, but rows that faithfully represent the correct business grain and rules. Some duplicates are valid state changes; some are legitimate detail lines. Understand why duplication occurs before choosing the technical fix.