[Bug] Data tests can have duplicated names

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

Discovered when researching https://github.com/dbt-labs/dbt-core/issues/13894

### Expected Behavior

It should not allow duplicated test names.

### Steps To Reproduce

https://github.com/dbt-labs/dbt-core/issues/13894#issuecomment-3408278964

### Reprex
You can see how this is a bug in Core with the following example:

Create these files:

`models/my_model.sql`

```sql
select
1 as col_a,
null as col_b
```

`models/_properties.yml`

```yaml
models:
- name: my_model
columns:
- name: col_a
data_tests:
- not_null:
name: IS_NOT_NULL
- name: col_b
data_tests:
- not_null:
name: IS_NOT_NULL
```

Run these commands:

```sql
dbt-core run -s my_model
dbt-core test -s IS_NOT_NULL
```

Get this output:

```
$ dbt-core test -s IS_NOT_NULL
21:01:21 Running with dbt=1.10.9
21:01:22 Registered adapter: snowflake=1.10.0
21:01:22 Found 1 model, 2 data tests, 477 macros
21:01:22
21:01:22 Concurrency: 4 threads (target='snowflake')
21:01:22
21:01:22 1 of 2 START test IS_NOT_NULL .................................................. [RUN]
21:01:22 2 of 2 START test IS_NOT_NULL .................................................. [RUN]
21:01:23 1 of 2 FAIL 1 IS_NOT_NULL ...................................................... [FAIL 1 in 0.24s]
21:01:24 2 of 2 PASS IS_NOT_NULL ........................................................ [PASS in 1.22s]
21:01:24
21:01:24 Finished running 2 data tests in 0 hours 0 minutes and 2.22 seconds (2.22s).
21:01:24
21:01:24 Completed with 1 error, 0 partial successes, and 0 warnings:
21:01:24
21:01:24 Failure in test IS_NOT_NULL (models/schema.yml)
21:01:24 Got 1 result, configured to fail if != 0
21:01:24
21:01:24 compiled code at target/compiled/fusion_reprex/models/schema.yml/IS_NOT_NULL.sql
21:01:24
21:01:24 Done. PASS=1 WARN=0 ERROR=1 SKIP=0 NO-OP=0 TOTAL=2
```

Open this file and see this content:

```
select col_a
from analytics.dbt_dbeatty.my_model
where col_a is null
```

Note that the `target/compiled/fusion_reprex/models/schema.yml/` directory only has **one** test file instead of two, and it says `col_a` instead of `col_b` (this is the bug).

```
$ tree target/compiled/fusion_reprex/models/schema.yml/

target/compiled/fusion_reprex/models/schema.yml/
└── IS_NOT_NULL.sql

1 directory, 1 file
```

### Relevant log output

```shell

```

### Environment

```markdown
- OS:
- Python:
- dbt:
```

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

_No response_

### Additional Context

_No response_