Idempotent Event Deduplication with Window Functions

What you will learn

• Partition incoming event streams by business reference keys using ROW_NUMBER()
• Implement multi-column deterministic tie-breaking logic incorporating business status priorities
• Filter duplicate records within Common Table Expressions using row number qualification
• Guarantee idempotent settlement processing across distributed, at-least-once message pipelines

What you need first

SELECT payment_id, transaction_ref, ROW_NUMBER() OVER (PARTITION BY transaction_ref ORDER BY received_at DESC) AS rn FROM raw_payments LIMIT 3;

payment_id | transaction_ref | rn
-----------+-----------------+---
2          | TXN_9001        | 1
1          | TXN_9001        | 2
3          | TXN_9002        | 1

SELECT 'SUCCESS' AS status, CASE WHEN 'SUCCESS' = 'SUCCESS' THEN 1 ELSE 2 END AS priority;

status  | priority
--------+---------
SUCCESS | 1

Architecture and data flow

The data

Raw payment event logs containing retry duplicates and variable completion statuses.

CREATE OR REPLACE TABLE raw_payments (
  payment_id INT,
  transaction_ref VARCHAR,
  merchant_id INT,
  amount DOUBLE,
  status VARCHAR,
  received_at TIMESTAMP
);
INSERT INTO raw_payments VALUES
  (1, 'TXN_9001', 501, 1200.0, 'PENDING', TIMESTAMP '2024-03-01 10:00:00'),
  (2, 'TXN_9001', 501, 1200.0, 'SUCCESS', TIMESTAMP '2024-03-01 10:00:05'),
  (3, 'TXN_9002', 502, 3400.0, 'SUCCESS', TIMESTAMP '2024-03-01 10:05:00'),
  (4, 'TXN_9003', 503, 850.0, 'PENDING', TIMESTAMP '2024-03-01 10:10:00'),
  (5, 'TXN_9003', 503, 850.0, 'FAILED', TIMESTAMP '2024-03-01 10:10:02'),
  (6, 'TXN_9003', 503, 850.0, 'SUCCESS', TIMESTAMP '2024-03-01 10:10:10');

Step by step

Read each step, run its code, then check your output against the one shown.

1. 01

   Query raw webhook payments ledger

   We start by querying the raw_payments table ordered by transaction_ref and received_at. Inspecting the raw data reveals duplicate records: TXN_9001 appears twice (PENDING, SUCCESS), while TXN_9003 appears three times across PENDING, FAILED, and SUCCESS. In payment engineering, verifying raw payload characteristics ensures our deduplication logic handles variable status transitions and timestamp intervals without dropping legitimate distinct transactions. We inspect the raw ingestion stream to confirm that duplicate records share consistent business reference keys before applying deterministic ranking rules. In high-throughput payment architectures, understanding the arrival order and state transitions of webhook retries prevents faulty deduplication logic from dropping valid subsequent updates.

   SQLStep 1Copy

   SELECT payment_id, transaction_ref, merchant_id, amount, status, received_at FROM raw_payments ORDER BY transaction_ref, received_at;

   Show outputGuess what it prints first

   payment_id | transaction_ref | merchant_id | amount | status  | received_at
   -----------+-----------------+-------------+--------+---------+--------------------
   1          | TXN_9001        | 501         | 1200   | PENDING | 2024-03-01 10:00:00
   2          | TXN_9001        | 501         | 1200   | SUCCESS | 2024-03-01 10:00:05
   3          | TXN_9002        | 502         | 3400   | SUCCESS | 2024-03-01 10:05:00
   4          | TXN_9003        | 503         | 850    | PENDING | 2024-03-01 10:10:00
   5          | TXN_9003        | 503         | 850    | FAILED  | 2024-03-01 10:10:02
   6          | TXN_9003        | 503         | 850    | SUCCESS | 2024-03-01 10:10:10

   Common mistake

   Assuming webhook duplicates have identical payloads and attempting to deduplicate using SELECT DISTINCT across all columns.

   How you will know

   You will see 6 raw payment records with multiple entries for TXN_9001 and TXN_9003.

