Your question is Deduplicate Late Meta Event Logs. Start with the requirements and the one table on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Meta’s event pipeline for Facebook activity can receive the same logical event multiple times, and some duplicates arrive late. Write a SQL query to return one canonical row per event_id, keeping the most recently ingested record.
event_id.event_id across the full table, even if duplicates arrive on different ingestion dates.ingested_at for each event_id.event_id have the same ingested_at, keep the one with the greater record_id.event_id, user_id, event_name, event_time, and ingested_at.event_id ascending.| Column | Type | Description |
|---|---|---|
| record_idPK | INT | Unique identifier for each ingested row |
| event_id | VARCHAR(50) | Logical event identifier used to detect duplicates |
| user_id | INT | User associated with the event |
| event_name | VARCHAR(50) | Type of event generated on a Meta surface |
| event_time | TIMESTAMP | Timestamp when the event occurred |
| ingested_at | TIMESTAMP | Timestamp when the event record arrived in the warehouse |
| source_surface | VARCHAR(50) | Meta product surface that emitted the event |