tl;dr: Rosetta is sticking around through macOS 27. After that, the normal Rosetta will be removed from macOS. but a subset of Rosetta will remain to support certain older unmaintained games.