Write dbt test for positive values

Question

Is there an easy way to write a test for a column being positive in dbt?

accepted_values doesn't seem to work for continuous vatiables.

I know you can write queries in ./tests but it looks like an overkill for such a simple thing.

score 14 · Accepted Answer · answered Jul 01 '20 at 18:21

14

You could use dbt_utils.expression_is_true

version: 2

models:
  - name: model_name
    tests:
      - dbt_utils.expression_is_true:
          expression: "col_a > 0"

answered Jul 01 '20 at 18:21

Stephan

451
4
5

4

Solid answer. Slight addition: if you are adding a test like this to a specific column in the model then you wouldn't include the column name. So the test here would simply be `expression: "> 0"` – philarmour Feb 10 '22 at 13:49

score 3 · Answer 2 · answered Jul 02 '20 at 15:07

3

I think the dbt_utils suggestion is good, the only reasonable alternative I can think of is writing a custom schema test:

https://docs.getdbt.com/docs/guides/writing-custom-schema-tests/

But why bother when you can just use expression_is_true @jake

answered Jul 02 '20 at 15:07

luther

254
1
7

score 2 · Answer 3 · answered Mar 21 '23 at 08:27

2

Previous answer is correct. Another option is accepted_range:

version: 2

models:
  - name: model_name
    columns:
      - name: user_id
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              inclusive: false

answered Mar 21 '23 at 08:27

ifoukarakis

1,199
8
8

Write dbt test for positive values

3 Answers3