How do you ensure accurate counts with duplicate analytics events?

What's really being asked

This question assesses your understanding of real-world data pipelines, which often have at-least-once delivery guarantees. It's not a trick SQL question; it's a systems design question disguised as an analytics problem. The interviewer wants to see if you think about data lineage, idempotency, and the trade-offs between accuracy, cost, and latency.

The full answer

A strong answer has three parts. First, identify the immediate impact: a simple COUNT() will overstate the number of logins because each duplicate is counted as a legitimate event, leading to inflated metrics. Second, propose a robust solution. The key is idempotency. This requires ensuring each event has a unique identifier (e.g., a UUID) assigned by the producer at the moment of creation. The reporting query then changes from COUNT() to COUNT(DISTINCT event_id). Third, discuss the trade-offs. While COUNT(DISTINCT) is simple, it can be computationally expensive on large datasets. A senior candidate might also mention alternative strategies like stateful stream processing (e.g., using a tool like Flink or Dataflow) to deduplicate events in a time window before they are written to the final table, trading higher infrastructure complexity for faster query times.

The mistakes people make

A major red flag is confusing unique login events with unique users. Suggesting COUNT(DISTINCT user_id) is a classic mistake; this calculates the number of distinct users who logged in, not the total number of legitimate login actions. Another weak answer is to suggest cleaning the data "later" without a concrete mechanism, which ignores the need for timely, reliable reporting. Finally, simply stating "use COUNT(DISTINCT event_id)" without explaining why (idempotency) or acknowledging the performance cost shows a lack of depth.

What usually comes next

Expect questions about the implementation details. "Where would you generate this unique event ID? On the client or the server?" (Answer: As early as possible, ideally the client, but server-side is more reliable). "What if you can't change the event producer? How would you deduplicate then?" (Answer: Composite key of user_id, timestamp, and other salient fields, but acknowledge this is brittle). "How would you handle duplicates that arrive days apart, outside your processing window?" (Answer: This points to batch reconciliation or choosing a data warehouse that handles deduplication on write, like one using a MergeTree or supporting UPSERT semantics).

A concrete example

Let's say we get 10,000 'login' events, but 500 are duplicates. A COUNT(*) query would return 10,000, incorrectly inflating our daily logins by 5%. If each event had a unique event_id, a COUNT(DISTINCT event_id) query would correctly return 9,500. For a high-volume system processing millions of events per day, the cost of a COUNT(DISTINCT) on a petabyte-scale table could be substantial, potentially running for minutes and costing tens or hundreds of dollars per query, which is why an upstream streaming deduplication solution might be better.