calogica / dbt-expectations

Port(ish) of Great Expectations to dbt test macros
https://calogica.github.io/dbt-expectations/
Apache License 2.0
989 stars 120 forks source link

[Feature Request] Proportion test for varchar columns #267

Open rpremis opened 1 year ago

rpremis commented 1 year ago

Describe the solution you'd like I would like to have the possibility of a proportion test for varchar type columns. As of right now, the dbt-expectations does not support this kind of test. The test would look as follows:

{% test expect_column_proportion_to_be_between(
    model, 
    column_name, 
    group_by='1=1', 
    where_clause='1=1', 
    column_condition=True, 
    min_value=0, 
    max_value=1
) %}

with column_count as (

    select

        {{ group_by }},
        count(case when {{ column_condition }} then {{ column_name }} end) as numerator,
        count({{ column_name }}) as denominator,
        numerator / denominator as proportion

    from {{ model }}
    where {{ where_clause }}
    group by {{ group_by }}

),

column_comparison as (

    select

        {{ group_by }},
        proportion between {{ min_value }} and {{ max_value }} as is_within_proportion_range

    from column_count

)

select *
from column_comparison
where not is_within_proportion_range

{% endtest %}
rpremis commented 1 year ago

If you agree, I'd be willing to put out the PR for this.