[Bug] Unit test fixture is returning syntax errors for sources with hyphens ('-') in their name

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

In my BigQuery dataset, we are using tables with hyphens ('-') in their name. This usually requires us to use backticks in table name so avoid syntax error.
It appears that when we run unit test, dbt is creating fixture with CTE and names it after table name, which creates an issue if table has hyphen in its name.

### Expected Behavior

Unit test should wrap CTE name with backticks to make it work, or alter the way how it names it to avoid such issue.

### Steps To Reproduce

1. Create dataset, e.g. named temp_dataset
2. Create table and fill with data
```
CREATE TABLE IF NOT EXISTS `gcp-project.temp_dataset.source-table`
(
input_text_field STRING
)
;
INSERT INTO `gcp-project.temp_dataset.source-table` VALUES ('1')
;
INSERT INTO `gcp-project.temp_dataset.source-table` VALUES ('2')
```
3. Create test_model.sql
```
{{config(
materialized='table'
)}}

with source_data as (
select
cast(input_text_field as int) as int_field
from
{{source('test', 'source-table')}}
)

select * from source_data
```
4. Create test_model.yml
```
unit_tests:
- name: test_model_general
model: test_model
given:
- input: source('test', 'source-table')
rows:
- {
input_text_field: 3
}
expect:
rows:
- {
int_field: 3
}
```
5. Execute the test
`dbt test --select test_model_general`

### Relevant log output

```shell
❯ dbt test --select test_model_general
15:03:33 Running with dbt=1.8.2
15:03:33 Registered adapter: bigquery=1.8.1
15:03:34 Found 114 models, 635 data tests, 2 sources, 1253 macros, 2 unit tests
15:03:34
15:03:35 Concurrency: 5 threads (target='dev')
15:03:35
15:03:35 1 of 1 START unit_test test_model::test_model_general .......................... [RUN]
15:03:37 BigQuery adapter: ...link...
15:03:37 1 of 1 ERROR test_model::test_model_general .................................... [ERROR in 1.96s]
15:03:37
15:03:37 Finished running 1 unit test in 0 hours 0 minutes and 2.89 seconds (2.89s).
15:03:37
15:03:37 Completed with 1 error and 0 warnings:
15:03:37
15:03:37 Runtime Error in unit_test test_model_general (models/test_model.yml)
An error occurred during execution of unit test 'test_model_general'. There may be an error in the unit test definition: check the data types.
Database Error
Syntax error: Expected keyword AS but got "-" at [19:25]
15:03:37
15:03:37 Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1
```

### Environment

```markdown
- OS: Ubuntu 22.04.3 LTS (WSL)
- Python: 3.11.9
- dbt:
Core:
- installed: 1.8.2
- latest: 1.8.2 - Up to date!
Plugins:
- bigquery: 1.8.1 - Up to date!
```

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

bigquery

### Additional Context

_No response_