lesson 50 Advanced product analysis · advanced

Deduplicate event retries

Identify repeated events and keep a consistent record.

the idea

Ingestion retries can produce several physical rows for one logical event. Define the event key and which version wins. ROW_NUMBER per event_key, ordered by ingested_at and id descending, keeps the latest version.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

WITH ranked AS(
  SELECT
    *,
    ROW_NUMBER() OVER( PARTITION BY event_key ORDER BY ingested_at DESC,id DESC ) AS rn
  FROM social_events
)
SELECT id,event_key,member_id
FROM ranked
WHERE rn=1;

Watch for: DISTINCT on all columns cannot remove retries whose id or ingested_at changed.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Find event retries. Logical event a has more than one physical row.

   step-1.sql Run

   sql›

   SELECT *
   FROM social_events
   WHERE event_key='a'
   ORDER BY ingested_at,id;

2. 2

   Number versions. The latest ingestion wins; id breaks timestamp ties.

   step-2.sql Run

   sql›

   SELECT
     id,
     event_key,
     ingested_at,
     ROW_NUMBER() OVER(PARTITION BY event_key ORDER BY ingested_at DESC,id DESC) AS rn
   FROM social_events
   ORDER BY event_key,rn;

3. 3

   Keep one version. DISTINCT on all columns cannot remove retries whose id or ingested_at changed.

   step-3.sql Run

   sql›

   WITH ranked AS(
     SELECT
       *,
       ROW_NUMBER() OVER(PARTITION BY event_key ORDER BY ingested_at DESC,id DESC) AS rn
     FROM social_events
   )
   SELECT id,event_key,member_id
   FROM ranked
   WHERE rn=1