Design a clickstream pipeline from ingestion to data warehouse

WHAT THIS TESTS: This question evaluates your ability to design a production-grade data pipeline that handles high-velocity, high-volume events with durability, scalability, and clear separation of concerns. Interviewers care about your reasoning around backpressure, buffering strategies, exactly-once or at-least-once semantics, cost-aware technology choices, and how you model data for analytical queries rather than just listing AWS services.

A GOOD ANSWER COVERS: First, ingestion architecture: a load-balanced fleet on ECS behind an ALB or Global Accelerator to accept HTTP events, with a buffer such as Amazon Kinesis Data Streams or Amazon MSK to decouple producers from processors and absorb traffic spikes. Second, processing: a scheduled or event-driven Spark job on EMR Serverless that reads raw batches from S3, deduplicates, enriches, and sessionizes clicks into structured events, sinking results back to S3. Third, loading and modeling: an EventBridge rule triggering a Step Functions workflow that loads processed data into Amazon Redshift, using a star or flat schema optimized for session metrics and funnel analysis. Fourth, justification: Kinesis for low-latency buffering, MSK for Kafka ecosystem compatibility, EMR Serverless for auto-scaling batch ETL without cluster management, and Redshift for columnar aggregation performance.

COMMON WRONG ANSWERS: Proposing direct writes from web servers into Redshift or a relational database, which ignores backpressure and will fail under load. Suggesting a single monolithic service that both ingests and transforms, blurring failure domains. Ignoring late-arriving events or duplicate clicks, which corrupts session metrics. Choosing streaming for everything without acknowledging that clickstream analytics is often batch or micro-batch friendly and cheaper to process that way.

LIKELY FOLLOW-UPS: How would you handle late-arriving events after the daily batch has run? How do you guarantee exactly-once semantics when the same click may be retried by the client? What is your retention and replay strategy if a bug corrupts three days of processed data? How would you scale the ingestion layer independently if traffic grows tenfold?

ONE CONCRETE EXAMPLE: A retail site generates fifty thousand events per second during flash sales. The ingestion module uses an ALB distributing to an ECS fleet that sinks into Kinesis Data Streams. A Lambda consumer batches events into S3. Every fifteen minutes, EventBridge triggers an EMR Serverless Spark job that reads the new S3 partition, joins clicks with a user dimension table, assigns session IDs using a thirty-minute inactivity gap, and writes parquet to a processed bucket. An Object Created event in that bucket triggers Step Functions to run a Redshift COPY command, loading a sessions fact table and a devices dimension table. Analysts query session duration and conversion funnels in Redshift with sub-second aggregation response times.

Source: docs.aws.amazon.com