[Bug] unit test input SQL does not escape column names

[kdeggelman]

Is this a new bug in dbt-core?

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

Current Behavior

The CTE that contains the input data seems to follow this format select safe_cast(<value> as <data type>) as <column name>,

For example,

safe_cast('''compact''' as STRING) as group

Expected Behavior

The CTE should escape the column names. For example, (in BigQuery):

safe_cast('''compact''' as STRING) as `group`

Steps To Reproduce

Running with dbt=1.8.8
Registered adapter: bigquery=1.8.3

1. Create a source dataset/table that uses reserved words as a column name:

create or replace table vehicles.cars as (
  select 1 as id, 'compact' as `group`
)

models/src_vehicles.yaml

sources:
  - name: vehicles
    tables:
      - name: cars
        columns:
          - name: id
          - name: group

2. Create a model that utilizes the source:

models/cars_by_group.sql

with cars as (
    select * from {{ source('vehicles', 'cars') }}
),

count_by_group as (
    select
        group,

        count(*) as count,
    from cars
)

select * from count_by_group

4. Create a unit test for the model:

models/_properties.yml

unit_tests:
  - name: test_count_of_cars_by_group
    model: cars_by_group
    given:
      - input: source('vehicles', 'cars')
        rows:
          - { id: 1, group: 'compact' }
          - { id: 2, group: 'compact' }
          - { id: 3, group: 'midsize' }
          - { id: 4, group: 'suv' }
          - { id: 5, group: 'suv' }
          - { id: 6, group: 'suv' }
    expect:
      rows:
        - { group: 'compact', count: 2 }
        - { group: 'midsize', count: 1 }
        - { group: 'suv', count: 3 }

5. Run the test
   dbt test -s test_count_of_cars_by_group

6. Inspect the SQL

select * from (
        with  __dbt__cte__cars as (

-- Fixture for cars
select safe_cast(1 as INT64) as id, safe_cast('''compact''' as STRING) as group
union all
select safe_cast(2 as INT64) as id, safe_cast('''compact''' as STRING) as group
union all
select safe_cast(3 as INT64) as id, safe_cast('''midsize''' as STRING) as group
union all
select safe_cast(4 as INT64) as id, safe_cast('''suv''' as STRING) as group
union all
select safe_cast(5 as INT64) as id, safe_cast('''suv''' as STRING) as group
union all
select safe_cast(6 as INT64) as id, safe_cast('''suv''' as STRING) as group
), cars as (
    select * from __dbt__cte__cars
),

Relevant log output

21:38:54    Runtime Error in unit_test test_count_of_cars_by_group (models/etl/splits_and_stats/splits_and_stats.yml)
  An error occurred during execution of unit test 'test_count_of_cars_by_group'. There may be an error in the unit test definition: check the data types.
   Database Error
    Syntax error: Unexpected keyword GROUP at [20:75]

Environment

- OS: MacOS 15
- Python: 3.11.9
- dbt: 1.8.8
- dbt-bigquery: 1.8.3

Which database adapter are you using with dbt?

bigquery

Additional Context

Might be similar to:

• [Regression] Unit tests generate an sql with wrong CTE on models with alias #10605
• [Bug] Unit test fixture is returning syntax errors for sources with hyphens ('-') in their name #10345

[kdeggelman]

After doing some more digging, I found that this is a duplicate of dbt-labs/dbt-adapters#205