Your source system sometimes sends the same event twice. How do you handle duplicates in your pipeline?

What the interviewer wants to hear

• Identify a unique event ID or a natural key
• Deduplicate with ROW_NUMBER or DISTINCT on the key
• Use MERGE or upserts so loads are idempotent
• In streaming, use stateful dedup with a time bound
• Monitor duplicate rates

Model answer

First I find a reliable key, ideally an event ID created by the producer. If there is none, I build one from fields that together identify the event, like user_id, event_type and timestamp, and I push the source team to add a proper ID. In batch, I keep raw data as received, then in staging I deduplicate using ROW_NUMBER over the key ordered by ingestion time and keep row 1. When loading into the final table I use MERGE on the key, so reruns and retries do not add the same row again. In streaming, engines like Spark Structured Streaming or Flink support dropDuplicates with a watermark, so state is kept only for a limited time window. I also track the duplicate rate as a metric, because a sudden jump often means a bug upstream.

More medium Data Engineer questions

• A dbt model that used to take 5 minutes now takes 2 hours. How do you troubleshoot it?
• A stakeholder asks for a "real-time" dashboard. How do you decide whether to build streaming?
• Your Spark job fails with executor out-of-memory errors. What do you check and change?
• How would you design an incremental load for a large orders table that also receives updates to old orders?
• Your main nightly Airflow DAG failed at 3 a.m. and dashboards are due at 8 a.m. Walk me through how you handle it.
• How would you build a pipeline that maintains an SCD Type 2 customer dimension from a daily source extract?
• When would you choose a lakehouse with Delta Lake or Iceberg over a cloud warehouse like Snowflake or BigQuery, or use both?
• A Spark job that used to take 20 minutes now takes 2 hours. How do you investigate?
• Product engineers keep breaking your pipelines by changing database columns without warning. How would you fix this?