# 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 connectivity team collects telemetry from cellular towers globally - signal strength, handoff events, and coverage measurements - and we need to combine this with mobile app performance logs to understand how network conditions affect user experience. Design the data warehouse pipeline including the ingestion architecture and the dimension/fact schema. ## Worked solution and explanation ### Why this problem exists in real interviews Two telemetry streams, three teams, one warehouse, and a join key (which tower at which moment) that goes wrong silently if you do it the obvious way. The trap isn't the volume; it's that the easy join produces numbers that look right and aren't, and that the joined dataset is more sensitive than either input on its own. The natural draw is to land both feeds in the warehouse and join app events to towers on device id and the device's current tower. The fact table populates, dashboards render, infrastructure is happy. Then ML retraining produces a different model every week because the same session keeps getting reattributed to whichever tower the device is on now, not the tower it was on at the event. Analytics dashboards quietly disagree with infrastructure's; nobody notices for months. > **Trick to Solving** > > Time-and-location joins are point-in-time joins; the tower at the event's moment, not today's tower, decides which row the event belongs to. > > 1. Towers are a slowly-changing dimension. The fact joins on `event_time BETWEEN tower_valid_from AND tower_valid_to`, not on the latest tower id. > 2. Three freshness budgets means at least two paths into the warehouse: a streaming path for tower health, a batch path that builds the joined fact for analytics and ML. > 3. Precise coordinates and bucketed coordinates are different columns in different tables with different permissions; the privacy boundary lives in the schema, not in a query convention. --- ### Walk the requirements #### Step 1: Three freshness budgets, two paths Infrastructure wants tower health within minutes; product analytics is fine on hourly; ML retrains weekly. That's two paths into the warehouse: a streaming path that lands tower telemetry to a tower-health table for infrastructure, and a batch path that builds the joined session-connectivity fact on an hourly cadence for analytics and ML. Both write to the same warehouse, but to different tables sized for the consumer. One shared streaming table for all three teams ends up too expensive for ML and not fresh enough to keep infrastructure happy on outages. #### Step 2: Build the join with as-of semantics, not 'current tower' An app event at a given moment belongs to whichever tower the device was attached to at that moment, even if it has handed off since. Model towers as a slowly-changing dimension keyed on (tower_id, valid_from, valid_to) and join on `event_time BETWEEN valid_from AND valid_to`. The fact_session_connectivity row carries the tower id that was correct at the event's moment. An equi-join on 'latest tower id' looks correct in the warehouse and silently corrupts every analysis using it. #### Step 3: Precise coordinates restricted, bucketed coordinates exposed Combining tower precise coordinates with the device's app data can pinpoint a person physically. Land precise coordinates in a restricted table that only infrastructure can read, and write a bucketed location (a coarse spatial cell) onto the joined fact that everyone can read. The platform enforces the permission, not a convention. A 'we'll trust people not to query the precise column' posture is the version that fails the privacy review. #### Step 4: Coverage gaps are flagged rows, not missing rows When an app event happens in a region with no tower coverage, the join writes the row with `tower_id IS NULL` and a `coverage_status` flag. Implausible signal readings get a `quality_flag` rather than being filtered out. Analysts can ask 'how many app events happened in no-coverage areas,' and that question is only answerable if those rows exist. Filtering them out at join time is the move that makes coverage gaps invisible to the people who need to see them. --- ### The shape that fits > **What this design gives up** > > Two paths into the warehouse means two ingestion systems to operate. The as-of dimension join scans a range and is more expensive than an equi-join. Restricted views add a permission layer and a coarsening step on every read for non-infra teams. Raw query speed and pipeline simplicity get sacrificed; what arrives is joined numbers that are actually correct, three sustainable freshness budgets, and a privacy boundary that survives a review. > **What reviewers check** > > A reviewer looks at the canvas for these properties: > - A streaming path serves tower health within minutes; a batch path builds the joined session-connectivity fact for analytics and ML. > - Precise location lives in a restricted tier; the joined fact carries only the bucketed location. > **The mistake that ships** > > The build that ships joins app events to towers on the device's latest tower id and serves everyone from one fact table. The dashboards render. ML retraining produces a different model every week because the same session keeps reattributing to whichever tower the device is on now, not the tower it was on at the event. Product analytics' tower-attribution numbers contradict infrastructure's; both teams build their own derived tables to 'fix' the join, the warehouse develops three competing answers to 'which tower was this event on,' and the team rebuilds the fact with as-of semantics six months in. --- ## Common follow-up questions - A device hands off between two towers during a single app session. How does the joined fact represent that? _(Tests whether the candidate's grain is right. Per-event grain handles handoffs naturally because each event picks its own as-of tower. Per-session grain has to choose, split sessions on handoff, or attribute the session to the tower it was on longest, both have analytical consequences worth saying out loud.)_ - An analyst asks for a histogram of app event counts by tower coverage area. Which table do they query, and what would they need permission for that they don't have? _(Tests whether the candidate has actually placed the privacy boundary. Bucketed coverage is on the joined fact, which analytics can read; precise coordinates are in the restricted dimension, which they can't. If the candidate's design lets the analyst query precise coordinates here, the privacy boundary doesn't exist.)_ ## 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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.