Duplicate ingest rate by event type

Before you fix duplicates, measure them. The ridelog collector sometimes stores an event twice. A copy has the same user_id, event_type and event_ts as the original, with a different event_id and ingested_at.

The pipeline team wants to know whether some event types are hit harder than others. Return one row per event_type with:

column	type	meaning
event_type	text	the event type
raw_rows	integer	rows in events for this type
unique_events	integer	distinct events after collapsing copies
duplicate_rows	integer	raw_rows - unique_events
duplicate_pct	decimal	100 * duplicate_rows / raw_rows, rounded to 1 decimal

An event stored three times counts as one unique event and two duplicate rows.

Row order doesn’t matter.

Tables: events.

Approach

Count raw rows and distinct event keys per type, then derive the rest.

WITH per_type AS (
  SELECT event_type,
         COUNT(*)                                  AS raw_rows,
         COUNT(DISTINCT (user_id, event_ts))       AS unique_events
  FROM events
  GROUP BY event_type
)
SELECT event_type,
       raw_rows,
       unique_events,
       raw_rows - unique_events                               AS duplicate_rows,
       ROUND(100.0 * (raw_rows - unique_events) / raw_rows, 1) AS duplicate_pct
FROM per_type;

Why it works

The duplicate key is (user_id, event_type, event_ts). Grouping by event_type fixes one part of the key, so counting distinct (user_id, event_ts) pairs inside the group counts distinct events. Everything that isn’t a distinct event is a duplicate, however many extra copies there are.

(user_id, event_ts) is a struct in DuckDB, so COUNT(DISTINCT ...) compares both fields together. Counting DISTINCT user_id and DISTINCT event_ts separately would answer a different question.

Under the hood

The outer GROUP BY is a hash aggregate with six groups. COUNT(DISTINCT ...) needs more: for each group DuckDB keeps a set of the pairs it has seen, so memory grows with the number of distinct events, not with the six groups. That’s why exact distinct counts get expensive at scale and why warehouses offer approx_count_distinct (HyperLogLog), which uses a fixed few kilobytes per group.