SQL Deduplication for Structured Events

Deduplication is a business-definition problem before it is a SQL problem. Two events that look similar may represent a duplicate delivery, an intentional retry, or two valid user actions. Start with a source-generated identifier for the logical event or delivery. Do not infer identity from a timestamp and message string unless the producer contract guarantees that combination is unique.

Select one row for each stable identifier

WITH ranked AS (
  SELECT
    event_id,
    account_id,
    event_name,
    timestamp_utc,
    received_at,
    ROW_NUMBER() OVER (
      PARTITION BY event_id
      ORDER BY received_at ASC
    ) AS delivery_rank
  FROM product_events
  WHERE timestamp_utc >= now() - INTERVAL '7 days'
)
SELECT
  account_id,
  event_name,
  timestamp_utc
FROM ranked
WHERE delivery_rank = 1;

This version keeps the first received copy. A state-sync table might instead keep the newest record. Write the choice down beside the query, because changing the ordering changes the meaning of the result.

Measure duplicates before removing them

Count raw rows, distinct identifiers, and identifiers with more than one delivery. Break the result down by producer, SDK version, release, or delivery path when those dimensions can identify the source. A deduplication CTE may make a downstream dashboard correct while hiding a growing ingestion problem.

Do not deduplicate on account_id and event_name alone. One account can legitimately complete the same action many times. Likewise, retries can be valid analytical events when the question is reliability, even if product conversion counts only the final successful outcome.

Late events require a bounded correction window. A duplicate that arrives after a report has been exported may change the final count, so record the reporting cutoff and whether results are provisional.

Use the Duplicate Event ID recipe to quantify source duplication and Window Functions to understand the ranking operation. The event schema guide explains how to add identifiers that make the rule auditable.