Deduplicate change events using a deterministic tie-breaker

Deduplicating change events involves two different decisions: identifying repeated deliveries of the same event and choosing the authoritative version of a business record. A deterministic sort makes an answer repeatable, but repeatability alone does not make that answer correct.

Use a source-defined revision or sequence when it represents business ordering. Use ingestion time to describe delivery order. When two records claim the same authoritative revision but disagree on the payload, expose the conflict rather than silently choosing whichever arrived last.

Define the keys before using ROW_NUMBER

Consider updates to an order. The business key is order_id. The source event key is event_id. Revision increases when the source changes the order. Delivery_id identifies each arrival at the warehouse, including replays.

In the following self-contained SQLite example, event E2 is delivered twice with an identical payload. Revision 2 is authoritative even though revision 1 arrives later through a delayed delivery.

WITH deliveries(delivery_id, event_id, order_id, revision, status, ingested_at) AS (
    VALUES ('D1','E2','O1',2,'paid','2026-01-01T10:01:00'),
           ('D2','E2','O1',2,'paid','2026-01-01T10:02:00'),
           ('D3','E1','O1',1,'pending','2026-01-01T10:03:00')
), replay_rank AS (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY event_id ORDER BY ingested_at, delivery_id
    ) AS delivery_rank
    FROM deliveries
), version_rank AS (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY order_id ORDER BY revision DESC, event_id
    ) AS version_rank
    FROM replay_rank WHERE delivery_rank = 1
)
SELECT order_id, revision, status, event_id
FROM version_rank WHERE version_rank = 1;

The result is O1, revision 2, paid, E2. Sorting by latest ingestion alone would incorrectly restore pending status. The example assumes event IDs are unique within this source and every repeated event has identical business content.

With multiple sources, qualify keys with the source identifier. E2 from one system may have no relationship to E2 from another. Likewise, source revision numbers are not necessarily comparable across independent systems.

Detect contradictions before selecting a winner

Now suppose two distinct source events claim revision 2 but disagree on status:

WITH changes(event_id, order_id, revision, status) AS (
    VALUES ('E1','O1',1,'pending'),
           ('E2','O1',2,'paid'),
           ('E3','O1',2,'cancelled')
)
SELECT order_id, revision, COUNT(*) AS events_at_revision,
       COUNT(DISTINCT status) AS distinct_statuses
FROM changes
GROUP BY order_id, revision
HAVING COUNT(DISTINCT status) > 1;

The check returns O1/revision 2 with two events and two statuses. This population must fail the authoritative-version contract or enter a defined exception process. Alphabetical event order would consistently pick a row, but there is no evidence that this row is the correct business state.

This simplified conflict check compares status only and assumes it is non-NULL. A production check must compare all material payload fields, handle NULL explicitly, and detect contradictions within a repeated event_id as well. A canonical payload hash can support comparison, provided its serialization and included fields are controlled.

Make tie-breakers explainable

For identical replays, earliest ingestion followed by a unique delivery identifier is a reasonable way to retain one representative delivery. It does not discard the raw evidence: the curated table references the retained delivery while raw arrivals remain available for investigation.

For different business revisions, descending authoritative revision determines the winner. A stable identifier provides complete ordering only after the data contract establishes that remaining ties are equivalent. The SQLite window-function documentation explains how ROW_NUMBER applies that ordering within a partition.

Do not use SELECT DISTINCT over a partial column list to resolve conflicts. It may collapse records that differ in an omitted field, concealing a source-quality problem.

Test replays, lateness and ambiguity separately

Use three fixtures. An identical replay should leave the curated business state unchanged. An older revision arriving late should not replace a newer revision. A contradictory payload at the same revision should produce a visible exception rather than an ordinary winner.

Also test deletion events, if the source supports them. A latest-version query that ignores tombstones can resurrect deleted records. Define whether the curated output removes such entities or retains their deleted status for downstream interpretation.

Exercise: add revision 3 with status refunded, then replay revision 2 after it. The current state must remain revision 3. Add a second revision-3 event with a different status and ensure your conflict check prevents silent acceptance.

The examples run with the advanced SQL lab's SQLite environment. NeuraPath's Data Analytics with Generative AI course supports this progression from window-function syntax to reliable reporting tables. The useful portfolio evidence is the policy and failing fixtures, as well as the final query.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in SQL funnel conversion when events arrive out of order.
• Continue with SQL percentiles for skewed delivery times.