In my opinion the use case is when your data no longer fits on a single, potentially very large, server. Automatic distribution and replication and rebalancing of data is a tricky problem and something like Cassandra handles those very well.

Or Cockroach DB or similar, if you still need relational capabilities.