How to unit test case sensitive column names

[DONSA]

Hi, I can’t run unit tests when the model has case-sensitive column names. Is this a known issue?
I’ve tried the unit tests using CSV and SQL formats without success. The only way it works is if I rename the model column names from CountryCode and CountryName to country_code and country_name.

Input reference country_codes table:

country_code,country_name
US,United States
CA,Canada
GB,United Kingdom

The model transformation:

with codes as (
    select * from {{ ref('country_codes') }}
)

select
    codes.country_code as "CountryCode",
    codes.country_name as "CountryName"
from codes
limit 1

The example model transformation output:

CountryCode,CountryName
US,United States

The unit test that doesn’t support case-sensitive column names:

unit_tests:
  - name: test_is_valid_country
    description: "Check if the country is valid"
    model: example
    given:
      - input: ref('country_codes')
        rows:
          - {country_code: US, country_name: United States}
    expect:
      rows:
        - {"CountryCode": US, "CountryName": United States}

The error I get:

Runtime Error in unit_test test_is_valid_country (models/_models.yml)
An error occurred during execution of unit test 'test_is_valid_country'. There may be an error in the unit test definition: check the data types.
   Database Error
    column "countrycode" does not exist
    LINE 5:     CountryCode,CountryName, 'actual' as "actual_or_expected...
                ^
    HINT:  Perhaps you meant to reference the column "_dbt_internal_unit_test_actual.CountryCode".

[pgoslatara]

What version of dbt are you running? What adapter are you running?

I copied your code and I experienced no issues, the unit test ran as desired:

$ dbt build -s example
09:22:41  Running with dbt=1.8.5
09:22:41  Registered adapter: duckdb=1.8.3
09:22:41  Found 27 models, 58 data tests, 4 seeds, 10 sources, 1 exposure, 919 macros, 1 unit test
09:22:41  
09:22:42  Concurrency: 8 threads (target='dev')
09:22:42  
09:22:42  1 of 2 START unit_test example::test_is_valid_country .......................... [RUN]
09:22:42  1 of 2 PASS example::test_is_valid_country ..................................... [PASS in 0.18s]
09:22:42  2 of 2 START sql view model main.example ....................................... [RUN]
09:22:42  2 of 2 OK created sql view model main.example .................................. [OK in 0.10s]
09:22:42  
09:22:42  Finished running 1 unit test, 1 view model in 0 hours 0 minutes and 0.56 seconds (0.56s).
09:22:42  
09:22:42  Completed successfully
09:22:42  
09:22:42  Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

[DONSA]

venv ❯ dbt build -s case_sensitive
14:58:19  Running with dbt=1.8.6
14:58:19  Registered adapter: postgres=1.8.2
14:58:19  Found 3 models, 1 seed, 4 data tests, 417 macros, 1 unit test
14:58:19
14:58:19  Concurrency: 1 threads (target='dev')
14:58:19
14:58:19  1 of 2 START unit_test case_sensitive::test_is_valid_country ................... [RUN]
14:58:19  1 of 2 ERROR case_sensitive::test_is_valid_country ............................. [ERROR in 0.10s]
14:58:19  2 of 2 SKIP relation models.case_sensitive ..................................... [SKIP]
14:58:19
14:58:19  Finished running 1 unit test, 1 table model in 0 hours 0 minutes and 0.25 seconds (0.25s).
14:58:19
14:58:19  Completed with 1 error and 0 warnings:
14:58:19
14:58:19    Runtime Error in unit_test test_is_valid_country (models/_models.yml)
  An error occurred during execution of unit test 'test_is_valid_country'. There may be an error in the unit test definition: check the data types.
   Database Error
    column "countrycode" does not exist
    LINE 5:     CountryCode,CountryName, 'actual' as "actual_or_expected...
                ^
    HINT:  Perhaps you meant to reference the column "_dbt_internal_unit_test_actual.CountryCode".
14:58:19
14:58:19  Done. PASS=0 WARN=0 ERROR=1 SKIP=1 TOTAL=2

[pritpal21]

I am facing similar issue with most recent version of dbt with Snowflake adapter
Core:

• installed: 1.8.6
• latest: 1.8.6 - Up to date!

Plugins:

• snowflake: 1.8.3 - Up to date!

[megetron3]

currently it's a known bug in the dbt-adapters package