How do duplicate events bias COUNT(*) and daily login reports?

WHAT THIS TESTS: This question tests whether you understand that at-least-once delivery is the default in most distributed streaming systems and that analytics must be defensive rather than naive. Interviewers want to see if you distinguish between pipeline correctness and query correctness, and if you can design an idempotent reporting layer that survives duplicates without requiring exactly-once guarantees from the transport.

A GOOD ANSWER COVERS: First, the impact. A simple COUNT() on login events will overstate daily active users because every duplicate row is counted as an additional login. If the duplicate rate is five percent, your report is off by five percent, which compounds in downstream dashboards. Second, the strategy. The best answers propose a unique event identifier generated at the source, such as a UUID or a composite key of user_id, timestamp, and a client nonce. Then they describe two complementary layers. At the storage layer, use idempotent writes like INSERT OVERWRITE or MERGE with the event ID as the join key so reruns do not create new rows. At the query layer, use COUNT(DISTINCT event_id) instead of COUNT() so duplicates collapse to one. Third, daily partition reconciliation. Because duplicates may arrive late, the strategy should include reprocessing the last N partitions or using a watermark so daily reports can be backfilled deterministically.

COMMON WRONG ANSWERS: Saying the impact is negligible without quantifying it is a red flag. Proposing SELECT DISTINCT * is wrong because two duplicate rows may differ slightly in ingestion metadata like arrival time, so DISTINCT on all columns will not collapse them. Suggesting to fix the pipeline and drop duplicates upstream is incomplete because the question asks how to ensure reports are accurate despite duplicates, which implies the pipeline may not be fully fixed. Ignoring late-arriving duplicates and assuming a once-a-day batch window catches everything is another miss.

LIKELY FOLLOW-UPS: The interviewer might ask how you detect the duplicate rate in practice, how you handle events without a natural unique key, or what tradeoffs exist between COUNT(DISTINCT) and approximate cardinality estimators like HyperLogLog. They may also ask how to reconcile late arrivals that land in yesterday's partition without rewriting the entire table, or how this changes if you move from daily batch to real-time materialized views.

ONE CONCRETE EXAMPLE: Imagine a Pub/Sub to BigQuery pipeline where subscriber retries create two percent duplicate logins. Instead of COUNT(*), you define the BigQuery table with a merge key on event_id and use a MERGE statement in your daily Airflow job: insert new events, ignore matches. Your report query then selects COUNT(DISTINCT event_id) from the partitioned table where date equals current_date. If late events arrive, you rerun the merge for the last three partitions, and the report updates idempotently because the merge key prevents double counting.