don't require double quotes around numbers in unit testing yml

[graciegoheen]

Is this a new bug in dbt-snowflake?

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

Current Behavior

This unit test results in a SQL compilation error:

unit_tests:
  - name: test_valid_email_address # this is the unique name of the test
    description: my favorite unit test
    model: dim_wizards # name of the model I'm unit testing
    given: # the mock data for your inputs
      - input: ref('stg_wizards')
        rows:
          - {WIZARD_ID: 1, EMAIL: cool@example.com,     EMAIL_TOP_LEVEL_DOMAIN: example.com}
          - {WIZARD_ID: 2, EMAIL: cool@unknown.com,     EMAIL_TOP_LEVEL_DOMAIN: unknown.com}
          - {WIZARD_ID: 3, EMAIL: badgmail.com,         EMAIL_TOP_LEVEL_DOMAIN: gmail.com}
          - {WIZARD_ID: 4, EMAIL: missingdot@gmailcom,  EMAIL_TOP_LEVEL_DOMAIN: gmail.com}
      - input: ref('top_level_email_domains')
        rows:
          - {TLD: example.com}
          - {TLD: gmail.com}
      - input: ref('stg_worlds')
        rows: []
    expect: # the expected output given the inputs above
      rows:
        - {WIZARD_ID: 1, IS_VALID_EMAIL_ADDRESS: true}
        - {WIZARD_ID: 2, IS_VALID_EMAIL_ADDRESS: false}
        - {WIZARD_ID: 3, IS_VALID_EMAIL_ADDRESS: false}
        - {WIZARD_ID: 4, IS_VALID_EMAIL_ADDRESS: false}

22:48:20 Using snowflake connection "unit_test.coalesce_ci_demo_2023.dim_wizards.test_valid_email_address"
22:48:20 On unit_test.coalesce_ci_demo_2023.dim_wizards.test_valid_email_address: create or replace temporary table DEVELOPMENT.dbt_ggoheen.test_valid_email_address__dbt_tmp
         as
        (select * from (
        with  __dbt__cte__stg_wizards as (

-- Fixture for stg_wizards
select 
    try_cast(1 as NUMBER)
 AS WIZARD_ID, try_cast(null as VARCHAR) AS WIZARD_NAME, 
    try_cast('cool@example.com' as VARCHAR)
 AS EMAIL, 
    try_cast('example.com' as VARCHAR)
 AS EMAIL_TOP_LEVEL_DOMAIN, try_cast(null as VARCHAR) AS PHONE_NUMBER, try_cast(null as NUMBER) AS WORLD_ID
union all
select 
    try_cast(2 as NUMBER)
 AS WIZARD_ID, try_cast(null as VARCHAR) AS WIZARD_NAME, 
    try_cast('cool@unknown.com' as VARCHAR)
 AS EMAIL, 
    try_cast('unknown.com' as VARCHAR)
 AS EMAIL_TOP_LEVEL_DOMAIN, try_cast(null as VARCHAR) AS PHONE_NUMBER, try_cast(null as NUMBER) AS WORLD_ID
union all
select 
    try_cast(3 as NUMBER)
 AS WIZARD_ID, try_cast(null as VARCHAR) AS WIZARD_NAME, 
    try_cast('badgmail.com' as VARCHAR)
 AS EMAIL, 
    try_cast('gmail.com' as VARCHAR)
 AS EMAIL_TOP_LEVEL_DOMAIN, try_cast(null as VARCHAR) AS PHONE_NUMBER, try_cast(null as NUMBER) AS WORLD_ID
union all
select 
    try_cast(4 as NUMBER)
 AS WIZARD_ID, try_cast(null as VARCHAR) AS WIZARD_NAME, 
    try_cast('missingdot@gmailcom' as VARCHAR)
 AS EMAIL, 
    try_cast('gmail.com' as VARCHAR)
 AS EMAIL_TOP_LEVEL_DOMAIN, try_cast(null as VARCHAR) AS PHONE_NUMBER, try_cast(null as NUMBER) AS WORLD_ID
),  __dbt__cte__stg_worlds as (

-- Fixture for stg_worlds
select try_cast(null as NUMBER) AS WORLD_ID, try_cast(null as VARCHAR) AS WORLD_NAME
    limit 0
),  __dbt__cte__top_level_email_domains as (

-- Fixture for top_level_email_domains
select 
    try_cast('example.com' as VARCHAR)
 AS TLD
union all
select 
    try_cast('gmail.com' as VARCHAR)
 AS TLD
), wizards as (

    select * from __dbt__cte__stg_wizards

),

worlds as (

    select * from __dbt__cte__stg_worlds

),

accepted_email_domains as (

    select * from __dbt__cte__top_level_email_domains

),

check_valid_emails as (

    select  
        wizards.wizard_id,
        wizards.wizard_name,
        wizards.email,
        wizards.phone_number,
        wizards.world_id,

        coalesce (regexp_like(
            wizards.email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
        )
        = true
        and accepted_email_domains.tld is not null,
        false) as is_valid_email_address

    from wizards
    left join accepted_email_domains
        on wizards.email_top_level_domain = lower(accepted_email_domains.tld)

)

select
    check_valid_emails.wizard_id,
    check_valid_emails.wizard_name,
    check_valid_emails.email,
    check_valid_emails.is_valid_email_address,
    check_valid_emails.phone_number,
    worlds.world_name
from check_valid_emails
left join worlds
    on check_valid_emails.world_id = worlds.world_id
    ) as __dbt_sbq
    where false
    limit 0

        );
