Problem RestatementProblem
Design the backend of an internal analytics system for a ChatGPT-like product (asked at Salesforce). Product managers and engineers want a dashboard showing things like:
- daily and monthly active users (DAU/MAU), conversations and messages per day,
- response latency percentiles (time to first token, total time),
- token usage and cost per model, errors and timeouts,
- user feedback (thumbs up or down),
- all sliced by time range, model, region, plan and app version.
The dashboard UI is out of scope. Focus on instrumentation, pipelines, storage and query serving.
RequirementsRequirements
- Collect events from the chat service (and clients).
- Near-real-time panels (errors, latency, traffic) within ~1–2 minutes.
- Historical analysis (trends, retention, cost) with ad-hoc filters.
- Consistent metric definitions ("an active user is someone who sent at least one message").
- Privacy: no raw conversation text in analytics by default.
1.1 Scale Estimates
- 20M messages/day → with request, response and feedback events, about 100M events/day (~1.2K/sec average, 10K/sec peak). This is moderate volume, and the challenge is flexibility and trust in the numbers.
What to Log (instrumentation)
Define a small set of well-structured events:
message_sent { event_id, ts, user_id(hashed), conversation_id, plan, region, app_version, client }
response_complete { event_id, ts, conversation_id, message_id, model, input_tokens, output_tokens,
ttft_ms, total_ms, status (ok/error/timeout/filtered), gpu_cluster }
feedback { event_id, ts, message_id, rating (up/down), reason_code }
session_start { event_id, ts, user_id(hashed), client, app_version }- The chat backend emits
response_complete, because server-side timing and token counts are the trustworthy ones. - No prompt or response text. Optionally keep a small, consented, sampled dataset in a separate locked-down store for quality review.
ArchitectureArchitecture
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
CS["Chat service + clients"] --> K[("Kafka - analytics events")]
K --> ST["Stream jobs - 1-min rollups"]
ST --> OL[("OLAP store - ClickHouse / Druid")]
K --> LAKE[("Data lake - raw events")]
LAKE --> BATCH["Daily batch - DAU/MAU, retention, cost"]
BATCH --> WH[("Warehouse tables")]
ML["Metrics layer - shared definitions"] --> OL
ML --> WH
DASH["Dashboard"] --> QS["Query service + cache"]
QS --> ML- Real-time path: stream jobs aggregate per minute per (model, region, plan): message counts, error counts, latency histograms and token sums → OLAP store.
- Batch path: daily jobs compute exact DAU/MAU (distinct users), retention cohorts and cost per model (tokens × price, or GPU-hours) → warehouse.
- Metrics layer: one place that defines each metric (the SQL and filters), used by both the dashboard and analysts, so numbers match everywhere.
- Query service: translates dashboard filters into queries, and caches popular panels for 1 minute.
Key Metrics and How to Compute Them
- Latency p50/p95/p99: store histograms per minute (counts per latency bucket), not averages. Percentiles are computed from merged histograms for any time range and filter.
- DAU/MAU: exact distinct counts in batch. For real-time "active users today", use HyperLogLog sketches (about 1% error).
- Tokens and cost: sum tokens per model, and multiply by a price table (versioned, since prices change).
- Satisfaction: thumbs-up rate = ups / (ups + downs), shown with the number of ratings so small samples aren't misread.
- Error rate: errors / total responses, split by error type (timeout, overloaded, safety filter).
Deep Dive — Reporting latency percentiles over billions of messagesDeep dive
"Time to first token, p95" is the metric everyone looks at. Computing it over a billion messages a day, sliced by model and region, is harder than it sounds.
Store every latency and sort at query time
Keep a row per message with its latency; a percentile query sorts the matching rows and picks the right index.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
Q["p95 TTFT, last 7 days, model X"] --> SCAN["Scan ~7 billion rows"]
SCAN --> SORT["Sort them to find one value"]
SORT --> SLOW["Minutes per dashboard panel"]
SORT --> COST["The data must be kept at full resolution forever"]Exact, and the cost is proportional to the raw event count for every single panel refresh. A dashboard with eight panels re-does that work eight times, and the retention needed to answer "last quarter" is the full raw stream.
Pre-compute an average per minute
Roll up to a small table: per minute, per model, the mean latency and the count. Queries become trivial.
Fast, tiny, and it answers the wrong question. Averages hide exactly what a latency metric exists to expose — a p95 of 9 seconds is invisible in a mean of 800 ms, and the mean barely moves when the worst 5% of requests double. Percentiles also cannot be recovered from means: there is no way to get p95 back out of an average, however the buckets are combined.
Store a mergeable sketch per bucket
Keep a histogram (fixed exponential buckets) or a t-digest per minute, per model, per region. The sketch is a few hundred bytes and supports the operation that matters: two sketches can be merged into a sketch of their union.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
EV["Message latencies"] --> H["Histogram per minute, per model, per region"]
H --> M1["Merge across minutes - any time range"]
H --> M2["Merge across regions - any slice"]
M1 --> P["Read p50, p95, p99 off the merged sketch"]
M2 --> P
H --> RET["Keep sketches for a year - raw events for 30 days"]Mergeability is the whole point: a week's p95 is the merge of 10,080 minute sketches, computed in milliseconds, and it is correct — unlike averaging per-minute p95s, which is meaningless and unfortunately common.
Three things to say alongside it:
- The error is bounded and known. Histogram bucket width sets the precision (a few percent with sensible exponential buckets), and it is constant regardless of volume.
- Keep sketches far longer than raw events. Raw for 30 days for debugging; sketches for a year, at a tiny fraction of the storage.
- Pick bucket boundaries deliberately. Latency spans microseconds to minutes, so buckets must be exponential. Linear buckets give useless resolution at the low end and waste it at the high end.
Data Quality and Freshness
- Deduplicate by
event_id. Late events are included by re-running recent partitions. - Checks: row counts vs expected, null rates, and sudden drops (a broken client release that stops sending events). Alert the data team.
- Show "data as of HH:MM" on every panel, so people know how fresh the numbers are.
- Backfills: when a metric definition changes, recompute history from the raw data lake.
Trade-offs & AlternativesTrade-offs
| Decision | Choice | Why | Alternative |
|---|---|---|---|
| Paths | Stream for live panels + batch for exact metrics | Fresh and correct | Batch only: stale; stream only: harder exact distincts |
| Store | OLAP (ClickHouse/Druid) with minute rollups | Fast slicing | Query raw lake each time: slow |
| Latency | Histograms | Correct percentiles for any filter | Averages: hide tail latency |
| Privacy | No content in analytics | Safe by default | Log full text: legal and trust risk |
Wrap-UpWrap-up
Emit a small set of structured, content-free events (message, response with tokens and latency, feedback) into Kafka. Build minute-level rollups with latency histograms in an OLAP store for live panels, and compute exact DAU/MAU, retention and cost in daily batch jobs from a raw data lake. Put a shared metrics layer in front so every dashboard uses the same definitions, and add dedup, quality checks and "data as of" freshness labels.