The Speed Layer
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
- L5
Problem
Our product team tracks user engagement through active user counts. They need hourly active users, daily active users, and weekly active users all refreshed every hour. Right now we compute these at query time and it is too slow. Design a pipeline that pre-computes and serves these metrics.
Dashboards can't wait for raw logs. Something has to happen upstream.
Business requirements
- Product wants HAU within minutes of the hour closing and DAU shortly after midnight; today they wait until 2am for yesterday's number.
- DAU goes into investor reporting; the count has to be exact, not approximate.
- Mobile clients buffer events for hours; a late event from yesterday has to update yesterday's numbers, not be silently dropped.
Worked solution and explanation
Why this problem exists in real interviews
DAU into investor reporting wants exact distinct counts, HAU wants minutes-fresh, and mobile late events have to correct yesterday's number rather than disappear into today. The trap is approximating the count with HyperLogLog to hit the speed budget , that breaks the investor-exact constraint , or running everything as a single nightly recompute, which misses the hourly window.
The default reach is one streaming aggregator that uses HLL to keep approximate counts and a nightly recompute to fix DAU. Investor reporting can't use HLL because the number has to be exact; the nightly run produces a different number than HLL by enough to matter. Late mobile events arrive after the daily aggregation has run; either they're dropped or they get attributed to the wrong day. HAU is fresh; DAU is exact only at midnight, and history is never corrected.
Walk the requirements
Step 1: HAU within minutes, DAU within minutes of midnight, exact counts
A streaming aggregator maintains exact distinct user sets per (period_type, period_id) , hour-of-the-day, day, week. When an hour closes, the aggregator emits the exact distinct count to the warehouse table; same for the day at midnight UTC. Dashboards read pre-aggregated rows; no recompute at query time. A 'compute distincts at query time on the raw events' design is what's been making them wait until 2am; pre-aggregated tables read in milliseconds.
Step 2: Exact counts even under freshness pressure
Investor DAU goes on the front of regulatory filings; approximate counts can't substitute. The aggregator holds the actual user-id set (or a structure that allows exact distinct, not a probabilistic sketch) for each open period. Memory cost is proportional to active users in the open windows, which is bounded. HLL is the version that hits the speed budget but produces a number that doesn't match the audited count; exact distinct sets are the contract that matches the regulatory bar.
Step 3: Late events correct the day they belong to
Mobile clients buffer events; a late event from yesterday has to update yesterday's DAU, not today's. Each event carries event-time; the aggregator keeps recently-closed periods open for the lateness allowance and reopens older periods when a late event arrives outside the allowance. The warehouse pre-aggregated row for the affected day is overwritten with the corrected count. A 'drop events past the lateness window' design is the version where history goes silently wrong; reopening older periods is what keeps the historical figure honest.
The shape that fits
- user_events (source, Kafka); parallelism: 16 partitions
- event_archive (storage, S3); backfill: partition_overwrite
- distinct_aggregator (transform, Flink); freshness: real-time; idempotency: upsert
- late_event_compactor (transform, Spark); freshness: < 1h; backfill: partition_overwrite; idempotency: staging_table
- pre_aggregated_metrics (storage, Snowflake); freshness: < 1min; backfill: partition_overwrite
- product_dashboard (consumer, Grafana); freshness: < 1min
- investor_reporting (consumer, Tableau); freshness: < 24h
- user_events -> distinct_aggregator
- user_events -> event_archive
- distinct_aggregator -> pre_aggregated_metrics
- event_archive -> late_event_compactor
- late_event_compactor -> pre_aggregated_metrics
- pre_aggregated_metrics -> product_dashboard
- pre_aggregated_metrics -> investor_reporting
- Source + archive: user_events, event_archive
- Aggregation: distinct_aggregator, late_event_compactor
- Metrics + consumers: pre_aggregated_metrics, product_dashboard, investor_reporting
Common follow-up questions
- Active users for a single hour spike past the aggregator's memory budget for an exact set. What does this design do, and what's the failure mode if not? (Tests whether the candidate sees the memory bound: the aggregator either spills the set to disk (slower but still exact), partitions across more workers, or , only if the business accepts approximation for that grain , uses an approximate sketch. The candidate should name the trade and not silently swap exactness for HLL.)
- DAU for a day a month ago is corrected upward after a late buffer drain. Investor reporting has already filed on the original number. What does this design do? (Tests whether the candidate sees the warehouse keeping the original-as-filed value alongside the restated value (or in an immutable filing archive separate from the live warehouse) so the regulatory record is preserved. The pre-aggregated table reflects the restated number; the filing's record reflects what was filed.)