Find duplicate business keys without deleting evidence

A repeated business key is a signal to inspect, not an automatic instruction to delete rows. The records may be identical replays, authorized revisions, separate events sharing an inadequate key, or conflicting data that needs investigation.

A reliable cleaning process preserves the raw deliveries, reports the repeated keys and creates a documented analytical view. That lets another person reconstruct what was received and why a particular representation was used.

The synthetic commerce dataset contains nine payment-delivery rows but only eight unique event IDs. P02 was delivered twice with the same order and amount at different ingestion times.

Measure the repetition before resolving it

SELECT
    COUNT(*) AS raw_rows,
    COUNT(DISTINCT event_id) AS distinct_event_ids
FROM payment_events;

Expected: nine and eight. The difference is one extra delivery, but it does not identify which key repeated or whether its payload changed.

SELECT event_id,
       COUNT(*) AS deliveries,
       MIN(ingested_at) AS first_seen,
       MAX(ingested_at) AS last_seen
FROM payment_events
GROUP BY event_id
HAVING COUNT(*) > 1
ORDER BY event_id;

P02 appears twice, one minute apart. This establishes replay timing in the fixture. It does not establish that “latest wins” is the correct policy for every source.

Compare business payloads separately from delivery metadata

WITH payloads AS (
    SELECT DISTINCT event_id, order_id, amount_paise
    FROM payment_events
)
SELECT event_id, COUNT(*) AS distinct_payloads
FROM payloads
GROUP BY event_id
HAVING COUNT(*) > 1;

The result is empty: no event ID has conflicting selected business payloads in the unchanged fixture. This comparison deliberately excludes ingestion time because a replay can arrive later without changing the business event.

In a real table, include all fields that define the event's meaning. A comparison of amount alone could miss a changed currency, customer or status. NULL handling and normalization rules also matter when comparing payloads.

The DISTINCT lesson explains why selecting delivery metadata changes which rows are considered duplicates.

Create a reproducible view, not an unexplained deletion

For this fixture, the source contract can be stated as: event IDs identify immutable payment events; repeated IDs with equal business payload are delivery replays; conflicting payloads are exceptions.

Under that contract, the analytical amount is:

WITH unique_events AS (
    SELECT DISTINCT event_id, order_id, amount_paise
    FROM payment_events
)
SELECT COUNT(*) AS events, SUM(amount_paise) AS value_paise
FROM unique_events;

Expected: eight events and 104,000 paise. The raw sum is 122,000, so the replay correction removes 18,000 paise from the analytical total while leaving the source evidence intact.

Do not use this view if conflicting payloads exist without first resolving their policy. The query retains different payloads for the same key and could still overcount them.

If versions are allowed, define which version wins

Some systems send updates under the same entity ID. In that case, a version number or authoritative update timestamp may define ordering. An ingestion timestamp only tells you when your pipeline saw a record; a late delivery can contain an older business version.

A window function such as ROW_NUMBER can select one record per key, but its ORDER BY must encode the source's authoritative rule. If ties remain, use a valid stable tie-breaker or flag the ambiguity. An arbitrary selection is not made correct by being deterministic.

SQLite supports window-function ranking, but the correct partition and ordering still come from the data contract. SQLite window functions.

Record the effect of the resolution

A useful deduplication manifest contains raw row count, distinct keys, replay keys, conflict keys, retained row count, amount before and after resolution, rule version and source snapshot. Keep exception rows available for review.

This matters when the cleaned table feeds several downstream reports. Without a recorded rule, one team may count deliveries, another count unique events and a third select a latest snapshot. Their totals differ for a reason that is invisible in the dashboard.

Also check whether the business key is scoped. An event ID may be unique only within a source system or tenant. Combining two sources can create apparent duplicates that are actually different events. A composite key may be necessary.

Test the unhappy path

In a copy of the fixture, change the second P02 amount. The conflict query should now return P02 with two payloads. The correct next step under our immutable-event contract is to flag the conflict, not to silently choose the later delivery.

Then add an identical replay of another event. The clean total should remain unchanged while the replay report grows. These two mutations distinguish conflict detection from ordinary idempotent replay handling.

The Data Analytics with Generative AI programme covers wrangling, SQL and automated reporting. Preserving evidence while resolving duplicates is a useful practice across all three: it makes a cleaned dataset explainable instead of merely smaller.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in SQL percentage change when the previous value is zero.
• Continue with SQL text cleaning: normalize categories without merging different people.