How do you handle duplicate events in an analytics pipeline?

WHAT THIS TESTS: This question assesses your practical understanding of data quality and idempotent processing in distributed systems. Interviewers want to see if you can diagnose a common data integrity issue (inflated metrics) and propose a concrete, robust engineering solution with clear trade-offs, not just a vague concept. It separates candidates who just write code from those who own the quality of the data their systems produce.

A GOOD ANSWER COVERS: A strong answer has four parts. First, state clearly that COUNT(*) will be inflated, leading to inaccurate reports. Second, propose a pipeline-level solution: generate a unique event_id for each event at the source (e.g., a UUID). Third, describe using a stateful stream processing engine (like Dataflow, Flink, or Spark Streaming) to track seen event_ids within a defined time window (e.g., 24 hours for daily reports), discarding any duplicates. The state can be managed in memory or a fast external store like Redis. Fourth, contrast this with a simpler query-time solution, COUNT(DISTINCT event_id), and discuss its trade-offs (simpler pipeline vs. potentially higher query cost and latency).

COMMON WRONG ANSWERS: A major red flag is not mentioning the need for a unique event identifier. Without it, deduplication is impossible. Another weak answer is just saying "we'll deduplicate the data" without specifying the mechanism (stateful streaming vs. query-time). Suggesting COUNT(DISTINCT *) is incorrect SQL and shows a lack of fundamental knowledge. Finally, dismissing the duplicates as insignificant demonstrates a poor appreciation for data accuracy, which is critical in analytics.

LIKELY FOLLOW-UPS: "How would you choose the time window for deduplication? What are the memory/cost implications?" (Answer: Balance business requirements for accuracy against the cost of storing state. A 24-hour window for daily metrics is common.) "What if an event arrives after the deduplication window has closed?" (Answer: This is a late-arriving data problem. You might accept the small inaccuracy, or run a batch correction job later.) "When would you prefer query-time deduplication over stream-processing deduplication?" (Answer: When the pipeline must be stateless and simple, query costs are acceptable, and real-time accuracy isn't the absolute priority.)

ONE CONCRETE EXAMPLE: For a daily login count, a duplicate event would make COUNT() report 2 logins instead of 1. To fix this, our mobile client generates a unique event_id (UUID) for each login attempt. Our Dataflow pipeline keys events by user_id and uses a stateful DoFn to store the event_ids it has processed for that user in the last 24 hours. If a new event arrives with an event_id already in the state, it's discarded. This ensures our downstream BigQuery table, used for the daily report, contains only unique login events. The final query can be a simple COUNT() because the data is already clean.