Towers and Phones, Same Story

A hard 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
hard
Seniority
L6

Problem

Our app performance reports can't tell a weak signal from a slow app, because nothing ties the 2 billion daily app events to the 400,000 towers reporting signal, handoff and coverage readings every few seconds. Design the pipeline that ingests both feeds and models them as warehouse dimensions and facts.

Tower signals meet app events. Somewhere in between is the truth.

Business requirements

Worked solution and explanation

The trap

This is a point-in-time join dressed up as a telemetry warehouse. Anyone can land two Kafka feeds and draw a fact table. What separates candidates is the join key: which tower the device was on at the moment of the event, not the tower it is on now. Join on the latest tower and every handoff silently reattributes old sessions. Dashboards still render, and ML retrains on a different truth every week. The second trap: the joined record is more sensitive than either input, because it can place a person.

Walk the requirements

Step 1: Split by freshness budget

Infrastructure needs tower health in minutes, analytics works hourly, and ML retrains weekly. A real-time Flink job feeds infra_only_tower_store for outage response. A Spark batch builds fact_session_connectivity hourly behind session_quality_mart. ML reads that same fact once a week. One shared streaming tier would cost too much for ML and still not be tuned for outages.

Step 2: Join as-of, never on the current tower

Key dim_tower on (tower_id, valid_from, valid_to). In as_of_join_batch, each app event picks the tower version valid at its own event_time, so handoffs resolve themselves. An equi-join on the latest tower_id passes every test you would think to write, which is why it ships.

Step 3: Make the privacy boundary a stage

Precise coordinates land only in infra_only_tower_store, a separate store that only infra_console reads. On the batch side, location_bucketer swaps coordinates for a coarse cell before dim_tower or the fact is written. As a result, nothing in the warehouse holds a location finer than a cell, and no analyst join can recover one. 'Please don't query that column' fails a privacy review; a pipeline with no path to the column passes it.

Step 4: Flag gaps, never drop them

quality_flagger marks an event with no coverage (tower_id null, a coverage_status value) and an implausible signal (quality_flag), then passes both rows on. Filter them out and 'where do we lose coverage' becomes unanswerable.

The reference design

Streaming serves tower health and exact locations to infrastructure alone through `infra_only_tower_store`. The hourly batch builds the as-of `fact_session_connectivity` with bucketed cells only; ML pulls it weekly.
  • tower_telemetry_raw (source, Kafka)
  • app_log_raw (source, Kafka)
  • tower_health_stream (transform, Flink); freshness: real-time
  • infra_only_tower_store (storage, PostgreSQL)
  • as_of_join_batch (transform, Spark)
  • quality_flagger (quality gate, Great Expectations)
  • location_bucketer (transform, dbt)
  • dim_tower (storage, warehouse)
  • dim_device (storage, warehouse)
  • fact_session_connectivity (storage, warehouse)
  • session_quality_mart (storage, warehouse); freshness: < 1h
  • infra_console (consumer, Grafana)
  • analytics_view (consumer, Metabase)
  • ml_weekly_training (consumer, Jupyter)
  • tower_telemetry_raw -> tower_health_stream
  • tower_telemetry_raw -> as_of_join_batch
  • app_log_raw -> as_of_join_batch
  • tower_health_stream -> infra_only_tower_store
  • infra_only_tower_store -> infra_console
  • as_of_join_batch -> quality_flagger
  • as_of_join_batch -> dim_device
  • quality_flagger -> location_bucketer
  • location_bucketer -> dim_tower
  • location_bucketer -> fact_session_connectivity
  • dim_tower -> fact_session_connectivity
  • dim_device -> fact_session_connectivity
  • fact_session_connectivity -> session_quality_mart
  • session_quality_mart -> analytics_view
  • fact_session_connectivity -> ml_weekly_training
  • Sources: tower_telemetry_raw, app_log_raw
  • Streaming path: tower_health_stream, infra_only_tower_store
  • Batch join path: as_of_join_batch, quality_flagger, location_bucketer, dim_tower, dim_device, fact_session_connectivity, session_quality_mart
  • Consumers: infra_console, analytics_view, ml_weekly_training
Latest tower join
ON e.device_id = d.device_id AND t.tower_id = d.current_tower_id. A cheap equi-join. Last month's sessions move whenever the device hands off.
As-of tower join
ON e.tower_id = t.tower_id AND e.event_time BETWEEN t.valid_from AND t.valid_to. A range join, costlier, and stable forever.

Common follow-up questions