Two Hundred Million Redirects
A medium Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.
- Domain
- Pipeline Design
- Difficulty
- medium
- Seniority
- L4
- Asked in
- HubSpot
Problem
Creators keep telling the support team their link stats lag, and the analytics API's date-range reports crawl, at about 200 million redirects a day with viral links spiking traffic. Design the pipeline that carries every click event both to the nightly totals behind those reports and to the creators' per-link last-hour dashboard.
Billions of clicks. One tiny code. Two very different clocks.
Business requirements
- Link creators check their dashboard right after sharing and need clicks visible within roughly a minute; perceived lag is a top support complaint.
- The analytics API answers date-range queries from daily click totals per link that the nightly batch produces, never by summing raw clicks at request time.
Worked solution and explanation
Why this problem exists in real interviews
Two consumers off one click stream with very different query shapes: a real-time dashboard for the last hour and an analytics API serving single-link lookups and date-range scans. The trap is one flat table; neither consumer is well-served.
The default reach is one streaming consumer that updates a flat clicks table. The dashboard's last-hour view is fast at low volume; at peak, an interactive dashboard query runs slow because nothing's indexed for per-link recent reads. The analytics API's date-range scans are slow because the layout doesn't match the access pattern.
Walk the requirements
Step 1: Per-link last-hour counts within a minute for the creator dashboard
Click events flow through a streaming consumer that maintains per-link last-hour counts in an online store. The dashboard reads from there; lag stays within the minute. Without a streaming tier the dashboard waits for batch and creators feel the lag they're complaining about.
Step 2: Date-partitioned aggregates for the analytics API's two query shapes
A nightly batch aggregates clicks by (link_id, date) into a warehouse table partitioned by date. Single-link lookups hit a partition pruned on date filter; date-range scans for an account read a small slice keyed on link plus date. A flat table with one row per click is the version where both query shapes scan everything; the aggregate is what makes the API fast for both.
The shape that fits
- redirect_events (source, Kafka)
- streaming_aggregator (transform, Flink)
- last_hour_store (storage, PostgreSQL)
- nightly_batch_aggregator (transform, Spark); freshness: < 24h
- daily_click_totals (storage, warehouse)
- creator_dashboard (consumer, Grafana)
- analytics_api (consumer, API)
- redirect_events -> streaming_aggregator
- redirect_events -> nightly_batch_aggregator
- streaming_aggregator -> last_hour_store
- nightly_batch_aggregator -> daily_click_totals
- last_hour_store -> creator_dashboard
- daily_click_totals -> analytics_api
Common follow-up questions
- An analytics-API customer queries a single link over a year. What in this design serves it, and how fast? (Tests whether the candidate sees the date-partitioned aggregate as the surface: the query reads partitions for the year filtered on the link id, scanning a slice rather than the full table. The aggregate's row count is bounded by (link, date), which makes the year's worth of rows small for a single link.)
- A bot floods a single link with millions of clicks in an hour. What does this design do, and what does the creator see? (Tests whether the candidate sees the streaming aggregator absorbing the burst with per-link state; the creator's dashboard shows the inflated count (which is what was redirected). Bot-detection downstream of the aggregate is a separate consumer; the click count itself reflects what the redirector saw.)