Clickstream Analytics Lakehouse

Collect product events and serve analytics at scale

Design the pipeline that collects web and mobile product events, makes them queryable in a lakehouse table within minutes, and serves sessions, daily aggregates and dashboards. It must handle duplicates, late events, bots and user deletion.

Overview

Product teams want to know what users do: which pages they view, what they click, where they drop out of checkout. The app and the website send a small record, an event, for each of these actions. This system collects those events from every client, stores them in a data lake, and turns them into tables and dashboards that analysts trust.

The design is a pipeline with two speeds. A streaming path writes raw events into a lakehouse table within a few minutes, so a launch can be watched the same morning. A batch path rebuilds each day's sessions and aggregates once the day's events have mostly arrived. The lake is object storage plus an open table format (Apache Iceberg, Delta Lake or Apache Hudi), which adds snapshots, schema changes and row-level deletes on top of files that are never modified once written. For the formats themselves, see the Data Lakes and Lakehouse Table Formats topic and the Columnar File Formats (Parquet and ORC) topic.

The design is shaped by three facts. Clients are unreliable: phones go offline, clocks are wrong, and SDKs retry, so events come late and twice. Streams write many small files, which make queries slow. And the lake is append-only, yet the law can require you to delete one person's rows from two years of data.

Questions to ask first

How many users and events? — 100 million daily active users, 5 billion events a day (50 per user)
How bursty? — Peak is 3 times the average
How big is an event? — About 1 KB of JSON on the wire
How fresh must raw data be? — Queryable within 15 minutes of the event reaching us
When must daily tables be ready? — By 06:00 UTC for the previous UTC day, then corrected for late events for 3 more days
How long do we keep data? — 2 years of raw events; aggregates longer
Is a small error acceptable? — Dashboards may lag; counts must not double-count retries
Do we handle deletion requests? — Yes, a user's events must be gone within one month of the request

What you will be able to do

Turn 5 billion events a day into requests a second, MB/s into the log, TB in the lake and files a day, and say which number forces compaction.
Explain event time, ingest time and client clock skew, and why the table is partitioned by event date.
Remove duplicates by event ID in the stream and exactly in the daily rebuild, and rebuild a day when late events land.
Write sessionization with LAG and a running sum, and choose between pre-aggregated tables and an OLAP store.
Delete one user's rows from an immutable lake, and say what it costs.

Key concepts

Event time vs ingest time
SDK batching
Event log buffer
Lakehouse table format
Partition by event date
Small files and compaction
Dedup by event ID
Late events and partition rebuilds
Sessionization
Row-level deletes

Functional requirements

SDKs on web, iOS and Android send page views, clicks, purchases and custom events in batches
Every accepted event is queryable in the raw events table within 15 minutes
Daily tables (sessions, daily metrics per page, country and platform) are built for each UTC day and corrected when late events arrive
Dashboards show daily and hourly metrics for the last 2 years
Each event type has a contract (owner, fields, version); events that break it are quarantined, not lost
Suspected bots are flagged and left out of product metrics
A user deletion request removes that user's events from every table within one month

Non-functional requirements

No accepted event is lost; duplicates from retries never reach the daily tables (a target we chose)
Collection endpoint answers p99 under 200 ms and is available 99.95% of the time (targets we chose); clients keep events on the device while it is down
Raw data fresh within 15 minutes at p99; daily tables ready by 06:00 UTC
Writes scale to 3 times the average rate without manual work
Any day can be rebuilt from raw events without affecting other days
Dashboard queries answer in under 2 seconds at p95 (a target we chose)

Interview questions

Why not partition by ingest time, since it never lies?
How do you make sure a retried batch is not counted twice?
Write the sessionization query.
What is the small-files problem and how do you fix it?
A day was published, and then thousands of late events for it arrive. What happens?
How do you delete one user's data when the files are immutable?
Pre-aggregated tables or an OLAP store for the dashboards?
How do you stop one team's release from breaking everyone's dashboards?
How would the design change at ten times the events?