[CT-3400] Document testing best practices in CONTRIBUTING.md

[MichelleArk]

Examples:

• one test method per class
• additional, more precise assertion after using expect_pass=False with run_dbt
• avoid asserting on specific things, not like '1.0' in message

[McKnight-42]

some notes from things we've seen in adapter land pulling in tests.

our general teardown method defined for testing doesn't catch any default value schemas so any changes currently to schema, database, or the identifier from initial test initialization are not caught and require clean_up methods added to the test see prs like #9195 for example where we have some changes to alt databases or modify the test_schema name.

not doing this leads to hanging artifacts that slowly build up and and causes the test databases to reach limits (typically around 10K for us) and cause all tests to fail. till items are dropped.