Schema Test - Accepted Values for null value

[ddppi]

Hi, in one of my schema test, I want to test if the accepted values for a column is a list of numbers, or NULL, for example
models:

• name:
  columns:
  • name:
    tests:
    • accepted_values:
      values: [0, 1, 99, null]

I find DBT tries to convert the value of the accepted values to characters, and also covert null to None. Then the test failed, because
Numeric value 'None' is not recognized

Is this an issue?

I am using Snowflake, Python 3.7 and DBT v0.18.0.

Thanks

[clrcrl]

Instead, you should use two separate tests:

version: 2

models:
  - name: my_model
    columns:
      - name: id
        tests:
          - accepted_values:
              values: [0, 1, 99]
          - not_null

[ddppi]

In my case, I consider null is one of the accepted values. If I only leave 0, 1, 99 in the accepted value list, I can see the test passes, so null seems one of the default accepted values?

…

________________________________ From: Claire Carroll <notifications@github.com> Sent: Wednesday, 30 September 2020, 23:06 To: fishtown-analytics/dbt-utils Cc: ddppi; Author Subject: Re: [fishtown-analytics/dbt-utils] Schema Test - Accepted Values for null value (#287) Instead, you should use two separate tests: version: 2 models: - name: my_model columns: - name: id tests: - accepted_values: values: [0, 1, 99] - not_null — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#287 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AM6SDLSZPR3D6H7RNQ6GDK3SIMUMZANCNFSM4R6QFYRA>.

[ddppi]

Hi @clrcrl , can you please let me know how to check accepted value of 0,1,99 and NULL? NULL is an acceptable value for me in this case, and I don't think your suggestion solves my issue.

P.S. it seems NULL is a default accepted value.

[jrmidkiff]

@clrcrl - I suppose the best way to do this is with a custom sql test such as

SELECT t.*
FROM table t 
WHERE t.column IS NOT NULL AND t.column NOT IN ('VALUE1', 'VALUE 2')