2. 02

   Attempt naive DISTINCT on business keys

   Wrong turn first: this step shows the mistake before the fix

   Here is the naive approach: writing SELECT DISTINCT transaction_ref, merchant_id, amount from raw_payments. While this collapses the rows into 3 distinct transaction references, it strips out the status and received_at columns entirely. If the application needs to know whether the payment succeeded or when it finalized, this query fails to deliver the necessary operational state. Distinct filtering collapses rows indiscriminately, stripping out crucial metadata needed for financial auditing and merchant reporting.

   SQLStep 2Copy

   SELECT DISTINCT transaction_ref, merchant_id, amount FROM raw_payments ORDER BY transaction_ref;

   Show outputGuess what it prints first

   transaction_ref | merchant_id | amount
   ----------------+-------------+-------
   TXN_9001        | 501         | 1200
   TXN_9002        | 502         | 3400
   TXN_9003        | 503         | 850

   Common mistake

   Using DISTINCT on a subset of columns, which discards critical transactional metadata like status and settlement timestamps.

   How you will know

   Notice that while transaction references are deduplicated, status and received_at attributes are completely lost.

3. 03

   Assign row numbers with deterministic tie-breaking

   To preserve full row metadata while resolving duplicates, we apply ROW_NUMBER() partitioned by transaction_ref. We construct a multi-column ORDER BY clause: first prioritizing confirmed SUCCESS status using a CASE statement (status = 'SUCCESS' gets priority 1, others get 2), and second sorting by received_at DESC to pick the latest timestamp in case of ties. This guarantees row number 1 always identifies the authoritative transaction record. Combining business status prioritization with reverse chronological ordering ensures that finalized successful payments take precedence over transient pending retries, even when network delays cause webhooks to arrive out of order.

   SQLStep 3Copy

   SELECT
     payment_id,
     transaction_ref,
     merchant_id,
     amount,
     status,
     received_at,
     ROW_NUMBER() OVER (
       PARTITION BY transaction_ref
       ORDER BY
         CASE WHEN status = 'SUCCESS' THEN 1 ELSE 2 END,
         received_at DESC
     ) AS row_num
   FROM raw_payments
   ORDER BY transaction_ref, row_num;

   Show outputGuess what it prints first

   payment_id | transaction_ref | merchant_id | amount | status  | received_at         | row_num
   -----------+-----------------+-------------+--------+---------+---------------------+--------
   2          | TXN_9001        | 501         | 1200   | SUCCESS | 2024-03-01 10:00:05 | 1
   1          | TXN_9001        | 501         | 1200   | PENDING | 2024-03-01 10:00:00 | 2
   3          | TXN_9002        | 502         | 3400   | SUCCESS | 2024-03-01 10:05:00 | 1
   6          | TXN_9003        | 503         | 850    | SUCCESS | 2024-03-01 10:10:10 | 1
   5          | TXN_9003        | 503         | 850    | FAILED  | 2024-03-01 10:10:02 | 2
   4          | TXN_9003        | 503         | 850    | PENDING | 2024-03-01 10:10:00 | 3

   Common mistake

   Sorting only by received_at, which fails if an upstream service delivers an out-of-order FAILED webhook after a successful authorization.

   How you will know

   Every payment row receives a row_num, with rank 1 assigned to the successful event for each transaction reference.

4. 04

   Compare timestamp sorting versus status prioritization

   To understand why status prioritization is essential, we run a query comparing rank derived purely from received_at against rank derived with status weighting. If a gateway retried a payment and recorded a trailing timeout error after the banking debit succeeded, timestamp-only sorting would select the error row. Our deterministic rule ensures that settled financial transactions are never overwritten by late-arriving transient network error logs. Explicitly weighting business status above timestamp ordering prevents edge-case errors where delayed error notifications arrive after successful payment confirmations.

   SQLStep 4Copy

   SELECT
     payment_id,
     transaction_ref,
     status,
     received_at,
     ROW_NUMBER() OVER (PARTITION BY transaction_ref ORDER BY received_at DESC) AS time_only_rank,
     ROW_NUMBER() OVER (
       PARTITION BY transaction_ref
       ORDER BY CASE WHEN status = 'SUCCESS' THEN 1 ELSE 2 END, received_at DESC
     ) AS status_priority_rank
   FROM raw_payments
   ORDER BY transaction_ref, status_priority_rank;

   Show outputGuess what it prints first

   payment_id | transaction_ref | status  | received_at         | time_only_rank | status_priority_rank
   -----------+-----------------+---------+---------------------+----------------+---------------------
   2          | TXN_9001        | SUCCESS | 2024-03-01 10:00:05 | 1              | 1
   1          | TXN_9001        | PENDING | 2024-03-01 10:00:00 | 2              | 2
   3          | TXN_9002        | SUCCESS | 2024-03-01 10:05:00 | 1              | 1
   6          | TXN_9003        | SUCCESS | 2024-03-01 10:10:10 | 1              | 1
   5          | TXN_9003        | FAILED  | 2024-03-01 10:10:02 | 2              | 2
   4          | TXN_9003        | PENDING | 2024-03-01 10:10:00 | 3              | 3

   Common mistake

   Relying on database insertion order or primary key order rather than explicit business priority rules.

   How you will know

   The output compares time_only_rank against status_priority_rank across all 6 payment records.

