expect_column_distinct_values_to_be_in_set.sql

{% macro test_expect_column_distinct_values_to_be_in_set(model, column_name,
                                                    values,
                                                    quote_values=False,
                                                    partition_column=None,
                                                    partition_filter=None
                                                    ) %}

with all_values as (

    select distinct
        {{ column_name }} as value_field

    from {{ model }}
    {% if partition_column and partition_filter %}
    where {{ partition_column }} {{ partition_filter }}
    {% endif %}

),
set_values as (

    {% for value in values -%}
    select 
        {% if quote_values -%}
        '{{ value }}'
        {%- else -%}
        {{ value }}
        {%- endif %} as value_field
    {% if not loop.last %}union all{% endif %}
    {% endfor %}

),
unique_set_values as (

    select distinct value_field
    from
        set_values

),
validation_errors as (
    -- values from the model that are not in the set
    select
        v.value_field
    from 
        all_values v
        left outer join
        unique_set_values s on v.value_field = s.value_field
    where
        v.value_field is null

)

select count(*) as validation_errors
from validation_errors

{% endmacro %}