System design interview

Batch ETL for 10TB Daily Clickstream

HardLocked55 min read

Process 10TB/day clickstream with Protobuf, Flink sessionization + Spark daily aggregations, and spot-instance cost control.

system-designbatchclickstreamcost-optimizationflinkspark

Warm-up Trace

Warm-up Trace

Start here

The problem in plain words

A large website records every click, scroll and page view. That adds up to about 10 terabytes of click events every day. Stored as plain text such as JSON, that much data drowns you twice: first in the CPU time spent reading the text, then in the storage bill.

The obvious first design is a single giant job that reads everything, groups the clicks into visits, and builds all the daily reports. It works at small scale. At this size it becomes impossible to tune, because one part of the job needs memory for millions of live visits and the other part is simple work that could be spread across any number of machines.

A better design splits it into stages: store events compactly, group them into sessions in one stage, then compute the daily summaries in another stage on cheap, interruptible machines.

Words you will meet

Clickstream
The stream of user actions on a site or app, such as page views, clicks and searches.
JSON
A text format that is easy for people to read but bulky and slow for computers to parse.
Protobuf
A compact binary format. A schema defines each field, and messages are smaller and faster to read than JSON.
Columnar file (Parquet)
A file format that stores each column together, which compresses well and lets queries read only the columns they need.
Sessionization
Grouping one visitor's events into visits. It needs memory per visitor, so it is stateful.
Flink
A stream processing engine that handles stateful, event-time work and saves checkpoints.
Spark
A batch processing engine that spreads big jobs across many machines.
Spot / preemptible instance
A discounted cloud machine the provider can take back at short notice. Cheap, but your job must survive being interrupted.
Checkpoint
A saved snapshot of a job's progress so it can restart without starting over.
Schema evolution
Changing the fields of a data format over time without breaking older readers or writers.
One giant job for everything
10 TBslow10 TB of JSONevery dayOne giant jobparse, sessions,reportsHuge bill, slowrunhard to tune or rerun
Press Play to watch it move

Each arrow is one thing happening. Play it step by step, or read the list below.

  1. 1.A single job reads all the text events and has to parse every one of them.
  2. 2.Parsing, grouping into sessions and building reports all compete in one job. It is expensive, hard to tune, and a failure forces a rerun of everything.

The numbers, as things you can picture

  • 10 TB a dayAverages out to about 116 MB every second, with busier peaks.
  • Daily rollups are easy to paralleliseEach day is a separate chunk of work that can be spread across many machines.
  • Sessionization is statefulIt must remember each visitor's recent events, which needs careful memory use.

What the interviewer wants

Design a two-stage clickstream pipeline with clear boundaries, a compact format, stateful sessionization, daily summaries on cheap compute, safe reruns of a day, and a result ready by morning.

The interview setup

Design a pipeline for 10TB of daily clickstream. Use Protobuf serialization, 2-stage processing (Flink sessionization + Spark daily aggregations), and cost optimization with spot instances.

The interviewer is testing whether you understand why one mega-job is a bad idea, why binary schemas matter at this volume, and how spot compute changes failure design.

Why clickstream gets expensive

JSON click events are easy to debug and expensive to store and parse at multi-TB/day. CPU burns on parsing. Object storage burns on fat bytes. Downstream jobs burn on reading them again.

Protobuf (and friends)

Protobuf is a compact binary schema with evolving field rules. You get smaller payloads and stronger contracts than free-form JSON. Avro and similar formats play the same role. The interview point is schema discipline + compact encoding, not brand loyalty.

2 stages: stateful vs embarrassingly parallel

Sessionization needs event-time state per user. That is a streaming or micro-batch stateful job.

Daily aggregations (revenue by country, top pages) are often group-bys over a date partition. Spark batch on spot fits well.

Mixing both in one untunable monster job makes ownership, retries, and cost controls worse.

Spot / preemptible compute

Spot machines are cheap and can disappear. Fine when checkpoints and atomic outputs exist. Painful for long state rebuilds if you did not plan for interruption.

The expanded problem

  • Ingest and store daily clickstream efficiently (Protobuf into columnar lake files)
  • Sessionize events with Flink (or equivalent)
  • Compute daily aggregations in Spark
  • Use spot where safe
  • Support reprocessing a day

Scale prompts

  • 10TB/day raw ≈ ~116 MB/s average; peaks higher
  • Compression to Parquet/ZSTD can cut storage sharply after decode
  • Session state sizing depends on concurrent users, not only TB/day

What good looks like

  • Cost of JSON called out early
  • Clear stage boundary between sessions and daily aggs
  • Spot strategy with checkpoint/commit story
  • Reprocess-by-date plan
  • Schema evolution awareness

Clarifying questions

  • Session definition and allowed lateness?
  • Retention of raw versus sessions?
  • Cloud spot characteristics and interruption rate?
  • SLA for morning aggregates?
  • Peak versus average traffic shape?

Out of scope

  • Building a new serialization format
  • Perfect ML on clickstream
  • Multi-cloud networking deep dive

Clarifying questions strategy

Ask questions that change grain, retention, SLA, or cost. If told to decide, state assumptions explicitly and continue.

What good looks like verbally

Narrate why stages exist. Name atomic publish. Name how reruns stay safe. Mention what is out of scope.

Scope control

Batch interviews reward candidates who protect the critical morning path and park nice-to-haves. Say what can wait until after the SLA table lands.