Approach: use a window function (ROW_NUMBER or FIRST_VALUE) partitioned by event_id to pick the earliest or latest event_time per event_id, then upsert the deduped set into a partitioned target table to enforce idempotency.Example: keep earliest occurrence per event_id, dedupe in a staging table (staged_events) then MERGE into partitioned events table (events_table partitioned by event_date).sql
-- 1) Build deduped set in staging using ROW_NUMBER to keep earliest event_time
WITH dedup AS (
SELECT
event_id,
event_time,
payload,
event_date, -- derived partition key (e.g. date(event_time))
ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY event_time ASC, payload) AS rn
FROM staged_events
)
SELECT * FROM dedup WHERE rn = 1;
sql
MERGE INTO events_table tgt
USING (
SELECT event_id, event_time, payload, event_date FROM dedup WHERE rn = 1
) src
ON tgt.event_id = src.event_id
AND tgt.event_date = src.event_date -- ensure partition-scoped idempotency
WHEN MATCHED AND src.event_time < tgt.event_time THEN
UPDATE SET event_time = src.event_time, payload = src.payload
WHEN NOT MATCHED THEN
INSERT (event_id, event_time, payload, event_date)
VALUES (src.event_id, src.event_time, src.payload, src.event_date);
