[Bug] generic test are not working with fields with special characters

[gekas93]

Is this a new bug?

• [X] I believe this is a new bug
• [X] I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

When I add some tests to my yml file in a field with special characters, I receive a sintax error.

Ex:

- name: uuid-part1
  data_tests:
    - unique
    - not_null

Response: column uuid does not exists in base_autoportal_reference_data

Expected Behavior

Tests executed perfectly.

Steps To Reproduce

1. You need to have your model with some fields with special characters in the column names.
2. Create your tests in your yml file in some of the column with the special characters.
3. Run the tests.

Relevant log output

No response

Environment

- OS: Mac
- Python:3.9.18
- dbt-core:1.8.1
- dbt-redshift:1.8.1

Additional Context

It's because the column names are not quoted in the tests.

At the moment, I have overwritten the macro tests with something like this:

{% macro redshift__test_unique(model, column_name) %}

select
    "{{ column_name }}" as unique_field,
    count(*) as n_records

from {{ model }}
where "{{ column_name }}" is not null
group by "{{ column_name }}"
having count(*) > 1

{% endmacro %}

[amychen1776]

@gekas93 would you be able to share what are the special characters in your column? Also what happens if you quote them in your dbt model?

[gekas93]

Thanks for your quick answer @amychen1776 . Sure. I add some examples:

• UUID-field1
• part number
• part.number

I have a select * in those queries with the issues but I have chaged the query for having only two quoted columns, like this:

select "UUID-field1", "UUID-field2"
from {{ source('xxxx', 'xxxxx') }}

And this is the compiled code of the unique test:

select
      count(*) as failures,
      count(*) != 0 as should_warn,
      count(*) != 0 as should_error
    from (

select
    UUID-field1as unique_field,
    count(*) as n_records

from "xxxxx"."xxxxx"."xxxxxxxx"
where UUID-field1 is not null
group by UUID-field1
having count(*) > 1

    ) dbt_internal_test

And the issue: column "uuid" does not exist in xxxxx.

So quoting them it's the same as not quoting them.

[amychen1776]

@gekas93 do you have quoting configured in any of your yml files like project.yml? https://docs.getdbt.com/reference/project-configs/quoting

[gekas93]

@amychen1776 No, we have the default behavior (quoting = True) and it's quoting the database, identifier and schema but not the fields.