Deduplicate Out-of-Order Replication Events

Business Context

You’re on the data platform team at a fintech payments company processing 50M+ ledger events/day from multiple upstream services (KYC, card processing, bank transfers). Events are ingested into a centralized warehouse for downstream reconciliation and regulatory reporting.

A recent incident revealed that the upstream “replication” service occasionally emits duplicate Repl created events for the same business entity (e.g., the same transfer), and these duplicates can arrive out of order due to retries, network delays, and at-least-once delivery semantics. If duplicates aren’t removed, downstream jobs may double-count “created” states, causing incorrect operational dashboards and noisy alerts.

Task

Write a SQL query that identifies duplicate Repl created events that may arrive out of order and outputs the rows that should be removed (i.e., all but the earliest true creation per entity).

Assume that for a given entity_id, the correct record to keep is the event with the earliest created_at timestamp. If multiple events tie on created_at, keep the one with the smallest event_id.

Requirements

1. Consider only rows where event_type = 'Repl created'.
2. Define duplicates as events with the same entity_id and event_type.
3. For each entity_id, keep exactly 1 Repl created event (the earliest by created_at, tie-break by smallest event_id).
4. Return the events that should be deleted, with columns: event_id, entity_id, event_type, created_at, ingested_at, and duplicate_rank (where rank 1 is the kept row, rank > 1 are duplicates).
5. Order the output by entity_id, then duplicate_rank, then created_at.