Customized schema test compile problems

[QPeiran]

Describe the bug

Customized schema test malfunction -- return 1 even the test condition is true. (So does db_utils.expression is true)

Steps To Reproduce

Here is a very basic customized test macro, I am testing against a field that has all positive values:

{% macro test_is_positive(model, column_name) %}

with validation as (

    select
        {{ column_name }} as positive_field

    from {{ model }}

),

validation_errors as (

    select
        positive_field

    from validation
    -- if this is true, then even_field is actually odd!
    where not(positive_field >= 0)

)

select count(*)
from validation_errors

{% endmacro %}

Expected behavior

It can pass the test

Actual behavior

test failed

System information

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

installed version: 0.19.0
   latest version: 0.19.1

The operating system you're using:

The output of python --version:
Python 3.9.4

Additional context

The compiled SQL is:

select count(*) as validation_errors
    from (
      

with validation as (

    select
        sale_price as positive_field

    from DEMO_DB.DEV_with_staging.snowflake_model_items_price_abnormal

),

validation_errors as (

    select
        positive_field

    from validation
    -- if this is true, then even_field is actually odd!
    where not(positive_field >= 0)

)

select count(*)
from validation_errors


    ) _dbt_internal_test

It returns 1 even past the test.

By review the compiled code, I think for line 23, it should be select * instead of select count(*):

select count(*) as validation_errors
    from (
      

with validation as (

    select
        sale_price as positive_field

    from DEMO_DB.DEV_with_staging.snowflake_model_items_price_abnormal

),

validation_errors as (

    select
        positive_field

    from validation
    -- if this is true, then even_field is actually odd!
    where not(positive_field >= 0)

)

select *   -- HERE is line 23
from validation_errors


    ) _dbt_internal_test

Then it returns 0 as expected.

[jtcohen6]

Hey @QPeiran, thanks for opening the issue!

I see that your dbt version output says 0.19.0, but I get a strong sense that you're using changes in dbt v0.20.0-b1, namely #3286, which swapped schema/generic tests from returning a numeric value (e.g. count(*)) to returning a set of rows from which failures could then be counted/calculated.

I see your test query is being wrapped in:

https://github.com/fishtown-analytics/dbt/blob/8d39ef16b67fcc3c30e8a18751b4e64831ec9aaf/core/dbt/include/global_project/macros/materializations/test.sql#L4-L7

That's brand new for v0.20.0.

By review the compiled code, I think for line 23, it should be select * instead of select count(*)

I completely agree! This is a required + breaking change for all schema/generic tests, starting in v0.20.0.

By chance, are you installing dbt from source?

[QPeiran]

Hi @jtcohen6 , exactly! I installed it from the git source.

[jtcohen6]

Got it, ok! You're using a development (beta) version of dbt. The resolution here is exactly as you say: Replace the final select count(*) in your schema test definition with select *.

If you'd instead prefer to use the latest stable version of dbt, please install v0.19.1 from Homebrew, PyPi, or the 0.19.latest branch.