It’s 9:14 PM on a rainy Friday in Bengaluru. Nikita has just wrapped up a long week at work and wants to get home. She taps Request Ride.

Within about 400 milliseconds her request has to be authenticated, rate-limited, priced, safely recorded and handed to dispatch. Within a few seconds, a dozen systems in her region have to agree on who picks her up, what she pays, how long she waits, and what gets written down so it can be audited, analyzed and fed into a model later.

Meanwhile, in a glass-walled meeting room, Nikhil, a senior data engineer, is taking his turn in the candidate’s chair at a system-design practice session with friends. One of them plays interviewer and asks the question that has ended many good interviews. Nikhil is ready for it.

“Design the data model for a ride-hailing app.”

This post follows Nikita’s ride from tap to payment, and then follows the data after she gets home. At each stop we look at the part of the system that handles it, and at the questions an interviewer is likely to ask there: what they’re really testing, what a strong answer sounds like, and the follow-up question that trips people up.

The story is in three acts:

• Act I · The whiteboard: how to run the 45 minutes, requirements, napkin math, the front door (API gateway, rate limiting, load shedding) and the core entities.
• Act II · The ride: real-time matching, race conditions, CAP trade-offs, sharding and scaling, event logs, regions, caching, error handling, IDs and payments.
• Act III · After the ride: the data pipeline, observability and real-time dashboards, the scheduler, the analytics star schema, slowly changing dimensions (SCD Types 1, 2 and 4), and a lightning round of the less common interview questions.

Grab a coffee, maybe two. The ride takes 27 minutes and the interview takes 45. This guide is long because it’s meant to be complete: bookmark it and come back to each act.

Act I · The whiteboard

Scene 1: The question (a.k.a. the first 5 minutes)

Most candidates stumble in the first five minutes. It isn’t a knowledge problem. They start drawing users, drivers and trips tables before they know what they’re designing for.

Nikhil takes a breath. In a system design round, the strongest candidates drive the conversation instead of waiting for questions. So before anything else, he tells the interviewer how he’ll use the time:

He also says: “Stop me whenever you want me to go deeper on something. Matching and payments are usually the interesting bits.” Interviewers love that line, because it hands them control while showing that Nikhil knows where the hard parts are.

Then he writes down the requirements in two columns:

Now he sets out the objective: a robust, real-time and analytics-ready data model for a ride-hailing platform that serves millions of users and daily transactions across regions. Then he says his assumptions out loud:

• Actors and roles: riders and drivers are different roles, but both are users.
• Core workflows: the rider requests a ride → the system matches a driver → the trip is tracked in real time → completion and payment → post-trip feedback and ratings.
• Scale and performance: millions of trips a day, high concurrency, users spread around the world.
• Latency: under a second for booking, matching and tracking. Reporting can be batch or near-real-time.
• Compliance and regulation: GDPR, CCPA and India’s DPDP Act. We must support data deletion, anonymization and audit trails.

Next come the architectural principles that every later decision has to follow:

• Real-time readiness: live trip status, driver availability and matching.
• Data lineage: every trip state transition can be traced for audits and debugging.
• Versionability: schema evolution, because vehicle types and pricing strategies will change.
• Event-centric thinking: every major transition is an event, so it can be time-traveled, replayed and analyzed.
• Separation of concerns: transactional data is decoupled from analytical and compliance workflows.

Finally he sketches a first stack, with a one-line reason for each layer:

Scene 2: The napkin math

Before choosing between DynamoDB and Postgres or deciding what goes in Redis, Nikhil does what many candidates skip: a quick estimate of scale. These numbers are assumptions stated out loud. The interviewer cares that you can reason with numbers, not that you guess a particular company’s figures.

• Trips: 20M/day ÷ 86,400 s ≈ 230 trips/s on average, and about 1,200/s at a 5× peak. That’s modest.
• Trip events: about 8 per trip → 160M/day. At ~1 KB each that’s ~160 GB/day.
• Location pings: 1M online drivers × 1 location update every 4 s (each a small batch of GPS fixes) = 250,000 writes/s. This is the real firehose.
• GPS volume: 250K × ~100 bytes ≈ 25 MB/s ≈ 2 TB/day, or roughly 800 TB/year before compression.
• Live state: 1M online drivers × ~200 bytes (position, status, current trip) ≈ 200 MB. It fits comfortably in Redis memory.

The napkin gives Nikhil his main design insight: history is huge, and “now” is tiny. The current state of the world (who’s online, where, doing what) is small and very hot, so it belongs in memory. The history is enormous and cold, so it belongs in cheap, partitioned storage with TTLs in the OLTP layer. The rest of the design follows from those two facts.

Scene 3: The front door (API gateway, rate limiting, load shedding)

“Walk me through what happens when Nikita taps the button,” says the interviewer. Nikhil starts at the edge, because every request passes through the front door before any data model sees it, and the front door is what keeps the data layer alive on a bad night.

The path of one tap