5. 05

   Filter authoritative deduplicated settlement records

   In the final query, we encapsulate the ROW_NUMBER calculation within a Common Table Expression named deduped and filter WHERE row_num = 1. This outputs the definitive settlement dataset containing exactly one row per transaction reference. All three transactions (TXN_9001, TXN_9002, TXN_9003) resolve to status SUCCESS with accurate merchant IDs and transaction amounts. Filtering the ranked CTE on row number 1 delivers exactly-once payout semantics, protecting settlement pipelines from double-disbursements. This deterministic filtering pattern scales effortlessly across millions of financial transactions while maintaining strict idempotency across distributed message queues.

   SQLStep 5Copy

   WITH deduped AS (
     SELECT
       payment_id,
       transaction_ref,
       merchant_id,
       amount,
       status,
       received_at,
       ROW_NUMBER() OVER (
         PARTITION BY transaction_ref
         ORDER BY
           CASE WHEN status = 'SUCCESS' THEN 1 ELSE 2 END,
           received_at DESC
       ) AS row_num
     FROM raw_payments
   )
   SELECT
     transaction_ref,
     merchant_id,
     amount,
     status,
     received_at
   FROM deduped
   WHERE row_num = 1
   ORDER BY transaction_ref;

   Show outputGuess what it prints first

   transaction_ref | merchant_id | amount | status  | received_at
   ----------------+-------------+--------+---------+--------------------
   TXN_9001        | 501         | 1200   | SUCCESS | 2024-03-01 10:00:05
   TXN_9002        | 502         | 3400   | SUCCESS | 2024-03-01 10:05:00
   TXN_9003        | 503         | 850    | SUCCESS | 2024-03-01 10:10:10

   Common mistake

   Writing WHERE ROW_NUMBER() OVER (...) = 1 directly in the outer query, which triggers a syntax error.

   How you will know

   The result contains exactly 3 clean records, all with status SUCCESS and no duplicate transaction references.

Build it yourself

Write your solution below, run it in your browser, and pass every check.

What the result means

Interpretation

The deduplication query successfully isolates the authoritative financial records from noisy webhook retry logs. For TXN_9001, the pipeline discarded the initial PENDING event and retained the finalized SUCCESS confirmation. For TXN_9003, three events arrived across PENDING, FAILED, and SUCCESS states; our deterministic status priority logic correctly selected the SUCCESS event. This guarantees merchant settlement systems disburse exact funds without double-counting.

The message you would send

Hi Merchant Settlements Team, the SQL deduplication query is deployed and verified. It successfully eliminates duplicate webhook retries across all transactions while deterministically selecting confirmed SUCCESS states. Merchant settlement payouts are now protected from double-disbursement risks.

Put it on your CV

Resume or LinkedIn bullet

Built an idempotent webhook deduplication pipeline in SQL using ROW_NUMBER() window functions and multi-attribute tie-breaking in DuckDB.

README for GitHub

# Idempotent Event Deduplication with Window Functions

A production SQL deduplication pipeline isolating authoritative payment records from distributed webhook retry streams.

## Features
- Partitioned ranking using ROW_NUMBER() over business reference keys
- Multi-column deterministic tie-breaking prioritizing finalized status over timestamps
- Common Table Expression filtering guaranteeing exactly-once payout semantics
- Seamless resilience against out-of-order message delivery in distributed architectures

Keep going

• LessonSQL deduplication and upserts
• Case studyCDC Event Order Deduplication
• Case studyThe Duplicate Click Problem