They don't have the same guarantees as a real rolling release distro. My experience of SID is things randomly breaking and having to be very careful whenever you update. And because it is not a "production" intended rolling release, they don't have the same guarantees for security fixes either - they can be quite unhurried.
Many DD are running unstable, so it should be non-breaking enough for most people. The most common breakages are file conflicts. It is a shame we didn't fix such issues yet, as it is a bit difficult to fix the problem, but having testing as an additional source and rolling back the problematic package should fix the issue. I don't remember any other kind of breakages (other than upstream regressions, but this is a rolling release) for the past five years. If you tried unstable 10 years ago, this is now vastly different.
On the other hand, I remember Arch transition from readline 6 to 7 making the system unbootable (2016) if you did upgrade at the wrong moment. I don't know if package containing libraries are now versioned to avoid such issues.
I think the main difference you get by using Arch is AUR (we don't have that in Debian) and, I think, more fresh packages (mainly because it easier to package for Arch than for Debian).
> They don't have the same guarantees as a real rolling release distro.
I can guarantee my distro will bring world peace, help you lose ten pounds and get 10% better gas mileage.
Unlike rolling release distros, Debian doesn't try to pretend that rolling releases are as reliable. It's exactly what it says on the tin. Both are probably equal in terms of reliability, but Debian is actually honest about what that reliability level is.
IME Sid mostly causes issues if you try to cherry-pick programs or libraries from it, mostly because you get into dependency hell. I have successfully done it with aptitude’s (not apt) package solver, but it’s a pain. If you run full Sid for everything, ironically it’s more stable.
That said, on my servers I just run stable and then compile anything I want that differs, and make sure it doesn’t clobber the system’s version. Like altinstall for newer Python versions. I’ve yet to have an issue this way.
> IME Sid mostly causes issues if you try to cherry-pick programs or libraries from it
Instead of doing this, create a Debian Stable (or Testing, if that's what you're running) repository on OBS¹. Fork the packages you want from Debian Unstable into your repo, and let them build against your preferred Debian release. If some of them need newer deps, pull in their deps, too. Add your repository to your system and upgrade that way.
Or if you're not that attached to only using DEBs, just use Nix or Guix to install newer software.
On arch this is all much less hassle.