22:48:20 Snowflake adapter: Snowflake query id: 01b1efd8-0804-0b33-000d-37832a1438f6
22:48:20 Snowflake adapter: Snowflake error: 001065 (22023): SQL compilation error:
Function TRY_CAST cannot be used with arguments of types NUMBER(1,0) and NUMBER(38,0)
22:48:20 Timing info for unit_test.coalesce_ci_demo_2023.dim_wizards.test_valid_email_address (execute): 22:48:19.451516 => 22:48:20.639856
22:48:20 On unit_test.coalesce_ci_demo_2023.dim_wizards.test_valid_email_address: Close
22:48:20 Runtime Error in unit_test test_valid_email_address (models/marts/unit_tests.yml)
  During unit test execution of dim_wizards::test_valid_email_address, dbt could not build the 'actual' result for comparison against 'expected' given the unit test definition:
   Database Error
    001065 (22023): SQL compilation error:
    Function TRY_CAST cannot be used with arguments of types NUMBER(1,0) and NUMBER(38,0)
22:48:20 1 of 1 ERROR dim_wizards::test_valid_email_address ............................. [ERROR in 1.35s]
22:48:20 Finished running node unit_test.coalesce_ci_demo_2023.dim_wizards.test_valid_email_address

That's because this doesn’t work in snowflake: select try_cast(1 as NUMBER); But this does: select try_cast('1' as NUMBER); So does this, but gross: select try_cast(to_numeric(1) as NUMBER) as my_col; so does this: select cast(1 as NUMBER); (but dbt doesn't have a cast macro, only safe_cast https://github.com/dbt-labs/dbt-adapters/issues/84)

If I update my unit test definition to wrap all of the numbers in double quotes, then this error goes away.

Expected Behavior

I should be able to have numbers in my unit test definition without wrapping them in quotes.

Environment

No response

Additional Context

No response

[graciegoheen]

select try_cast(1 as NUMBER); works on BQ and postgres

[graciegoheen]

Is this an update to the safe_cast macro, just add literals to everything? But that macro accepts any expression..

If the jinja type is number, then add string literal

https://docs.getdbt.com/reference/dbt-jinja-functions/cross-database-macros#safe_cast

[graciegoheen]

@MichelleArk was this fixed as part of your adapter case-sensitivity work?

[graciegoheen]

This change was pulled into https://github.com/dbt-labs/dbt-snowflake/pull/896 here.