1. DNS / global load balancing: latency-based or geo DNS (or anycast) sends Nikita to the nearest healthy region, ap-south.
2. Edge / CDN: terminates TLS close to her, and applies WAF rules, DDoS protection and bot detection. Static assets and map tiles are served from cache.
3. Regional L7 load balancer: spreads traffic across availability zones and gateway instances, and runs health checks.
4. API gateway: the bouncer and receptionist (details below).
5. Services: synchronous gRPC/REST on the request path, Kafka events for everything that can happen asynchronously.
6. A real-time channel: a persistent WebSocket / gRPC stream through a connection gateway carries driver location pings up and trip updates down (“Ravi is 2 min away”). Mobile push (APNs/FCM) is the fallback when the app is in the background.

What the API gateway does, and what it deliberately doesn’t

The service map

Behind the gateway, each service owns its data (database-per-service). No other service reaches into its tables; they call its API or consume its events:

• Rider BFF / Driver BFF: backend-for-frontend layers shaped for each app.
• Location service: ingests pings and maintains the Redis geo index and the location stream.
• Dispatch / matching: finds and ranks candidates and makes the conditional assignment.
• Trip service: owns the trip state machine and trips / trip_events.
• Pricing: fare estimates, surge and promos.
• Payments: the ledger, the payment gateway integration and payouts.
• Notification, Identity, Ratings, Fraud: supporting services.

The APIs (a quick sketch)

Rate limiting: the token bucket at the door

At 9:10 PM a concert ended at the stadium three kilometres from Nikita’s office. Forty thousand people opened the app at the same moment, some of them tapping “Request” over and over. Without a rate limiter, that turns into a self-inflicted DDoS.

Where to limit: in layers. Per IP and bot rules at the edge, per user/device/API key and per endpoint at the gateway, and per-dependency concurrency limits inside services.

How to make it distributed: keep counters in Redis and update them atomically with a small Lua script (read tokens, refill based on elapsed time, decrement, set TTL, all in one round trip). At very high QPS, gateways keep local buckets and sync to Redis periodically, trading a little precision for far fewer network hops. Responses use 429 Too Many Requests with Retry-After and X-RateLimit-* headers so well-behaved clients back off.

Load shedding and backpressure: protect what matters

Rate limiting protects you from one noisy client. Load shedding protects you from everyone arriving at once. Nikhil assigns priorities:

• P0, never shed: ride requests, trip state changes, payments, safety features.
• P1, degrade gracefully: live tracking frequency (every 4 s becomes every 8 s), ETA refinements.
• P2, shed first: trip history, promo carousels, recommendations, analytics beacons.

Techniques: adaptive concurrency limits per service, queues with deadlines (drop work whose client has already timed out; serving a dead request is pure waste), autoscaling on RPS and queue depth, and pre-scaling for known peaks such as New Year’s Eve, festivals and large concerts.

Scene 4: Meet the cast

Only now does Nikhil draw entities. Because he knows the workflows and the scale, he can explain each one in terms of how it is used.

• users holds shared identity and PII. riders and drivers are role tables keyed on the same user_id. The same person can be both, which is why “is-a” beats two unrelated tables.
• trips is the central entity: one row per trip with its current state. It is deliberately denormalized, copying vehicle_type and driver_rating as they were at booking time.
• trip_events is the append-only history of every state change (Chapter 5).
• trip_location_logs holds the GPS trail of trips in progress. It’s the highest-volume table and has a TTL. Off-trip pings (drivers cruising) only go to Redis and Kafka → lake; writing all 250K/s into DynamoDB would be needlessly expensive.
• vehicles and driver_sessions record what a driver drives and when they were online.
• payments and ratings record money and feedback, and both carry compliance and refund obligations.
• promos and surge_zones are pricing inputs, with hot copies in Redis.

With DynamoDB you design keys from access patterns, not from entities. Nikhil writes the access patterns next to the tables:

Act II · The ride

Chapter 1 · 9:14:02 PM: Nikita taps “Request”

The first thing the system needs is available drivers near Nikita, right now. It sounds simple, and it’s where a lot of designs fall over.

If you answer “we query the drivers table”, your rider ends up like the skeleton on the left. Luckily, Nikita is riding on Nikhil’s design. The napkin already told us why the table scan is wrong: 250,000 location updates a second, read thousands of times a second per city. That’s an in-memory workload.

Nikhil applies the first two operational design principles:

• Low latency first: optimize for fast matching, live driver updates and rider notifications. Use DynamoDB or Redis for latency-critical lookups, and keep read/write paths for real-time entities under 100 ms where feasible.
• Cache what you query frequently: real-time availability, trip status and driver state hit Redis first. Use TTLs and pub/sub for invalidation and synchronization, and don’t cache cold, low-value entities, so memory is kept for hot data.

