[unit testing] hidden error message

[graciegoheen]

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

Test errors with no message

I have a unit test in my project using snowflake:

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}

I get an error with no message:

22:54:47 Writing runtime sql for node "unit_test.coalesce_ci_demo_2023.dim_wizards.test_valid_email_address"
22:54:47 Using snowflake connection "unit_test.coalesce_ci_demo_2023.dim_wizards.test_valid_email_address"
22:54:47 On unit_test.coalesce_ci_demo_2023.dim_wizards.test_valid_email_address: -- Build actual result given inputs
with dbt_internal_unit_test_actual AS (
  select
    WIZARD_ID,IS_VALID_EMAIL_ADDRESS, 'actual' as actual_or_expected
  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
  ) _dbt_internal_unit_test_actual
),
-- Build expected result
dbt_internal_unit_test_expected AS (
  select
    WIZARD_ID, IS_VALID_EMAIL_ADDRESS, 'expected' as actual_or_expected
  from (
    select 
    try_cast('1' as NUMBER)
 AS WIZARD_ID, 
    try_cast(True as BOOLEAN)
 AS IS_VALID_EMAIL_ADDRESS
union all
select 
    try_cast('2' as NUMBER)
 AS WIZARD_ID, 
    try_cast(False as BOOLEAN)
 AS IS_VALID_EMAIL_ADDRESS
union all
select 
    try_cast('3' as NUMBER)
 AS WIZARD_ID, 
    try_cast(False as BOOLEAN)
 AS IS_VALID_EMAIL_ADDRESS
union all
select 
    try_cast('4' as NUMBER)
 AS WIZARD_ID, 
    try_cast(False as BOOLEAN)
 AS IS_VALID_EMAIL_ADDRESS
  ) _dbt_internal_unit_test_expected
)
-- Union actual and expected results
select * from dbt_internal_unit_test_actual
union all
select * from dbt_internal_unit_test_expected
22:54:47 SQL status: SUCCESS 8 in 0.0 seconds
22:54:47 Applying DROP to: DEVELOPMENT.dbt_ggoheen.test_valid_email_address__dbt_tmp
22:54:47 Using snowflake connection "unit_test.coalesce_ci_demo_2023.dim_wizards.test_valid_email_address"
22:54:47 On unit_test.coalesce_ci_demo_2023.dim_wizards.test_valid_email_address: drop table if exists DEVELOPMENT.dbt_ggoheen.test_valid_email_address__dbt_tmp cascade
22:54:47 SQL status: SUCCESS 1 in 0.0 seconds
22:54:47 Timing info for unit_test.coalesce_ci_demo_2023.dim_wizards.test_valid_email_address (execute): 22:54:45.267084 => 22:54:47.975875
22:54:47 On unit_test.coalesce_ci_demo_2023.dim_wizards.test_valid_email_address: Close
22:54:48 1 of 1 ERROR dim_wizards::test_valid_email_address ............................. [ERROR in 2.87s]
22:54:48 Finished running node unit_test.coalesce_ci_demo_2023.dim_wizards.test_valid_email_address

Expected Behavior

I should not get an error.

Relevant log output

No response

Environment

No response

Which database adapter are you using with dbt?

snowflake

Additional Context

seems to be snowflake specific

[graciegoheen]

This is happening both when the SQL comparing actual to expected:

• is different
• is the same

We can run the SQL (copy & paste) in snowflake

This is happening when running in the Cloud IDE

[gshank]

The underlying error was a failure in casing of "actual_or_expected". However I'm not seeing the missing error message. For both the test and the build commands in a test case, I get:
"15:29:16 4 of 5 START unit_test dim_wizards::test_valid_email_address ................... [RUN]
15:29:19 Unhandled error while executing models/unit_tests.yml
'actual_or_expected'
15:29:19 4 of 5 ERROR dim_wizards::test_valid_email_address ............................. [ERROR in 2.51s]
"

Now that error message really out to be "KeyError: 'actual_or_expected'" but apparently the missing KeyError is a Python mis-fire, so there's not a lot we can do about it.

Exactly how are you executing this command? Are you using debug? Is it in the IDE or the command line? I'm guessing that we're missing the actual exception to stdout somehow.

[gshank]

The fix for the casing issue is handled in the fix for #9467. The issue with why we didn't see the error in the IDE logs will be handled separately (#9557)