How critical is encryption-at-rest for public cloud hosted systems?

Your threat model is focused on external parties breaking in. But the threats are broader than that.

Low-level hardware backups, VM snapshots, and disposed hardware can all contain data. And because these things tend to be seen to have lower risks, they are often mishandled. So, it's not a "Mission Impossible" style of threat that is likely. It's the "eh, the drive is old, just toss it" style of threat that's the problem. Even for large cloud providers.

And because, as you say, it's cheap and easy to implement encryption-at-rest, to not implement it is its own cause for concern and follow up questions.

Human factor issues, like password policies and secure coding practices, are also very important, but difficult to assure, insure, and be consistent. So, technical controls tend to be the focus, regardless of the overall priority.

This is not to challenge the value of encryption at rest, it is very cheap to access, so there is no reason not to enable it, but where does it sit in terms of priorities?

It is cheap when you use the lower layers to do the encryption (disk for instance). It gets expensive (in terms of architectural and code design) when you want to encrypt data at rest by the application itself (especially for multi-user accesses).

Lower-level encryption is good against physical theft and media mishandling but from the perspective of the application, it accesses plaintext data. This data is also available as plaintext to the cloud company administrators (or not - it all depends on the features of the storage service)

If you have an application that takes care of its own encryption and does not rely on the cloud provider at all you can go for more "hostile" environments (where you are not sure the administrative operations are reliable). I am not versed well enough in memory-based attacks to know whether it is feasible to completely protect the credential there but I suppose that this is not possible when everyone is against you.

In summary: the more you manage your encryption, the more independent you are, and provided that encryption is done right - the more secure your data is against direct snooping.

so surely public cloud providers would have similar controls in place?

I was discussing recently with one of the providers you mentioned. To the question "will you log the access of your administrators to our data?" the answer was "no" (plus a lot of chit-chat on how they would never access the data without authorization yada yada yada - the point was that they do not know who accessed what in the first place).

It was not even that the logs can be manipulated by administrators, or extreme demands are made we would not know about (based on governmental requests that must keep from us), or other similar scenarios - it was just that there was no logging full stop.