Retry-Driven Session Inflation in a Streaming Clickstream Pipeline

Start by separating three mechanisms that can inflate sessions: duplicate events, session fragmentation, and cross-day attribution. Duplicates are the first suspect because an 8% retry rate during the peak window can produce up to 8% extra rows, and if the SDK generates a new session identifier on retry, each duplicate can create an extra session rather than merely an extra event, pushing the session-level error above the event-level rate. Confirm by counting rows versus COUNT(DISTINCT event_id) by day and by hour; a duplicate rate near 8% in peak hours and near zero off-peak supports this. Next, compare event counts by event_time date with counts by ingestion-time date. If totals differ only at day boundaries, the issue is late-arriving events assigned to the wrong day, which shifts rather than inflates a daily total. Third, check the sessionization key: if session_id is regenerated per retry, group by a stable device_id plus a 30-minute inactivity window and compare session counts. Also compare a 1% sample against the app SDK's raw event export. Only after the dominant mechanism is quantified should finance receive an interim number; a deduplicated event-time count with a stated late-arrival cutoff is defensible.

Partition the raw table by DATE(event_time), not ingestion time, and write through a staging table that is merged on event_id. In Dataflow, keep a keyed state store of event_id values with a timer set to the allowed lateness-eight hours covers the observed six-hour tail with buffer-and drop duplicates on arrival; this makes retries idempotent rather than merely filtered downstream. Do not use a fixed 24-hour dedup window if the SDK can retry beyond it; align the window to the maximum retry policy. For late events, use a watermark with allowed lateness and emit pane information so downstream sessionization can update affected windows; alternatively, schedule a reprocessing job over the last two event-time partitions after the lateness horizon. For the finance close, compute revenue attribution from a deduplicated, event-time-based snapshot with an explicit cutoff, publish the restatement delta versus the previously reported number, and freeze the snapshot at a stated time. Backfill historical partitions through the same merge logic so the fix is consistent, and record the duplicate rate per day to show whether the interim number is stable.

Use four checks tied to distinct failure modes. First, uniqueness: duplicate rate = 1 − COUNT(DISTINCT event_id)/COUNT(*), evaluated hourly and daily, with a warning at 1% and a page at 3% during peak; the current 8% retry rate would have alerted immediately. Second, freshness: MAX(event_time) lag behind processing time, warning above 15 minutes and page above 60 minutes; this catches stalled ingestion, which row-count checks miss. Third, completeness: null or malformed event_id and event_time counts, target zero, since dedup is impossible without a key. Fourth, volume: event count versus the same hour in the prior seven days, with a band of ±25% to absorb weekday seasonality; alert only on two consecutive breaches to avoid noise. Route uniqueness and completeness alerts to the pipeline owner, freshness to on-call, and volume to the analytics owner. Add a reconciliation check against the app SDK's daily event total, with a tolerance of 2%, because that external comparison is what actually surfaced the discrepancy. Store check results in a quality table so trends, not just point alerts, are visible.