Edit: adding some context. I am planning to setup a dev machine that I will connect to remotely and would like to babysit very little while having stable and fresh packages. In the Ubuntu world we would go to an LTS release but on the RPM/Dnf world is there any other distro apart from CentOS Stream? And also is CentOS Stream comparable to an LTS release at all considering that they do not have release number?
Wanting both stable and fresh packages is unfortunately somewhat difficult in my experience. I think the primary choice within the Fedora ecosystem is if you want to have fresh packages (Fedora) or if you prefer a slower update cycle and more stable packages (RHEL/Alma/Rocky). In the second case you can also choose if you wish to pay Red Hat for support (RHEL) or not (Alma or Rocky).
One thing that’s quite different in RHEL vs Ubuntu/Debian ist that it gets minor releases that include substantial new features. For example you’ll get new compilers, python versions, drivers, … CentOS Stream gets those slightly ahead of RHEL/Alma/Rocky (a cynical person might say that CentOS Stream is a rolling beta for RHEL). But, IMHO that’s not really a strong reason to use CentOS Stream.
If you’d go with an Ubuntu LTS release, then I’d look into RHEL/Alma/Rocky.