At-least-once events inflate the daily active count — deduplicate idempotently
The event log delivers at-least-once, so the same event_id can arrive more than once. This daily-active-users query counts duplicated rows, inflating the numbers that feed retention. Fix it so re-runs are stable regardless of how many times an event was delivered.
SELECT event_date, COUNT(user_id) AS active_users
FROM events -- (event_id, user_id, event_date), at-least-once delivery
GROUP BY event_date;Find and fix the inflation — deduplicate idempotently before aggregating.
COUNT(user_id) tallies every delivered row, so duplicate deliveries inflate it. Collapse to one row per event_id first — DISTINCT ON (event_id) or ROW_NUMBER() = 1 — then COUNT(DISTINCT user_id). Keying dedup on the delivery-unique id is idempotent, so re-runs are stable.
- ✗Deduplicating the aggregated output instead of the raw event rows
- ✗Believing COUNT(*) or COUNT(DISTINCT user_id) alone removes redelivered rows
- ✗Blaming null keys rather than legitimate at-least-once redelivery
- →Why is deduplicating on event_id idempotent while COUNT(DISTINCT user_id) is not enough?
- →How would you pick which duplicate row to keep when their payloads differ?
COUNT(user_id) counts one per delivered row, so a duplicate delivery of the same event_id is counted twice. Deduplicate on the delivery-unique key before aggregating — keep exactly one row per event_id:
WITH deduped AS (
SELECT DISTINCT ON (event_id) event_id, user_id, event_date
FROM events
ORDER BY event_id
)
SELECT event_date, COUNT(DISTINCT user_id) AS active_users
FROM deduped
GROUP BY event_date
ORDER BY event_date;DISTINCT ON (event_id) collapses redundant deliveries to one canonical row; ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ...) = 1 is the portable equivalent. This is idempotent — replaying the same events yields the same deduplicated set, so the metric is stable across re-runs. Note why the shortcuts fail: SELECT DISTINCT on the aggregated output cannot un-inflate a count that already double-counted; and COUNT(*)/COUNT(DISTINCT user_id) on the raw stream still see the redelivered rows — only keying on event_id removes them robustly, whatever the downstream metric (active users, retention, event volume).
