Examples of custom schema tests

Show and Tell
5

We wanted to be able to test that numbers (float or non-float) were between a certain set of values. So we did this:

{% macro test_is_between(model, column_name, bottom_number, top_number) %}

with validation as (

    select
        {{ column_name }} as field_to_test

    from {{ model }}

),

validation_errors as (

    select
        field_to_test

    from validation
    where field_to_test > {{ top_number }} or field_to_test < {{ bottom_number }}

)

select count(*)
from validation_errors

{% endmacro %}

And here is an example of a test:

  - name: flight_end_long
    tests:
      - is_between: {'bottom_number' : -180, 'top_number' : 180}
2 Likes