I work at one of the decent size tech company and we are split between cloud and on prem. From our experience you have to inform AWS/GCP in advance (sometime way early) if you are looking to meaningfully increase capacity in zone/region.
Sure, auto scaling few hundreds of hosts may be possible but people who run a service which needs few hundreds of hosts run it directly on AWS, they will run it some kind of scheduler+resource manager which will have some kind of operational buffer anyway (as in you would already have those hosts so cloud elasticity is not a factor here).
Are managed data stores that attractive? You can pay for on-prem management.
What workloads are in the cloud versus on-prem?
We actually recommend not using custom cloud providers Databases or any other value added services.
Why not completely either way (on prem vs cloud) is something that happened way before I joined the group but I think the main reason is to have a tactical edge in the long run such that we avoid lock in. I guess in some ways it helps us negotiate pricing better.
Imagine moving a certain workload from GCP region to an AWS region as part of a failover drill.
This times a million. I think SQS standard queues are probably the only thing that IME actually fulfill that promise.
About the up-front investment - most hi-tech companies are a massive initial up-front (or nearly-up-front) investment.
Probably a whole lot less.
At larger scale - I would guess it's the same thing. If an organization needs more than a rack during peak use, it can probably benefit from setting up its own infrastructure. Only in the uncommon case of short extreme peak use and almost no use most of the time does such elasticity make a could solution attractive. IMHO.