[CT-3459] [Feature] Add cleanup methods to various base tests in core that are known to create leftover schemas from ci/cd in adapters

[McKnight-42]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Going to clear out our test account used for dbt-redshift see that several tests defined in dbt-core are leaving hanging artifacts at a rate suggesting their not properly being dropped see: dbt-labs/dbt-redshift#675

Describe alternatives you've considered

We can go look at the tests and evaluate what part of the default schema Is being changed and create cleanup methods to drop that changed schema. then install the connecting pr branch to this issue into local adapter runs and run tests locally to make sure the caught tests are not leaving any more schemas and try to catch follow-up ones that may not be nuisance artifacts

Who will this benefit?

our testing alerts can hopefully start to be more clean and pointing to real failures and stem flow of slowly filling up our ci/cd databases till no tests are allowed to pass

Are you interested in contributing this feature?

yes

Anything else?

No response

[mikealfare]

@McKnight-42 Should we verify this is working somehow (hence re-open until verified)? Or are you comfortable with this as-is given that it only impacts tests (hence move to Done status)?