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 variables.

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

Mulct

Answer 1

You could use dbt_utils.expression_is_true

version: 2

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

Seismo

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" – Manure

Answer 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

Via

Answer 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

Chancemedley

Answer 4

Another way with dbt-expectations:

version: 2

models:
  - name: model_name
    columns:
      - name: user_id
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              strictly: true

Natalia