[Bug] Unit Tests with duplicate names don't play nicely with `--threads`

### 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

We accidentally ended up with two unit tests against different models with the same name. When increasing the thread count, we noticed errors like:
`Destination deleted/expired during operation: delfina-gaia-prod:mayo_export.test_lab_icd_prioritization__dbt_tmp`

### Expected Behavior

I think either are okay:
Fail on compile with duplicate test names
Restructure temp outputs so they don't collide

### Steps To Reproduce

1. Have two unit tests with the same name
2. Run with a high thread count on bigquery to ensure they run simultaenously

### Relevant log output

```shell

```

### Environment

```markdown
- OS: GCP cloud run
- Python: 3.11
- dbt: 1.11.7
```

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

bigquery

### Additional Context

_No response_