dbt Tests Code - iff133/first GitHub Wiki

Check length of a string (e.g. so I have a custom schema tests to check that phone number fields always contain ten-digit number (TDN) strings)

{# Verifies all elements in field are TDNs. If validation_errors returns >0 rows, test fails. #}

{% macro test_is_tdn(model, column_name) %}

with validation as (
  select
    {{ column_name }} as tdn_field
  from {{ model }}
),

validation_errors as (
  select
    tdn_field
  from validation
  where tdn_field not rlike '^\\d{10}$'
)

select count(*)
from validation_errors

{% endmacro %}

• Modified version

{# Verifies all elements in field are TDNs. If validation_errors returns >0 rows, test fails. #}

{% macro test_is_tdn(model, column_name) %}

with validation as (
  select
    {{ column_name }} as tdn_field
  from {{ model }}
),

validation_errors as (
  select
    tdn_field
  from validation
  where tdn_field not rlike '^\\d{16,18}$' or '^[a-zA-Z0-9]{16,18}$'
)

select count(*)
from validation_errors

{% endmacro %}

select
	unique_key
from 
	public.i2gtgeneratedtransactions
where 
	LENGTH(unique_key) != 38 # length does not 38
	LENGTH(unique_key) BETWEEN 1 AND 38 # length is between 1 and 38
	LENGTH(unique_key) NOT BETWEEN 1 AND 38 # length is not between 1 and 38

• Sql length of string:

SELECT
	CHAR_LENGTH('{C7BBDEE0-9B8B-4084-8A24-CF4E94218655}')

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}

• Another:

        - name: flight_end_long
          tests:
              - is_between:
                  bottom_number: -180
                  top_number: 180