There's no magic. you need a core team that pivots from writing code at O(n) cost enterprise wide to more or less amortized O(1) where n is the amount of work required to process a new data stream - ie having to write code once per stream vs once for a standardized stream that gets reused. With only datamesh I don't think it's going to work but with standardized tools that allow your teams to write transformations and code as data then every team effectively gets access to a self-service data warehouse with only access to pre-approved happy paths that can be automatically monitored for the most part. That's where you gain in efficiency and can let your BI teams focus on BI and not boilerplate code, infrastructure, conformity, etc.

Yes, its similar path that I am taking (while leading BI in my org.) Having first sights of self-service from analysis perspective is super easy thanks to tools like metabase.

For bringing data in, thats completely different story, especially in non-tech organizations. The gap between how power user from specific department and somebody from my team brings and transforms data is still too big and somehow hard to enforce (following naming conventions, keeping same data formats for same columns, lowercasing certain columns, so joins are done correctly...). They usually have their "playground schemas" they use, but its very far from saying that they "own" data quality there.

A data mesh approach probably wouldn't work in the sort of organization you describe.

IMO - To make it work you need a consistent taxonomy or way of translating from a particular domain to some sort of interchange format.

If you have that then a set of centralized tools can pull from the separate domains using a core set of protocols to produce reports etc.