How it works: each driver’s app sends a location update every few seconds. The location service writes it to a geo index, available_drivers:<region> (a Redis geo set, which is a sorted set underneath). At scale that key is split per H3 cell, available_drivers:<region>:<cell>, because one Redis key lives on one shard (Chapter 4). One subtlety interviewers like: Redis TTLs expire whole keys, not individual members of a set. So the service also keeps a companion sorted set scored by last-ping time, and a sweeper removes drivers who haven’t pinged for ~30 s (ZRANGEBYSCORE + ZREM), while driver_state:<driver_id> hashes carry their own TTL. Nobody gets matched with a driver whose phone died. Candidates are ranked by proximity and recency: a driver 400 m away who pinged 2 s ago beats one 300 m away whose last ping was 25 s ago. In practice, “proximity” means road-network ETA from a routing engine, not straight-line distance. A driver 300 m away across a river or on the wrong side of a flyover can be ten minutes out.

Chapter 2 · 9:14:05 PM: Two riders, one driver

Ravi, a driver two streets away, is the best match for Nikita. He’s also the best match for Karan, who tapped “Request” at the same millisecond from the café next door.

This is the question that separates people who have built these systems from people who have only drawn them. Only one booking can win, and the database should enforce that, not luck.

Nikhil’s answer is a conditional write on the source of truth: set Ravi to ON_TRIP only if he’s still AVAILABLE. In DynamoDB that’s a ConditionExpression (or a TransactWriteItems call that creates the trip and flips the driver’s state atomically). In Postgres it’s UPDATE … WHERE status = 'AVAILABLE' with a row count check. The losing request gets a clean failure and moves on to the next driver. Tonight Nikita’s write lands first, and Karan gets matched with a driver 90 seconds away. Nobody is double-booked and nobody is left stranded.

One more real-world detail: the driver has to accept. Dispatch doesn’t just assign Ravi; it offers the trip to him. The conditional write puts him in OFFERED (a short reservation, e.g. 15 s, so no one else can offer him a trip), his app shows the request, and only his tap moves the trip to ACCEPTED. If he declines or the offer times out, the reservation is released and the next candidate gets the offer. Large platforms also batch matching: instead of greedily giving each rider the nearest driver, they collect requests for a second or two and solve a small assignment problem, which lowers the total pickup time across all riders.

This follows the operational flow using Redis from the design:

1. The rider starts a booking, and the service checks available_drivers:<region>.
2. Candidate drivers are ranked by proximity and recency.
3. The booking is confirmed with the conditional write in DynamoDB (durable path, source of truth), then trip_status:<trip_id> and driver_state:<driver_id> are updated in Redis (hot path).
4. The change is persisted to downstream systems through the dual-write / change-stream pattern, and notifications go out: “Ravi is on the way”.
5. TTLs clean up expired and inactive keys.

Chapter 3 · 9:14:06 PM: The consistency dial (CAP, for real this time)

The interviewer leans back: “You keep saying ‘source of truth’ and ‘strong consistency’. Is your system CP or AP?”

This is the CAP theorem question, and there’s a trap in it. Quick recap: when a network partition (P) happens, a distributed system has to choose between consistency (C), where every read sees the latest write or gets an error, and availability (A), where every request gets an answer even if it’s stale. You can’t avoid partitions, so the real choice is C or A when things break.

The trap is answering for the whole system. Nikhil’s answer is “it depends on the operation“, and he backs it up:

A strong answer also mentions PACELC: if there’s a Partition, choose A or C; Else, choose Latency or Consistency. Even when nothing is broken, strong consistency costs latency. DynamoDB reflects this directly: reads are eventually consistent by default (cheaper and faster), strongly consistent reads are opt-in per request on the base table, and GSIs only support eventually consistent reads. That’s why the “assign driver” check reads the base table, never a GSI.

Across regions, DynamoDB Global Tables replicate asynchronously by default (multi-Region eventual consistency, with last-writer-wins on conflicts). Since mid-2025 there’s also a multi-Region strong consistency (MRSC) mode that offers a zero recovery point objective (RPO) at the cost of higher write latency. Nikhil’s design avoids needing it for most data by homing each trip in one region (Chapter 6), so two regions never compete to write the same trip.

Chapter 4 · 9:14:10 PM: The stadium hexagon (sharding and scaling)

Nikita is lucky: her office is three kilometres from the stadium. The 40,000 concert-goers are not so lucky. They’re all asking for rides from one small patch of the map. The interviewer smiles: “A big chunk of Bengaluru’s traffic just landed in one spot. Now what?”

This is where sharding goes from a buzzword to a design decision. First, the vocabulary. Partitioning splits data into pieces. Sharding is partitioning across machines. Vertical splitting separates by table or service. Horizontal splitting separates rows of the same table. Region partitioning (Chapter 6) is the outermost horizontal split. Inside each region you need a second level.

Sharding strategies, and where each fits

For geo, Nikhil picks H3, the open-source hexagonal hierarchical spatial index that Uber built. Hexagons have uniform neighbors (every neighbor is the same distance away, unlike squares’ diagonals), and the index is hierarchical, so a hot cell can be broken into finer child cells at the next resolution. That’s exactly what the stadium needs.

Shard key per table

Taming the hot shard

