Looking for advice on an unusual workflow

Background: My team does a lot of short-term data analysis projects. We have a Snowflake db with a lot of 'base' tables from other departments or organisations, pretty much all poorly documented and with questionable data quality. The underlying data often changes for months after the fact, due to error corrections and updates. We have built a core data model on top of this, using business logic that we learn the hard way through a lot of interaction with SMEs, which is frequently updated after we complete a project to include any updated or new and reusable logic. A project pretty typically i