Design a pipeline that ingests 200,000 clickstream events per second and powers both a real-time dashboard and daily analytics.

What the interviewer wants to hear

• Collect events through an API or SDK into Kafka, partitioned by a good key
• Use a schema registry and validate events at ingest
• Stream processing (Flink or Spark) with event-time windows and watermarks
• Serve real-time aggregates from a low-latency store
• Land raw events in a lakehouse table for batch models and replays
• Handle duplicates, late data, compaction, monitoring and cost

Model answer

Clients send events to a collector service that validates them against a schema from a schema registry and writes to Kafka, keyed by user or session so related events stay ordered, with enough partitions for peak load and growth. Each event carries an event_id and an event timestamp. A Flink or Spark streaming job reads the topic, drops duplicates by event_id within a time-bounded state, and computes windowed metrics on event time with a watermark that tolerates a few minutes of lateness. Those results go to a low-latency store such as ClickHouse or Druid for the live dashboard. A separate sink writes raw events into an Iceberg or Delta table partitioned by event date, with regular compaction to fix small files. Daily dbt or Spark models build sessions and funnels from that table and correct anything that arrived after the stream watermark. I monitor consumer lag, invalid event rate and freshness, and tune retention and storage tiers for cost.

More hard Data Engineer questions

• Design a data platform for a fast-growing startup with 20 data sources and a small team. What would you choose and why?
• Explain at-most-once, at-least-once and exactly-once processing, and how you achieve effectively-once results in practice.
• A user asks to delete all their personal data. Your data is spread across a data lake, warehouse and backups. How do you handle it?
• How do you choose partitioning and clustering for a very large table? What can go wrong?
• The CEO's revenue dashboard showed wrong numbers for two days before anyone noticed. How do you fix this and prevent it from happening again?
• You must migrate a company from an on-premise warehouse to Snowflake or BigQuery without breaking hundreds of reports. How would you plan it?
• Leadership asks you to cut the cloud warehouse bill by 40 percent without hurting the business. What do you do?
• A metric definition changed and you must backfill two years of a large fact table while the daily pipeline keeps running. How do you do it safely?
• Design a CDC pipeline that replicates a busy PostgreSQL database into Iceberg or Delta tables, including updates and deletes. What are the tricky parts?