• Split the cell: move the stadium cell to a finer H3 resolution so its load spreads across 7 child cells and more shards.
• Salt the key: append a suffix 0..N to spread writes, and scatter-gather on read (fine for a few hot keys).
• Cache and coalesce: 40,000 people asking “what’s the surge here?” should become one computation served from cache, not 40,000.
• Pre-warm for known events: concerts have calendars. Raise capacity and pre-position driver incentives before the encore.
• Let the platform help: DynamoDB’s adaptive capacity and automatic partition splitting absorb a lot of skew, but you still shouldn’t design a key that routes a whole city to one partition.

Scaling the whole system, not just the database

• Stateless services behind load balancers, autoscaled on RPS, CPU and queue lag.
• Cell-based architecture: each region is split into independent cells (a full stack serving a slice of users or cities). A bad deploy or a poison request hurts one cell, not the region. Roll out cell by cell.
• CQRS and read replicas: writes go to the source of truth; read-heavy views (trip history, receipts) come from replicas, GSIs or materialized views.
• Async by default: anything not needed to answer the user right now (receipts, analytics, loyalty points) goes through Kafka.
• Capacity planning: start from the napkin, validate with load tests (including replaying last NYE’s traffic shape), and keep headroom targets per tier.

Chapter 5 · 9:19–9:41 PM: The ride and its paper trail

Ravi arrives at 9:19. Nikita gets in at 9:20. They reach home at 9:41. Across the company, other teams will ask a lot of questions about this ride. Why was the pickup slow? Did the surge price apply correctly? Why do cancellations spike in Koramangala at 6 PM?

If all you stored was trips.status = 'COMPLETED', you can’t answer any of them.

This is the heart of the design: an immutable, append-only trip_events log. Every transition (requested → accepted → arrived → in progress → completed, or cancelled at any point) becomes a new row. You never update a row and you never lose one. It gives you time-travel, debugging, process mining and SLA audits, and it’s partitioned by event_date and region_id so analytics can scale.

CREATE TABLE trip_events (
    event_id    VARCHAR(26) NOT NULL,  -- time-sortable (ULID / Snowflake)
    trip_id     VARCHAR(26) NOT NULL,
    region_id   VARCHAR(16) NOT NULL,  -- partition key, always present
    event_type  VARCHAR(32) NOT NULL,  -- REQUESTED | ACCEPTED | CANCELLED | ...
    seq_no      INT         NOT NULL,  -- per-trip sequence, detects gaps/reordering
    actor_type  VARCHAR(16),           -- rider | driver | system
    actor_id    VARCHAR(26),
    payload     JSON,                  -- fare, location, reason codes, surge used
    event_ts    TIMESTAMP   NOT NULL,  -- UTC, event time (not arrival time)
    event_date  DATE        NOT NULL,  -- lake partition column
    PRIMARY KEY (region_id, trip_id, event_id)
);

The trip itself is a state machine, and the Trip service enforces its legal transitions with conditional writes. COMPLETED → ACCEPTED is rejected, and a late duplicate ARRIVED is ignored thanks to seq_no.

Three more operational principles apply here:

• Write-aware modeling: trip_events, trip_location_logs and driver_sessions take heavy write traffic. Make them append-only (less locking, faster inserts), use compound keys like (region_id, entity_id) to avoid hotspots, and add TTL indexing where the data is short-lived.
• Minimize joins on real-time APIs: denormalize critical entities, storing vehicle type and driver rating inline in the trip record, so the “Your ride” screen doesn’t need a five-way join.
• Versionability: keep rapidly evolving attributes in a JSON payload, so a new vehicle type or pricing strategy doesn’t require a migration.

SELECT t.pickup_city,
       COUNT_IF(e.event_type = 'CANCELLED') * 1.0
     / NULLIF(COUNT_IF(e.event_type = 'REQUESTED'), 0) AS cancel_rate
FROM   silver.trip_events e
JOIN   silver.trips t USING (region_id, trip_id)
WHERE  e.event_date >= current_date - INTERVAL 7 DAYS   -- partition pruning
GROUP  BY t.pickup_city;

Chapter 6 · Meanwhile, in Paris…

At the same moment, Léa is booking a ride in Paris. Her data is protected by GDPR, and it shouldn’t cross the Atlantic just because a US server was less busy. Nikita’s data falls under India’s DPDP Act. A user in California is covered by CCPA.

This is why Nikhil partitions by region from day one, even if the company launches in a single city.

• Performance optimization: Nikita’s request shouldn’t make a round trip to Virginia.
• Scalability: each region is an independent unit of horizontal scale, so tables, services and streams shard cleanly.
• Data residency and compliance: GDPR (EU) and DPDP (India) may require data to stay within certain borders.
• Failure isolation: an APAC incident shouldn’t affect US-West.
• Cost optimization: each region can have its own retention, storage and compute policies.

Implementation looks the same at every layer:

• region_id is a mandatory column and partition key in high-traffic tables (trips, driver_sessions, trip_events).
• OLTP stores such as DynamoDB or Spanner use compound keys: (region_id, entity_id).
• Lake folders are partitioned by a /region_id=…/ prefix for efficient query pruning.
• Kafka topic keys include region_id for parallel, regional consumption.

