I didn't get it either. I thought it just looks like SQL with some complicated additions. Then I did some work with it and the flow got way better. We could work a lot more incrementally and as a larger team rather than as individuals tripping all over each other. Maybe there is a better way doing something else, but this clicked with the team.
Not sure I get your cost centre black hole comment, but for the most part storage is so cheap that it isn't a concern. ELT has made it pretty easy for us to do less analysis and work on the data gathering side, which is low value, and do more on the modeling side which is higher value. Further, analysts can play with the raw data and find what they want and if they like it they will give it to us to model in a formal way. No need to work with an ETL team and application team to bring the data over. In ye olden times, this was overthought and created friction - "If you don't know what you're going to exactly do with the data how am I going to know what to bring in and how to land it"."But how am I to know what to do with the data if I can't query it and see what opportunities there are."
On the unwieldy part, sounds like you've had a bad experience. Ours has been the opposite. Really easy to setup and use. Easy incremental step for the team to get their heads around. Integration with git and snowflake was really easy. I'm still a bit worried that something is going to get really messed up with the declarative approach that dbt has, but we've not seen any issues yet.
I've been curious as well about what more can be done to make the development lifecycle better. Most of the other "big data" approaches that I've worked with seemed to make it worse not better. A lot more complexity, not thinking in sets, layers and layers of nothing I care about that can go wrong. I can't believe I'm still working with SQL.