# Towers and Phones, Same Story > Tower signals meet app events. Somewhere in between is the truth. Canonical URL: 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. ## Business requirements - Infrastructure needs tower health for outage response, product analytics works in hours, and ML retrains weekly. - Analysts need to know which tower a device was connected to when an app event happened; today they only have one or the other. - Combining tower and app data can pinpoint where a person physically is; only the infrastructure team can see exact locations, and everyone else sees only a coarse area. - Some regions have no tower coverage; analysts need to know an event happened there, not see it disappear. ## 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. > **The tower is a moment, not an attribute** > > Model towers as a slowly changing dimension and join on `event_time BETWEEN valid_from AND valid_to`. Once the join is as-of, the rest follows: a streaming path for health, a batch path that builds the fact, and a privacy split drawn into the pipeline as a stage. ### 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 **Pipeline stages** - 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) **Data flow** - 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 **Layers** - 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 *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.* **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. > **Three teams, three answers** > > Ship the latest-tower join and analytics contradicts infrastructure. Each team builds its own 'fixed' table, the warehouse grows competing answers to 'which tower was this', and `fact_session_connectivity` gets rebuilt six months in. > **A restricted column in a shared dimension leaks** > > Keeping exact coordinates in a warehouse dimension the fact links to means any analyst join recovers them. The boundary holds only when the precise copy lives in a store no analytics path reaches. > **Say 'as-of' before you draw a box** > > The tell is naming the join semantics and the privacy split before picking tools. Candidates who start at Kafka and the warehouse rarely get to either. ## Common follow-up questions - A device hands off mid-session. How does `fact_session_connectivity` represent it? _(Grain. Per-event grain resolves it for free; per-session grain must split the session or pick a dominant tower.)_ - An analyst wants event counts by coverage area. Which table, and what can't they read? _(Whether the privacy boundary really exists: bucketed cells in `dim_tower` and `session_quality_mart`, exact coordinates only in `infra_only_tower_store`.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/towers_and_phones_same_story) - [System Design Interview Questions](https://datadriven.io/data-engineering-system-design) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.