over a path of effort for the Clouds team
It seems to me that the Cloud team is putting in all of the effort they can. I'm not sure where they would find more time and energy to implement a better solution. I imagine any better solution wouldn't be a matter of a few extra hours, but rather finding thousands and thousands of hours to build something bespoke.
This is painful. I think you raised some really good points about cross-joins with Central Auth and Commons as those are *designed* to be cross-referenced from other wikis. But ultimately, if there's no reasonable way to do it in the software (Maria DB) we have available, implementing our own solution would take several orders of magnitude more time and then we'd need even more time to maintain it.
The real source of pain here is the success of Wikimedia projects. Once things get so big that you can't really use one bigass server to solve the problem anymore, scaling involves taking on new complexities in downstream code. In my experience, everyone is struggling with the limits that the "big data" age has put on our ability to query and analyze. No matter what, this type of transition and likely the ones that will follow, will cause a big burden on tool developers. I just don't see a good way around that even though it is a very bad situation and many volunteers won't be able to handle the burden.
I guess all I'm trying to say is, don't lay this on the Cloud team being lazy. They aren't. They are one of the most volunteer focused teams at the Wikimedia Foundation and they do quite a lot to support us with the little resources they have. Your frustrations are perfectly valid though. This is a very frustrating situation.