Do I need to set quote: false for an accepted_values test on booleans?

Help
1

Hi, We are using DBT 1.2.0 with Redshift.
Based on tests | dbt Developer Hub
My understanding is that the “accepted_values” should support boolean type, as quoted

accepted_values
This test validates that all of the values in a column are present in a supplied list of values. If any values other than those provided in the list are present, then the test will fail.

The accepted_values test supports an optional quote parameter which, by default, will single-quote the list of accepted values in the test query. To test non-strings (like integers or boolean values) explicitly set the quote config to false.

But neither of the following YAML file formats works for us

        tests:
          - accepted_values:
              values: [true, false]
              quote: false

Or

        tests:
          - accepted_values:
              values: ['true', 'false']
              quote: false

And I got the following errors in the log:

*[2022-12-13, 20:17:34 UTC] {{subprocess.py:89}} INFO - [0m20:17:34 [33mDatabase Error in test accepted_values_dp_dim_clients_is_g_cfg_pm_checkin_enabled__False__true__false (models/marts/base/dim/dp_dim_clients.yml)[0m*
*[2022-12-13, 20:17:34 UTC] {{subprocess.py:89}} INFO - [0m20:17:34 operator does not exist: character varying <> boolean*
*[2022-12-13, 20:17:34 UTC] {{subprocess.py:89}} INFO - [0m20:17:34 HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.*
*[2022-12-13, 20:17:34 UTC] {{subprocess.py:89}} INFO - [0m20:17:34 compiled SQL at target/run/data_platform_models/models/marts/base/dim/dp_dim_clients.yml/accepted_values_dp_dim_clients_8aca7694846b065184b5b74c9dfc691c.sql*
  1. Is the boolean type supported?
  2. If so, what is the syntax?

Thanks

2

@java8964 can you post the compiled code at the location described in your error message? i.e. target/run/data_platform_models/models/marts/base/dim/dp_dim_clients.yml/accepted_values_dp_dim_clients_8aca7694846b065184b5b74c9dfc691c.sql

3

@joellabes
Thanks for your reply. We are running this on AWS, so I need to contact a team member to access the sql file.
Meantime, can you tell me which format is the right one for the boolean “accepted_values”? [‘true’, ‘false’] or [true, false], or either? I cannot find any example in the document.

I can change to the “supposed correct one” and re-generate the compiled sql file.

Thanks

4

If you enclose the values in quotes, then they will be treated as strings by the YAML parser. Otherwise they will be inserted into the query as booleans (which will likely still work, it just means that they will be True and False instead of true and false.

For example, this SQL and YAML:

--my_model.sql
select true as col1, 1 as id

union all 

select false, 2

version: 2 
models: 
  - name: my_model
    columns: 
      - name: col1
        tests:
          - accepted_values:
              values: ['true', 'false']
              quote: false

Resolves to this query:

with all_values as (

    select
        col1 as value_field,
        count(*) as n_records

    from analytics.dbt_jlabes.my_model
    group by col1

)

select *
from all_values
where value_field not in (
    true,false
)

So the answer is that you should enclose them in single quotes, but you can do either. I suspect that the column you are testing could be a character varying type which is why you’re getting errors - the bool is being passed through correctly but the other column is the wrong type.

5

Yes, it is our mistake that one column in the DB is “varchar”, instead of “boolean”.

Thanks

6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.