I used to work in an environment not dissimilar from what's described above. The uni has some hardware details here: https://in.nau.edu/hpc/details/. Many of my answers would be similar, although the presence of physics, astronomy, and a few other areas motivates GPUs these days apparently. I was doing genomics workloads in the 900-1200gb RAM range with 30+ cores.

There's a pretty interesting NSF-wide project for managing clusters in a more commoditized way as part of https://www.xsede.org. You might describe it as a "private heterogeneous almost-cloud" in its goals? That might be saying a bit much.

Density, latency, storage throughput, etc. were in favor of DIY (plus the pricey professionals to run it) rather than cloud offerings. When I was there (2016) I did some basic math for being able to use a cloud provider for some of our lighter workloads when the local cluster was loaded down. Astronomical without a contract, which is quite the thing to set up, etc.

Worth noting that while they do alright, NAU (first link) is hardly a top-tier university with bleeding edge technical requirements.