KDE's megarelease is now just one month away. At the end of February 2024 KDE will publish Plasma 6, Frameworks 6, and a whole new set of applications in a special edition of KDE Gear all in one go.
Beryl was a fork of Compiz, and then was merged back later on. The desktop cube was basically Compiz' first big show off feature, along with the wobbly windows.
Noob question here: when KDE 6 is released, when would I then see it as available in my Ubuntu 22 / 23 app store for download? Right away or years later?
Usually major desktop environment updates are saved for new major OS updates. So you'd probably have to wait until the next major Ubuntu release. If you're on LTS, that may be years. If you're on the latest release, it will probably be less than a year.
We have the release candidates available (after jumping through some hoops) in Gentoo ATM, when 6.0 comes out they'll be generally available with minimal effort, and eventually be deployed to our stable users with no need for changes on their part