Skip to content

String length in range percent

The query for this check calculates the percent of string values whose length is within a certain range. It is performed by using SQL function LENGTH(), which returns the length of the value passed. The query then checks if the value is between min_length and max_length.

Furthermore, when specifying a different data type then STRING, sensor will cast the column to STRING.

Successfully classified records are assigned values of 1, and any other values, 0. Those values are then summed (so the counting of valid values is effectively performed), divided by the number of records, and multiplicated by a 100.0 so that the results are in percent.

Jinja Template

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN NULL
        ELSE
            100.0 * SUM(
                        CASE WHEN LENGTH( {{ lib.render_column_cast_to_string('analyzed_table') }} ) BETWEEN {{parameters.min_length}} AND {{parameters.max_length}} THEN 1
                                ELSE 0
                            END
    ) / COUNT({{ lib.render_target_column('analyzed_table') }}) END AS actual_value
    {{- lib.render_dimension_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN NULL
        ELSE
            100.0 * SUM(
                        CASE WHEN LENGTH( {{ lib.render_column_cast_to_string('analyzed_table') }} ) BETWEEN {{parameters.min_length}} AND {{parameters.max_length}} THEN 1
                                ELSE 0
                            END
    ) / COUNT({{ lib.render_target_column('analyzed_table') }}) END AS actual_value
    {{- lib.render_dimension_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}