Storage: Cassandra and MySQL

In one line: the design asks its own best question here — "why can't we use only MySQL or only Cassandra?" — and the answer is the clearest polyglot-persistence argument in the course.

The stated requirements

• The database must scale horizontally. There are new customers and drivers on a regular basis.
• The database should handle a large number of reads and writes, as driver locations are updated every 4 seconds.
• Our system should never be down.

Based on our requirements, we can use Cassandra to store the driver's last known location and trip information after the trip is complete, with no further updates.

We can use a MySQL database to store trip information while it's in progress. We use MySQL for in-progress trips to support frequent updates, since the trip information is relational and must be consistent across tables.

The split is by lifecycle stage, not by entity — and that is the interesting part

Most polyglot designs split by entity: users here, orders there. This one splits the same entity across its lifetime.

A trip lives in MySQL while IN_PROGRESS and moves to Cassandra when COMPLETED. Same record, two homes, and the migration happens at a state transition.

The reason is that its requirements genuinely invert at that moment:

There are perhaps a few hundred thousand trips in flight at any moment — a small, hot, mutable working set. There are 20 million completed trips per day forever — a colossal, cold, immutable archive.

Asking one database to be excellent at both is asking for a compromise at both. When a record's requirements change at a lifecycle boundary, moving it is cheaper than finding one store that satisfies every stage.

This also answers Lesson 7's point about the trip record being financially consequential. Moving to Cassandra at completion makes it immutable by construction — the record leaves the store where it could be edited.

But the handoff is a distributed transaction the design never mentions

The split is right and the seam is a real problem.

Completing a trip means: write it to Cassandra, delete it from MySQL, trigger payment. If the process dies between steps you get a trip in both stores, or neither, or one that is archived but never billed.

There is no distributed transaction across MySQL and Cassandra, so the standard resolutions apply:

Write Cassandra first, then delete from MySQL. A crash between them leaves a duplicate, which is recoverable — reconcile by trip ID. The reverse order risks losing the trip entirely, which is not.

Make the archive write idempotent, keyed by trip ID, so a retry overwrites rather than duplicates.

Emit completion as an event, not a synchronous cascade — which is exactly what Lesson 11's Kafka-based payment flow does. The trip manager publishes TRIP_COMPLETED; archiving and billing are independent consumers that can retry.

Prefer the failure mode you can detect and repair. A duplicate is visible and fixable; a lost trip is silent and unbillable.

Why neither database alone would do

The design poses this and leaves it as an exercise. Both halves are worth stating.

Why not MySQL alone? The volume. Driver locations alone are 750,000 writes per second, and route traces accumulate at roughly 162 GB per day (Lesson 3). A single-primary relational database cannot absorb that, and sharding it well enough would mean rebuilding Cassandra badly. Cassandra's leaderless, write-optimized design — appending to a commit log, no read-before-write — is built for exactly this.

Why not Cassandra alone? Consistency and relationships. An in-progress trip must be strongly consistent across two parties (Lesson 2), and Cassandra's tunable consistency achieves that only by quorum reads and writes, which surrenders the availability advantage that motivated choosing it. It also has no joins and no multi-row transactions, and the trip state machine wants atomic transitions.

The two databases exist because the two requirements are genuinely incompatible. That is the honest form of the answer: not "Cassandra scales and MySQL is consistent," but "we need strong consistency on a small hot set and enormous write throughput on a large cold set, and no single engine is excellent at both."

The Spanner note quietly undercuts the whole argument

Note: Recently, Uber migrated their data storage to Google Cloud Spanner. It provides global transactions, strongly consistent reads, and automatic multisite replication and failover.

Worth engaging with rather than skipping, because it is the counter-argument to the lesson you just learned.

Spanner is a distributed relational database offering strong consistency and horizontal scale simultaneously — the combination the polyglot split existed to work around. If one system can do both, the seam from the previous callout disappears, and with it the reconciliation, the dual-write risk, and the operational cost of running two databases.

The price is real: Spanner achieves it with tightly synchronized clocks (the TrueTime interval discussed in unique ID generation), and cross-region transactions pay a latency floor bounded by that uncertainty.

So the correct framing for an interview is the polyglot split is the right answer given commodity components, and a managed system with atomic clocks changes the calculus. Both answers are defensible; saying which assumption you are designing under is what matters.

Architectural trade-offs are conditional on the available primitives. A "correct" answer from five years ago can be superseded by infrastructure rather than by better reasoning.

The schema

Three things the schema does not have

No timestamps on trips. The Trips table has status and ETA but no requested_at, started_at, or completed_at. Yet Lesson 5's endTrip carries time_elapsed, Lesson 11's payment computes a fare from duration, and Lesson 12's fraud detection looks for inflated trip times. All three need timestamps the schema does not store. The Yelp chapter had the identical gap.

No fare or payment fields. Nothing links a trip to its charge, its authorization token, or its settlement status. Lesson 11's order store presumably holds it, but the join between a trip and its money is unspecified — and that is the join disputes and refunds depend on.

Driver_location stores old and new coordinates but no time. So you can reconstruct a segment but not a speed, which is exactly what Lesson 12's fraud detection needs to distinguish "stuck in traffic" from "driving slowly on purpose."

That last one is the sharpest. The API in Lesson 5 was carefully designed to carry old-and-new so the server could compute trajectories — and the schema drops the one field that makes a trajectory into a velocity.

The useful observation: a driver's location from thirty seconds ago is worthless, so that store can be volatile and fast; a trip record must survive anything. Holding both to the same durability bar would make the firehose needlessly expensive.

Next: staying available when connections drop.