milestone 1/4 Deduplicate event retries
Identify repeated events and keep a consistent record.
Social Product Analytics · Clean event retries, reconstruct ordered funnels, sessionize activity, and compare experiment conversions.
approach
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.
worked-example.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;data for this milestone
social_events6 columns · 22 rows
| idinteger · key | event_keytext | member_idinteger | event_typetext | event_attext | ingested_attext |
|---|---|---|---|---|---|
| 1 | a | 1 | view | 2025-01-01 10:00:00 | 2025-01-01 10:00:01 |
| 2 | b | 1 | signup | 2025-01-01 10:03:00 | 2025-01-01 10:03:01 |
| 3 | c | 1 | purchase | 2025-01-01 10:05:00 | 2025-01-01 10:05:01 |
| 4 | a | 1 | view | 2025-01-01 10:00:00 | 2025-01-01 10:10:00 |
| 5 | d | 2 | view | 2025-01-02 09:00:00 | 2025-01-02 09:00:01 |
showing 5 of 22 rows