The system design question
Our analytics team wants a dashboard with a heatmap of drivers in a city. They need one view of the last 20 minutes, close to real time, and another where they can look at yesterday hour by hour. Design the backend for it.
Agree on the scope (5 minutes)
Say up front what you're covering.
| We'll design | We'll skip |
|---|---|
| Counting drivers per map cell over time | Drawing the map in the browser |
| A live view of the last 20 minutes | Taking in raw locations, which already exists |
| An hourly view of yesterday | Who's allowed to see the dashboard |
| The API the dashboard calls | Riders and trips |
Then agree on what "good" means:
- Fresh: the live view should be under a minute behind.
- Right: the hourly numbers should be correct, even if some data showed up late.
- Quick to load: switching cities or hours should feel instant.
- Cheap: it's an internal tool with maybe a few hundred users, so don't over-build it.
Rough numbers (5 minutes)
Assumptions: 1 million drivers online worldwide, each sending a location every 4 seconds, across 500 cities. A big city's map is about 5,000 cells. A few hundred analysts use the dashboard.
- ~250K
- location updates per second
- ~5K
- cells in a big city
- ~100K
- numbers in a city's 20-minute view
- ~300 MB
- of hourly counts a day, worldwide
| Question | Rough math | What it means |
|---|---|---|
| How much raw data? | 250K updates a second | Far too much to scan per dashboard load, so count as data arrives |
| How big is the live view? | 5K cells × 20 minutes | About 100K numbers per city. That easily fits in memory |
| How big is a day of hourly counts? | 500 cities × a few thousand busy cells × 24 hours | About 10 million rows a day. Small for any database |
| How many dashboard reads? | A few hundred analysts | Hardly anything. Reads aren't the problem here |
The overall design (10 minutes)
- We read the location stream that already exists for drivers. Each update gets turned into a map cell.
- Updates are split by cell, so everything for one cell lands on the same counting worker.
- Every minute, each worker counts how many drivers were in each of its cells and writes those counts to a fast in-memory store, kept for about half an hour.
- The same minute counts are rolled up into hourly numbers and saved in a database.
- The dashboard API reads minutes for the live view and hours for yesterday, and returns a grid of counts the browser colors in.
What the apps need to do
| Action | Who does it | What's sent |
|---|---|---|
| Get the live heatmap | Dashboard | City, and we return counts per cell for each of the last 20 minutes |
| Get an hourly heatmap | Dashboard | City, date and hour, and we return counts per cell |
| List cities | Dashboard | Nothing, and we return cities and their map bounds |
What we store and where
| Data | Where it goes | Why |
|---|---|---|
| Raw locations | The existing stream, kept about a day | Our input, and lets us replay if something breaks |
| Counts per city, cell and minute | Fast in-memory store, expires after about 30 minutes | The live view only needs recent data |
| Counts per city, cell and hour | A relational database | Kept for as long as the business wants, and easy to query |
| Raw locations by day | Cheap file storage | So a nightly job can redo the hourly numbers if needed |
Step 1: Counting drivers per cell (12 minutes)
First, agree what the number means. A driver sends 15 updates a minute. If you count updates, a cell looks 15 times busier than it is. So count drivers, not updates. The easiest version: at the end of each minute, put each driver in the cell where we last saw them. Every driver counts once, and the cells add up to the total online.
Turn each location into a cell using geospatial indexing, like hexagons about 500 m across. Split the stream by cell so one worker owns each cell. Inside a minute, the worker just remembers each driver's latest cell. At the end of the minute it counts and writes them out. That's stream processing with one-minute windows.
- A driver crosses a cell line mid-minute: they'd be seen by two workers. Split the stream by driver instead and send the counts per cell onward, or accept a tiny double count. Talk through which you'd pick. Splitting by driver is cleaner.
- Late updates: use the time the phone took the reading, not when we got it. Wait about 30 seconds before closing a minute, so most stragglers make it in.
- Online but not available: ask whether analysts want all drivers or only free ones. Keep the status in the count so you can show both.
Step 2: The live view (8 minutes)
Store each minute's counts in a fast in-memory store, organized by city, then minute, then cell. Each minute's entry expires after half an hour, so the store never grows.
To load the live view, the API reads the last 20 minute entries for the city. That's 20 small reads, around 100K numbers, which comes back in milliseconds. A short cache of 10–15 seconds means 50 analysts looking at the same city cost the same as one.
The dashboard polls every 15–30 seconds. Pushing updates is possible, but polling is simpler and our data only changes once a minute anyway.
Step 3: Yesterday, hour by hour (10 minutes)
For the hourly view, roll up the minute counts. Decide what an hour means: the average number of drivers in the cell across its 60 minutes is usually what people want. Adding up minutes would give you driver-minutes, which is also fine, but label it.
Write hourly counts to a relational database with city, date, hour and cell as the key. Asking for one city and one hour is a quick lookup. Ten million rows a day is small, and you can keep months of it.
Making the hourly numbers right
The live path is allowed to be a bit off, since it closes minutes after 30 seconds. For yesterday, people expect correct numbers. So keep the raw locations in cheap storage, and run a nightly job that recounts yesterday from scratch and overwrites the hourly rows. Same counting rules, just in a batch.
That also saves you if the stream workers had a bug: fix it, rerun the batch, and history is right again.
Choices you should be able to defend
| We chose | Instead of | Because | We'd reconsider if |
|---|---|---|---|
| Counting as data streams in | Querying raw locations when someone loads the page | Raw data is far too big to scan for every page load | The questions changed every day and needed raw points |
| Two stores for two views | One database for both | The live view wants speed and expiry, the hourly view wants to stick around | Volumes were tiny and one database handled both easily |
| A nightly recount for history | Trusting the live counts forever | Late data and bugs get fixed automatically | The batch job got too expensive, then we'd only recount when needed |
| Polling every 15 seconds | Pushing updates to the browser | Data only changes once a minute, so polling is simpler | Analysts wanted second-by-second views |
What could go wrong (5 minutes)
| What happens | What we do about it |
|---|---|
| A counting worker crashes | Another picks up its part of the stream and replays from where it left off. The live view has a short gap |
| The in-memory store loses its data | The live view is empty for up to 20 minutes while it refills, and the hourly view isn't affected |
| The location stream falls behind | The dashboard shows how old its newest minute is, so nobody mistakes old data for live |
| Updates arrive very late | The live view misses them, and the nightly recount includes them |
| The nightly job fails | Yesterday shows the live-path counts, marked as not final, until the rerun works |
Follow-up questions (5 minutes)
What if they want a whole week instead of a day?
Roll hours up into days in the same database. Queries over a week still only read a few thousand rows per city.
What about showing riders too, for supply and demand?
Count rider app opens the same way, with the same cells and minutes. Showing both side by side is just another column.
How would you zoom in and out?
Hexagon systems have parent cells. Store the finest level, then add up children to show bigger cells when zoomed out.
How the 60 minutes add up
| Minutes | What you're doing |
|---|---|
| 0–5 | Agree on what you're building |
| 5–10 | Rough numbers |
| 10–20 | Draw the overall design |
| 20–32 | Counting drivers per cell |
| 32–40 | The live view |
| 40–50 | Yesterday, hour by hour |
| 50–55 | What could go wrong |
| 55–60 | Follow-up questions |
Key concepts used here
- Stream processing and windowingCount things in data that never stops coming in.
- Geospatial indexingFind what's nearby with map grids like geohashes and H3.
- Caching strategiesMake reads faster, and figure out what to do when data changes.
- Relational vs non-relational databasesPick a database based on how you'll actually use it.
- Message queues and event streamsKeep services from depending on each other, and handle traffic spikes.
Further reading
We wrote this sample answer ourselves.
Practice Loop is independent and isn't affiliated with or endorsed by Uber.