There are two options. Option 1 is a single table with a region_id column as a prefix or partition key (e.g. trip_id = apac#TRIP1234 or a (region_id, trip_id) compound key). Option 2 is separate tables per region (trips_us, trips_apac). Recommendation: use a single table partitioned by region_id unless legal or compliance rules require physical separation.

Chapter 7 · 9:22 PM: A Redis node goes down

Nikita is two minutes into her ride when a Redis node in the Bengaluru cluster fails. On-call gets paged. Nikita doesn’t notice anything.

That’s because Nikhil designed around one simple idea:

Redis is the goldfish: live, in-memory data for frequent reads and writes, evictable after the session. DynamoDB is the elephant: persistent, durable, queryable, the source of truth.

Why Redis? It’s an in-memory store with under 1 ms latency. It supports rich data structures (hashes, sorted sets, TTLs) and has built-in TTL expiry for automatic eviction. It’s easy to shard with Redis Cluster across zones, scales well with clustering and sharding, and has Pub/Sub for real-time notifications between components.

Why DynamoDB? It’s a fully managed NoSQL store with high throughput. It scales horizontally with high write volumes, offers strong consistency when required (payments, trip lifecycle), supports TTL for automatic deletion of stale records, provides Global Tables for multi-region sync, and has DynamoDB Streams to track and stream change events.

Integration strategy (hot path + durable path):

• Read path: always check Redis first for live status; on a cache miss, fall back to DynamoDB and optionally repopulate the cache.
• Write path: update DynamoDB (durable) and Redis (hot) to get low latency and auditability.
• Expiry and cleanup: Redis TTLs clear expired data; DynamoDB TTL purges stale records.
• Change streams: DynamoDB Streams or Kafka react to writes and sync downstream systems.

Best practices:

1. Sync model: write-through (writes go to both stores) for trip start and trip end; read-through (cache filled lazily when Redis is cold).
2. TTL and expiration: set TTLs on ephemeral keys to avoid memory pressure; use an eviction policy (LRU/LFU) plus per-region quotas; use DynamoDB TTL to clean up stale trips and sessions.
3. Failover and reliability: Redis is not a source of truth, so if it fails, everything must degrade gracefully to DynamoDB. Redis failover relies on replication, sharding and zonal redundancy, and application logic should detect stale or inconsistent Redis state.

The line to use in your interview: “I treat Redis as the nervous system of live trip activity: ephemeral but responsive. DynamoDB is the spine: strong, centralized and built for consistency. Together they cleanly separate real-time and durable responsibilities, and the two-layer design also supports elasticity, compliance and global distribution, which are critical for ride-hailing at scale.”

Chapter 8 · 9:26 PM: The promo service hiccups (error handling)

Six minutes into the ride, the promo service starts timing out: a bad deploy on another team. Nikita’s fare needs a promo check at the end of the trip. Does her ride break?

Not in Nikhil’s design, because he treats failure as normal. He sorts failures into categories first: transient (timeouts, throttling, 503s: retry), permanent (validation errors, 4xx: don’t retry, fix the input), partial (one step of a multi-step flow succeeded), poison (a message that fails every time), and duplicate or out-of-order delivery (normal in distributed systems).

The error-handling toolkit

• Timeouts and deadlines everywhere: every call has a timeout, and the request’s overall deadline is propagated (if 600 ms of an 800 ms budget is used up, downstream calls get only 200 ms).
• Retries, done safely: only for idempotent operations, with exponential backoff plus jitter, a max attempt count, and a retry budget (e.g. retries may add at most 10% extra load). Retry at one layer only: if three layers each make 3 attempts, one failing call can turn into 27.
• Circuit breakers: after N failures the breaker opens, calls fail fast, and after a cool-down it goes half-open to test recovery.
• Bulkheads: separate connection and thread pools per dependency, so a slow promo service can’t use up the resources the payment call needs.
• Fallbacks and graceful degradation: no promo service → finish the trip at the standard fare and apply the promo later as a credit. No fresh surge → use the quoted surge. No ETA model → use a straight-line estimate.
• Messaging safety: at-least-once delivery with idempotent consumers (dedupe on event_id), a dead-letter queue with alerting and replay tooling, and a transactional outbox for DB-plus-event atomicity.
• State-machine guards: illegal or stale transitions are rejected, not “fixed up” silently.

Sagas: long workflows without distributed transactions

Completing a trip touches several services: finalize fare → apply promo → charge rider → credit driver payable → send receipt. A two-phase commit across all of them would be fragile and slow. Instead, use a saga: a sequence of local transactions, each with a compensating action (a refund reverses a charge; a reversing ledger entry cancels a credit). You can run it as orchestration (a workflow engine like Temporal or AWS Step Functions drives each step, which is easier to see and retry) or choreography (each service reacts to the previous event, which is looser but harder to follow). For money, Nikhil prefers orchestration.

Chapter 9 · What’s this ride’s name?

Nikita’s trip needs an ID, and so do the millions of other rides happening at the same moment across dozens of regions.

Suppose you use the familiar auto-incrementing integer:

Mumbai issues trip #1001. London issues trip #1001. Now every join, dashboard and Kafka consumer downstream has a problem. At this scale a global ID strategy has to prevent duplicates across services and regions, sort naturally by time, support partition pruning and efficient scans in analytics, and fit event-driven architectures and OLTP systems, all without central coordination.

Integration considerations: use Snowflake-style IDs or ULIDs for high-throughput, sortable, distributed needs (trip_id, payment_id, event_id). Redis keys and Kafka partitions can embed region and time window for locality. In DynamoDB, use them as range (sort) keys for efficient scans. In Kafka, embed part of the ID in the message key for sharding. Ensure IDs are monotonic within a generator so sort order holds.

Chapter 10 · 9:41 PM: Nikita pays

Nikita gets home, pays ₹248 and gives Ravi five stars. For her it’s one tap. For the platform, it’s the most carefully engineered 300 milliseconds of the night, because a mistake here costs real money.

Idempotency keys are the bouncer at the payment door. Every charge attempt carries a key (e.g. trip_id + attempt_no). The payments service first does a conditional insert of that key. If the key already exists, it returns the original result instead of charging again. Network retries, app retries and saga retries all become harmless.

The money itself lives in a double-entry ledger. It’s append-only: every transaction writes balanced entries, so debits always equal credits:

ledger_entries(entry_id, txn_id, account_id, direction, amount, currency, created_at)

txn_42 (Nikita's ride, ₹248):
  DEBIT   rider:nikita.wallet_or_card     248.00
  CREDIT  platform:revenue                 49.60   -- commission
  CREDIT  driver:ravi.payable             198.40
-- refunds and corrections are NEW reversing entries, never UPDATEs

A payment moves through its own state machine: INITIATED → AUTHORIZED → CAPTURED → (REFUNDED | CHARGEBACK), with every transition logged. Many teams keep the ledger in PostgreSQL (ACID transactions, constraints, easy reconciliation queries) even when trips live in DynamoDB. Choosing the right store per workload is a sign of experience.

Act III · After the ride

Chapter 11 · 9:42 PM: The ride ends, the data keeps going

For Nikita the story is over, and she’s home. For the data platform, most of the work is just starting. Here’s everything built so far, drawn as a metro map. Follow any colored line and you’re following one piece of Nikita’s ride:

• Real-time line (red): Rider App → Booking API → Redis → ElasticSearch. Matching in milliseconds.
• Durable line (yellow): Booking API → DynamoDB → DynamoDB Streams → Kafka. The source of truth publishes every change (CDC).
• Location line (blue): Driver App → GPS pings → Kafka. The 250K writes/s firehose.
• Analytics line (purple): Kafka → S3 + Iceberg/Delta → Airflow/Dagster → BI. Cheap storage, time travel, schema evolution.
• ML line (green): Kafka → Feature Store → SageMaker/Vertex AI for ETA prediction, surge pricing and fraud detection. Predictions loop back to the Booking API for the next rider.

The lake uses a medallion layout. Bronze holds raw events exactly as received, kept for replay. Silver is cleaned, deduplicated, conformed and SCD-managed. Gold holds business-ready facts and aggregates. Tables are Iceberg/Delta, partitioned by event_date and region_id, compacted hourly (the small-files problem is real at 250K events/s), with GPS tiered to colder storage after 90 days.

This is the separation of concerns principle in action: transactional, analytical and compliance workloads never compete for the same resources, and Kafka connects them all.

Chapter 12 · Mission control: observability and real-time dashboards

At 9:11 PM, before Nikita even left her desk, an alert fired in #oncall-bengaluru: demand spike in one cell (stadium). Surge went up, supply nudges went out to nearby drivers, and the match p99 stayed inside the SLO. No human had to do anything. That’s what good observability looks like: you know before your users do, and most of the time the system already reacted.

Layer 1: system observability (is the platform healthy?)

• Metrics: the four golden signals (latency, traffic, errors, saturation) per service and endpoint; RED (rate, errors, duration) for services; USE (utilization, saturation, errors) for infrastructure; plus Kafka consumer lag, Redis hit rate and DynamoDB throttles.
• Logs: structured JSON, sampled on success and complete on errors, with PII masked.
• Traces: OpenTelemetry end to end. The gateway starts the trace, and trip_id travels as an attribute through every hop, log line and Kafka header. “Why was Nikita’s match slow?” becomes one search.

Layer 2: SLOs and alerting (when do we wake someone up?)

Alert on symptoms, not causes (users can’t book, not “CPU at 80%”), using multi-window error-budget burn-rate alerts (fast burn pages someone, slow burn opens a ticket). Every alert links to a runbook.

Layer 3: business real-time dashboards (is the marketplace healthy?)

City operations teams watch requests per minute, match rate, ETA promised vs actual, cancellations by reason and by side, supply and demand per H3 cell, the surge heat map, payment failures per gateway, and driver online hours. They need these in seconds, not after tomorrow’s batch.

The pipeline is Kafka → Flink (windowed aggregations, e.g. 30-second tumbling windows per cell) → a real-time OLAP store (Apache Pinot, Druid or ClickHouse, built for sub-second queries over fresh data) → Grafana / Superset. Uber has written publicly about running Kafka, Flink and Pinot for exactly this kind of real-time analytics.

Layer 4: data observability (is the data itself right?)

Freshness, volume, schema drift, null rates, distribution anomalies, and lineage, with alerts routed to the owning team, just like service alerts.

Chapter 13 · The scheduler: Nikita books Saturday’s 6 AM airport ride

Before bed, Nikita schedules a cab for 6:00 AM Saturday to the airport. She’s off to Paris for that well-earned holiday. She doesn’t want to be up at 5:40 refreshing an app, so the system has to remember and act on time, even if servers restart overnight.

Scheduled rides (the product scheduler)

scheduled_rides(
  ride_id, rider_id, region_id,
  pickup_at_utc, pickup_tz,        -- store UTC + IANA zone (e.g. Asia/Kolkata)
  dispatch_at_utc,                  -- pickup_at - lead time (depends on city/traffic)
  status,                           -- SCHEDULED | DISPATCHING | MATCHED | CANCELLED
  version                           -- optimistic concurrency
)
-- index: (region_id, dispatch_bucket = minute(dispatch_at_utc))

1. Durable storage first: the schedule lives in the database, not in someone’s in-memory timer.
2. Time-bucketed polling: scheduler workers each own a set of shards (via leases or leader election) and every few seconds claim rides where dispatch_bucket ≤ now. A Redis sorted set scored by timestamp (ZRANGEBYSCORE) can act as a hot index in front of the DB.
3. Claim with a conditional update (SCHEDULED → DISPATCHING only if the version matches), so two workers can’t dispatch the same ride. Delivery is at-least-once; processing is idempotent.
4. Timeline: T-60 the job wakes and checks supply forecasts; T-20 matching starts (with incentives if supply is thin); T-10 the driver is locked and Nikita is notified; T-0 pickup.
5. Fallbacks: no driver by T-5 → widen the radius, raise incentives, notify the rider honestly with options.

Alternatives worth naming: durable workflow timers (Temporal, Step Functions) handle long waits well. Plain message-queue delays (e.g. SQS, which caps delivery delay at 15 minutes) don’t fit rides booked days ahead by themselves.

Batch orchestration (the data scheduler)

Overnight, Airflow (or Dagster) runs the platform’s housekeeping as DAGs with dependencies, retries, SLAs and alerting: hourly SCD merges and lake compaction, daily payment reconciliation at 02:00, weekly driver payouts, daily GDPR erasure sweeps, and nightly model retraining. Rules: tasks are idempotent (safe to re-run), partitioned by date (ds), backfill-friendly, and data-aware (start when the upstream partition lands, not just at 2:00 AM and hope).

Chapter 14 · Monday, 10 AM: The analysts arrive

On Monday morning the analytics team shows up with questions. “Revenue by city and vehicle type last month?” “Average wait time for Gold-tier drivers?” “Is the new promo cannibalizing organic rides?” The operational model was built for writing one trip fast. They need a model built for reading millions of trips at once.

Nikhil switches hats and draws a dimensional model (Kimball-style star schema) in the gold layer:

The facts (and their grain)

The dimensions

dim_date and dim_time (local and UTC variants), dim_rider, dim_driver, dim_vehicle, dim_zone (H3 cell → neighborhood → city → region, with boundaries that change over time), dim_promo, and a small dim_driver_perf_band mini-dimension. How each one keeps history is the next chapter, and it’s where many senior candidates slip.

Chapter 15 · Ravi upgrades his car (slowly changing dimensions)

Good news for Ravi: on 1 September he upgraded from a hatchback to a sedan. The next quarterly review asks: “What was our revenue by vehicle type in August?” If dim_driver simply overwrote the vehicle, all of Ravi’s August hatchback trips now show up as sedan trips, and the numbers are wrong.

How a dimension handles change is the slowly changing dimension (SCD) type. It’s a design decision per attribute, not per table.

Type 1: overwrite (no history)

Replace the old value. Use it for corrections and attributes where history has no analytical value: a misspelled name, a corrected phone number, an email. It’s also what GDPR erasure needs (anonymize in place).

Type 2: add a new row (full history)

Close the old row and insert a new one with a new surrogate key, effective_from / effective_to and an is_current flag. Facts store the surrogate key that was current when the trip happened, so August trips point to the hatchback row forever. (Here effective_to is exclusive: a row is valid while effective_from ≤ t < effective_to. Pick one convention and use it everywhere; off-by-one-day bugs in SCD joins are very common.)

Use it for attributes that change reporting: vehicle type, driver tier, home city, commission plan, and zone boundaries in dim_zone (when a city redraws its surge zones, old trips must keep their old zone).

Type 4: separate history (and the “mini-dimension”)

For attributes that change very often, Type 2 would multiply rows. Ravi’s rating changes after every trip, so Type 2 would create hundreds of rows per driver. Type 4 moves volatility out of the main dimension. There are two common interpretations, and it’s worth saying both in an interview:

• History table: dim_driver keeps only current values (small, fast), and dim_driver_history holds every past version for the rare question that needs it.
• Mini-dimension (Kimball’s definition): put fast-changing attributes, banded, into a small separate dimension, e.g. dim_driver_perf_band(rating_band '4.8–4.9', acceptance_band '90–95%', trips_band '1k–5k'). The fact row gets a perf_band_key for the band at trip time. There are only a few hundred band combinations, so there’s no row explosion, and history is still available through the facts.

Bonus types (in case they come up): Type 3 adds a “previous value” column (only one level of history, e.g. previous_city). Type 6 combines 1 + 2 + 3: Type 2 rows plus a current-value column overwritten on all rows, so you can report “as it was” and “as it is now” without a second join.

Which type for which attribute?

Building SCD Type 2 from the CDC stream

DynamoDB Streams feed driver changes into a driver_changes staging table. An hourly job merges them (Spark SQL on Delta/Iceberg shown; the same pattern works in Snowflake or BigQuery). First deduplicate driver_changes to the latest change per driver in the batch (or process changes in order), because MERGE fails when two source rows match the same target row:

MERGE INTO dim_driver AS t
USING (
  -- rows to INSERT as new versions (merge_key NULL never matches)
  SELECT NULL AS merge_key, c.* FROM driver_changes c
  JOIN dim_driver d ON d.driver_id = c.driver_id AND d.is_current
  WHERE d.row_hash <> c.row_hash
  UNION ALL
  -- rows used to CLOSE the current version, or insert brand-new drivers
  SELECT c.driver_id AS merge_key, c.* FROM driver_changes c
) AS s
ON t.driver_id = s.merge_key AND t.is_current
WHEN MATCHED AND t.row_hash <> s.row_hash THEN
  UPDATE SET is_current = false, effective_to = s.changed_at
WHEN NOT MATCHED THEN
  INSERT (driver_sk, driver_id, vehicle_type, tier, home_city, row_hash,
          effective_from, effective_to, is_current)
  VALUES (xxhash64(s.driver_id, s.changed_at), s.driver_id, s.vehicle_type, s.tier,
          s.home_city, s.row_hash, s.changed_at, TIMESTAMP '9999-12-31', true);

And the point-in-time answer to the quarterly question:

-- facts already carry driver_sk resolved at load time:
SELECT d.vehicle_type, SUM(f.fare_amount) AS revenue
FROM   fact_trips f JOIN dim_driver d ON f.driver_sk = d.driver_sk
WHERE  f.local_date_key BETWEEN 20260801 AND 20260831
GROUP  BY d.vehicle_type;

-- or, joining on the natural key + time range:
... JOIN dim_driver d ON f.driver_id = d.driver_id
     AND f.trip_ts >= d.effective_from AND f.trip_ts < d.effective_to

Chapter 16 · The lightning round

With ten minutes left, the interviewer speeds up. These are quick questions that test breadth. One or two confident sentences each is enough.

Epilogue: Back in the interview room

Nikhil looks at the clock: 44 minutes. The whiteboard shows one ride, followed from tap to payment and then into the warehouse, with every component placed where it was needed.

He summarizes the whole journey in one line, the same real-time workflow he started with:

Rider requests → gateway (auth, rate limit) → booking service queries Redis → match driver (conditional write) → update Redis + write DynamoDB → notify → track location via stream → complete → pay (idempotent ledger) → archive to lake → dashboards, analytics and ML.

The interviewer caps the marker and says, “Most people give me 47 tables. You gave me a system.”

The practice session moves on to the next topic. Later that evening Nikhil and Nikita head out for dinner, and the cab is booked in under 400 milliseconds, of course.

Here’s everything you might get asked, in bingo form. Screenshot it before your next loop.

The cheat sheet

What makes this a strong answer

It isn’t about knowing more tools. Anyone can list Redis, Kafka and DynamoDB. What makes the difference:

• You drive. You set the agenda, manage the time and invite the interviewer to steer.
• You tell a story. Following one ride gives the interviewer a map of your thinking, which a list of tables doesn’t.
• You explain why. Every component is tied to a moment in the ride, a number from the napkin, and a trade-off you chose.
• You bring up failure yourself. Races, outages, retry storms, clock skew, hot shards and late data come up before the interviewer asks.
• You think in operations. SLOs, dashboards, runbooks, rollouts and cost are part of the design.
• You design for later. Event logs, region keys, sortable IDs and SCDs make next year’s questions cheap to answer.

Nikita got home in 27 minutes. Nikhil’s whiteboard took 44. With this design, you can give your answer in 45.

Found this useful? Share it with someone who has a system design round coming up. Tell us in the comments which bingo square you’ve been asked, and which one caught you out.