SQL generation is quite good, 90% of the problem is data quality.

The best thing you can do for your data agent is build a clean frontroom around your specific desired use cases -- with very clear documentation and obvious idiomatic join patterns built on clear marts. It is hard work, but I've been doing this a while and this is the only way.

A full top down rebuild is rarely feasible and can take years. What I suggest is focusing on rebuilding your base layers and introducing a versioned schema/model approach (to isolate breaking changes). Even with the data/compute redundancy it introduces, it is so much easier to move the sale report from customers_v1 to customers_v2 than it is to evaluate what happens when you remove the salesforce id as primary key from customers into all the downstream dependencies.