[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
Create dataset, e.g. named temp_dataset
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')
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
❯ 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
- 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!
Is this a new bug in dbt-core?
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.![Screenshot 2024-06-20 170735](https://github.com/dbt-labs/dbt-core/assets/26751805/5c551429-46ca-4a2c-b4e4-c40a75b7a214)
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.![Screenshot 2024-06-20 170844](https://github.com/dbt-labs/dbt-core/assets/26751805/3abf2902-d04c-4a8b-a5ba-4a0cee22256e)
Steps To Reproduce
with source_data as ( select cast(input_text_field as int) as int_field from {{source('test', 'source-table')}} )
select * from source_data
unit_tests:
Relevant log output
Environment
Which database adapter are you using with dbt?
bigquery
Additional Context
No response