[CT-3478] [Bug] Editing profiles as part of tests is not thread-safe

### Is this a new bug in dbt-core?

- [X] I believe this is a new bug in dbt-core
- [X] I have searched the existing issues, and I could not find an existing issue for this bug

### Current Behavior

When running tests in parallel that modify the profile, it is possible for invocations of dbt_run to grab the wrong profile, even across different test classes.

In dbt/tests/fixtures/project.py, you have:

```
@pytest.fixture(scope="class")
def profiles_yml(profiles_root, dbt_profile_data):
os.environ["DBT_PROFILES_DIR"] = str(profiles_root) # <- this is not safe, as it is global
write_file(yaml.safe_dump(dbt_profile_data), profiles_root, "profiles.yml")
yield dbt_profile_data
del os.environ["DBT_PROFILES_DIR"] # <- this is not safe, as it is global
```

### Expected Behavior

When you modify a profile as part of a test fixture, the modified profile is read as part of the test.

### Steps To Reproduce

This is hard to reproduce consistently, but basically:
1. Have multiple tests that run at the same time that modify/rely on a config in the profile
2. Run them in parallel
3. Observe sporadic failure

### Relevant log output

_No response_

### Environment

```markdown
- OS: Mac
- Python: Multiple
- dbt: 1.7.3
```

### Which database adapter are you using with dbt?

other (mention it in "Additional Context")

### Additional Context

Trying to write tests for dbt-databricks for warehouse-per-model.