Handling atomic transactions or ACID on a single computer is a completely different problem from dealing with them across multiple, and still a different problem from dealing with them across datacenters. I'd argue that the size of your data is the first question you need to ask, only then you can figure out if you need/desire these features.
> once your data does not fit on one computer/harddrive you need to start figuring out a way for multiple to coordinate
Going from one computer to two generally more than doubles the complexity of the system. But when done right, going from two computers to two hundred can be easy. However, going from two hundred (in a single datacenter) to ~two thousand (across multiple datacenters) brings its own unique set of challenges.
Picking the database for an app is one of the biggest tech decisions any team can make, certainly more important than the underlying OS, and even potentially more important than choosing a predominant programming language. Even when starting a tiny startup with no initial customers, considering how big about the absolute max you think you your data can grow, and picking a database that can grow with you can save you huge headaches down the road.
Case Study: I started a company on Python 2.7 (fine, but not enlightened decision), a fork of Django that supported NoSQL (bad decision), and an underlying NoSQL database that scaled with no configuration (great decision). In the first year, the database had around a gigabyte of data, so NoSQL was definitely overkill. But now, it's over 100 Terabytes, with auto replication making it much larger than that. Changing the Django middleware or even the Python language to 3.x will be difficult, but not impossible. Moving the database is far more terrifying, a non-starter.
I used Google's NoSQL datastore database. It's generally worked well for me, and has scaled from 1GB to 100TB incredibly well, it feels like magic. But I made the decision to go with it years ago. If I were starting from scratch today, I'd want to do a lot more research before endorsing it.
I agree that the possible second biggest (if not the first) differentiator is size though -- despite the amount of choices out there, once you start narrowing down answering those questions, many options fall away very quickly.
I'd also add on that the only real way to scale a database is to shard. There are a bunch of tricks you can do to improve throughput and those are always welcome, and sometimes I'll see articles that proclaim "we just changed to a system with 256GB of RAM to fix our scaling problem!" -- but these improvements don't get past the basic premise that once your data does not fit on one computer/harddrive you need to start figuring out a way for multiple to coordinate. You need to shard, and you need to organize your data in such a way that it can shard, then you need to start worrying about data access patterns. There's a lot of space before something like this is necessary (you really do have to have a lot of data for it to not fit on one machine these days), but I think people don't see this chain coming fast/often enough.