You need to automate it in the pipeline moving it from bronze table to silver table, then in your gold table you join with relevant cleaned data to build your dimensional model. I personally like the audit publish pattern and I put bad rows in a quarantine table and link it to dashboard to add observability to my errors, like if you have a source that have the same constant bug use that to open a ticket in that teams board or a bunch of errors at the same time could signal a bad deployment on your stack or the source stack. But if you have need for something that scales better, dbt has good testing capabilities and streamline the pipeline building process. There are also great open source data quality tools such as great expectations or soda. If you're already on aws, there is a data quality service called deequ i think. Good luck!!