Last updated: July 22, 2025
DQOps data quality text sensors, SQL examples
All data quality sensors in the text category supported by DQOps are listed below. Those sensors are measured on a column level.
max word count
Column level sensor that ensures that the count of words in a column does not exceed the maximum accepted length.
Sensor summary
The max word count sensor is documented below.
| Target | Category | Full sensor name | Source code on GitHub |
|---|---|---|---|
| column | text | column/text/max_word_count |
sensors/column/text |
Jinja2 SQL templates
The templates used to generate the SQL query for each data source supported by DQOps is shown below.
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_column_cast_to_string('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_column_cast_to_string('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_column_cast_to_string('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/clickhouse.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_column_cast_to_string('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_column_cast_to_string('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_column_cast_to_string('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/databricks.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/db2.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/hana.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/mysql.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/oracle.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/presto.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
WHERE {{lib.render_target_column('original_table')}} IS NOT NULL
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/spark.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/sqlserver.sql.jinja2' as lib with context -%}
SELECT
MAX(
LEN( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LEN( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LEN( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/teradata.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( OREPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/trino.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
min word count
Column level sensor that ensures that the count of words in a column does not exceed the minimum accepted length.
Sensor summary
The min word count sensor is documented below.
| Target | Category | Full sensor name | Source code on GitHub |
|---|---|---|---|
| column | text | column/text/min_word_count |
sensors/column/text |
Jinja2 SQL templates
The templates used to generate the SQL query for each data source supported by DQOps is shown below.
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_column_cast_to_string('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_column_cast_to_string('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_column_cast_to_string('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/clickhouse.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_column_cast_to_string('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_column_cast_to_string('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_column_cast_to_string('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/databricks.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/db2.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/hana.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/mysql.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/oracle.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/presto.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
WHERE {{lib.render_target_column('original_table')}} IS NOT NULL
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/spark.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/sqlserver.sql.jinja2' as lib with context -%}
SELECT
MIN(
LEN( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LEN( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LEN( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/teradata.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( OREPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_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/trino.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) - LENGTH( REPLACE(TRIM({{lib.render_target_column('analyzed_table')}}), ' ', '') ) + CASE WHEN LENGTH( TRIM({{lib.render_target_column('analyzed_table')}}) ) > 0 THEN 1 ELSE 0 END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
text length above max length count
Column level sensor that calculates the count of text values that are longer than a given length in a column.
Sensor summary
The text length above max length count sensor is documented below.
| Target | Category | Full sensor name | Source code on GitHub |
|---|---|---|---|
| column | text | column/text/text_length_above_max_length_count |
sensors/column/text |
Sensor parameters
| Field name | Description | Allowed data type | Required | Allowed values |
|---|---|---|---|---|
max_length |
This field can be used to define custom length. In order to define custom length, user should write correct length as a integer. If length is not defined by user then default length is 0 | integer |
Jinja2 SQL templates
The templates used to generate the SQL query for each data source supported by DQOps is shown below.
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/clickhouse.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/databricks.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS STRING)) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/db2.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS VARCHAR(4096))) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS VARCHAR)) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/hana.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS CHAR)) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/mysql.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS CHAR)) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/oracle.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}::VARCHAR) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/presto.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
COALESCE(SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}::VARCHAR) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
), 0.0) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table') }}::VARCHAR) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS STRING)) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/spark.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS STRING)) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/sqlserver.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LEN({{ lib.render_target_column('analyzed_table')}}) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/teradata.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS VARCHAR(4096))) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/trino.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
text length above max length percent
Column level sensor that calculates the percentage of text values that are longer than a given length in a column.
Sensor summary
The text length above max length percent sensor is documented below.
| Target | Category | Full sensor name | Source code on GitHub |
|---|---|---|---|
| column | text | column/text/text_length_above_max_length_percent |
sensors/column/text |
Sensor parameters
| Field name | Description | Allowed data type | Required | Allowed values |
|---|---|---|---|---|
max_length |
This field can be used to define custom length. In order to define custom length, user should write correct length as a integer. If length is not defined by user then default length is 0 | integer |
Jinja2 SQL templates
The templates used to generate the SQL query for each data source supported by DQOps is shown below.
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/clickhouse.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/databricks.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS STRING)) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/db2.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS VARCHAR(4096))) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS VARCHAR)) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/hana.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS CHAR)) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/mysql.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS CHAR)) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/oracle.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}::VARCHAR) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/presto.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE CAST(100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS DOUBLE) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
COALESCE(100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}::VARCHAR) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT({{ lib.render_target_column('analyzed_table') }}), 0.0)
AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table') }}::VARCHAR) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS STRING)) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/spark.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS STRING)) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/sqlserver.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT_BIG({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LEN({{ lib.render_target_column('analyzed_table')}}) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) / COUNT_BIG({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/teradata.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS VARCHAR(4096))) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/trino.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE CAST(100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) > {{(parameters.max_length)}}
THEN 1
ELSE 0
END
) AS DOUBLE) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
text length below min length count
Column level sensor that calculates the count of text values that are shorter than a given length in a column.
Sensor summary
The text length below min length count sensor is documented below.
| Target | Category | Full sensor name | Source code on GitHub |
|---|---|---|---|
| column | text | column/text/text_length_below_min_length_count |
sensors/column/text |
Sensor parameters
| Field name | Description | Allowed data type | Required | Allowed values |
|---|---|---|---|---|
min_length |
This field can be used to define custom length. In order to define custom length, user should write correct length as a integer. If length is not defined by user then default length is 0 | integer |
Jinja2 SQL templates
The templates used to generate the SQL query for each data source supported by DQOps is shown below.
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/clickhouse.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/databricks.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS STRING)) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/db2.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS VARCHAR(4096))) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS VARCHAR)) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/hana.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS CHAR)) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/mysql.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS CHAR)) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/oracle.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}::VARCHAR) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/presto.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
COALESCE(SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}::VARCHAR) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
), 0) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
WHERE {{ lib.render_target_column('original_table') }} IS NOT NULL
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table') }}::VARCHAR) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS STRING)) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/spark.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS STRING)) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/sqlserver.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LEN({{ lib.render_target_column('analyzed_table')}}) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/teradata.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS VARCHAR(4096))) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_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/trino.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
text length below min length percent
Column level sensor that calculates the percentage of text values that are shorter than a given length in a column.
Sensor summary
The text length below min length percent sensor is documented below.
| Target | Category | Full sensor name | Source code on GitHub |
|---|---|---|---|
| column | text | column/text/text_length_below_min_length_percent |
sensors/column/text |
Sensor parameters
| Field name | Description | Allowed data type | Required | Allowed values |
|---|---|---|---|---|
min_length |
This field can be used to define custom length. In order to define custom length, user should write correct length as a integer. If length is not defined by user then default length is 0 | integer |
Jinja2 SQL templates
The templates used to generate the SQL query for each data source supported by DQOps is shown below.
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/clickhouse.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/databricks.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS STRING)) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/db2.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS VARCHAR(4096))) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS VARCHAR)) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/hana.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS CHAR)) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/mysql.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS CHAR)) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/oracle.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}::VARCHAR) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/presto.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE CAST(100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS DOUBLE) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
COALESCE(100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}::VARCHAR) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }}), 0.0)
AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
WHERE {{ lib.render_target_column('original_table') }} IS NOT NULL
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table') }}::VARCHAR) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS STRING)) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/spark.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS STRING)) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/sqlserver.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT_BIG({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LEN({{ lib.render_target_column('analyzed_table')}}) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT_BIG({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/teradata.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS VARCHAR(4096))) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/trino.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 0.0
ELSE CAST(100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_column_cast_to_string('analyzed_table')}}) < {{(parameters.min_length)}}
THEN 1
ELSE 0
END
) AS DOUBLE) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
text length in range percent
Column level sensor that calculates the percentage of text values with a length below the indicated length in a column.
Sensor summary
The text length in range percent sensor is documented below.
| Target | Category | Full sensor name | Source code on GitHub |
|---|---|---|---|
| column | text | column/text/text_length_in_range_percent |
sensors/column/text |
Sensor parameters
| Field name | Description | Allowed data type | Required | Allowed values |
|---|---|---|---|---|
min_length |
Sets a minimum text length | integer | ||
max_length |
Sets a maximum text length | integer |
Jinja2 SQL templates
The templates used to generate the SQL query for each data source supported by DQOps is shown below.
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
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_data_grouping_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/clickhouse.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
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_data_grouping_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/databricks.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
ELSE
100.0 * SUM(
CASE
WHEN LENGTH( {{ lib.render_target_column('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_data_grouping_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/db2.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
ELSE
100.0 * SUM(
CASE
WHEN LENGTH(CAST( {{ lib.render_target_column('analyzed_table')}} AS VARCHAR(4096))) 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_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
ELSE
100.0 * SUM(
CASE
WHEN LENGTH( CAST({{ lib.render_target_column('analyzed_table') }} AS VARCHAR) ) 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_data_grouping_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/hana.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
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_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
ELSE
100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS CHAR)) 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_data_grouping_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/mysql.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
ELSE
100.0 * SUM(
CASE
WHEN LENGTH(CAST({{ lib.render_target_column('analyzed_table')}} AS CHAR)) 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_data_grouping_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/oracle.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
ELSE
100.0 * SUM(
CASE
WHEN LENGTH( {{ lib.render_target_column('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_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
ELSE
100.0 * SUM(
CASE
WHEN LENGTH( {{ lib.render_target_column('analyzed_table') }}::VARCHAR ) 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_data_grouping_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/presto.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
ELSE CAST(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
) AS DOUBLE) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
COALESCE(100.0 * SUM(
CASE
WHEN LENGTH( {{ lib.render_target_column('analyzed_table') }}::VARCHAR ) BETWEEN {{parameters.min_length}} AND {{parameters.max_length}} THEN 1
ELSE 0
END
) / COUNT({{ lib.render_target_column('analyzed_table') }}), 100.0)
AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
ELSE
100.0 * SUM(
CASE
WHEN LENGTH( {{ lib.render_target_column('analyzed_table') }}::VARCHAR ) 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_data_grouping_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 100.0
ELSE
100.0 * SUM(
CASE
WHEN LENGTH( {{ lib.render_target_column('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_data_grouping_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/spark.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
ELSE
100.0 * SUM(
CASE
WHEN LENGTH( {{ lib.render_target_column('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_data_grouping_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/sqlserver.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT_BIG({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
ELSE
100.0 * SUM(
CASE
WHEN LEN( {{ lib.render_target_column('analyzed_table') }} ) BETWEEN {{parameters.min_length}} AND {{parameters.max_length}} THEN 1
ELSE 0
END
) / COUNT_BIG({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_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/teradata.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
ELSE
100.0 * SUM(
CASE
WHEN LENGTH( CAST({{ lib.render_target_column('analyzed_table') }} AS VARCHAR(4096))) 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_data_grouping_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/trino.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
ELSE CAST(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
) AS DOUBLE) / COUNT({{ lib.render_target_column('analyzed_table') }})
END AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
text max length
Column level sensor that ensures that the length of text values in a column does not exceed the maximum accepted length.
Sensor summary
The text max length sensor is documented below.
| Target | Category | Full sensor name | Source code on GitHub |
|---|---|---|---|
| column | text | column/text/text_max_length |
sensors/column/text |
Jinja2 SQL templates
The templates used to generate the SQL query for each data source supported by DQOps is shown below.
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH({{lib.render_column_cast_to_string('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_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/clickhouse.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH({{lib.render_column_cast_to_string('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_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/databricks.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH({{ lib.render_target_column('analyzed_table') }})
) AS actual_value
{{- lib.render_data_grouping_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/db2.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS VARCHAR(4096)))
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS VARCHAR))
) AS actual_value
{{- lib.render_data_grouping_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/hana.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH({{lib.render_column_cast_to_string('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS CHAR))
) AS actual_value
{{- lib.render_data_grouping_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/mysql.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS CHAR))
) AS actual_value
{{- lib.render_data_grouping_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/oracle.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH({{ lib.render_target_column('analyzed_table') }})
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH({{ lib.render_target_column('analyzed_table') }}::VARCHAR)
) AS actual_value
{{- lib.render_data_grouping_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/presto.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH({{lib.render_column_cast_to_string('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH({{ lib.render_target_column('analyzed_table') }}::VARCHAR)
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
WHERE {{ lib.render_target_column('original_table') }} IS NOT NULL
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH({{ lib.render_target_column('analyzed_table') }}::VARCHAR)
) AS actual_value
{{- lib.render_data_grouping_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
MAX(
LENGTH({{ lib.render_target_column('analyzed_table') }})
) AS actual_value
{{- lib.render_data_grouping_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/spark.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH({{ lib.render_target_column('analyzed_table') }})
) AS actual_value
{{- lib.render_data_grouping_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/sqlserver.sql.jinja2' as lib with context -%}
SELECT
MAX(
LEN({{ lib.render_target_column('analyzed_table') }})
) AS actual_value
{{- lib.render_data_grouping_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/teradata.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH(CAST({{lib.render_target_column('analyzed_table')}} AS VARCHAR(4096)))
) AS actual_value
{{- lib.render_data_grouping_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/trino.sql.jinja2' as lib with context -%}
SELECT
MAX(
LENGTH({{lib.render_column_cast_to_string('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
text mean length
Column level sensor that ensures that the length of text values in a column does not exceed the mean accepted length.
Sensor summary
The text mean length sensor is documented below.
| Target | Category | Full sensor name | Source code on GitHub |
|---|---|---|---|
| column | text | column/text/text_mean_length |
sensors/column/text |
Jinja2 SQL templates
The templates used to generate the SQL query for each data source supported by DQOps is shown below.
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH({{lib.render_column_cast_to_string('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_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/clickhouse.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH({{lib.render_column_cast_to_string('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_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/databricks.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH({{ lib.render_target_column('analyzed_table') }})
) AS actual_value
{{- lib.render_data_grouping_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/db2.sql.jinja2' as lib with context -%}
SELECT
AVG(
CAST(LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS VARCHAR(4096))) AS DOUBLE)
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS VARCHAR))
) AS actual_value
{{- lib.render_data_grouping_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/hana.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH({{lib.render_column_cast_to_string('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS CHAR))
) AS actual_value
{{- lib.render_data_grouping_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/mysql.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS CHAR))
) AS actual_value
{{- lib.render_data_grouping_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/oracle.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH({{ lib.render_target_column('analyzed_table') }})
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH({{ lib.render_target_column('analyzed_table') }}::VARCHAR)
) AS actual_value
{{- lib.render_data_grouping_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/presto.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH({{lib.render_column_cast_to_string('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH({{ lib.render_target_column('analyzed_table') }}::VARCHAR)
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
WHERE {{ lib.render_target_column('original_table') }} IS NOT NULL
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH({{ lib.render_target_column('analyzed_table') }}::VARCHAR)
) AS actual_value
{{- lib.render_data_grouping_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
AVG(
LENGTH({{ lib.render_target_column('analyzed_table') }})
) AS actual_value
{{- lib.render_data_grouping_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/spark.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH({{ lib.render_target_column('analyzed_table') }})
) AS actual_value
{{- lib.render_data_grouping_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/sqlserver.sql.jinja2' as lib with context -%}
SELECT
AVG(
CAST(LEN({{ lib.render_target_column('analyzed_table') }}) AS FLOAT)
) AS actual_value
{{- lib.render_data_grouping_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/teradata.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH(CAST({{lib.render_target_column('analyzed_table')}} AS VARCHAR(4096)))
) AS actual_value
{{- lib.render_data_grouping_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/trino.sql.jinja2' as lib with context -%}
SELECT
AVG(
LENGTH({{lib.render_column_cast_to_string('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
text min length
Column level sensor that ensures that the length of text values in a column does not exceed the minimum accepted length.
Sensor summary
The text min length sensor is documented below.
| Target | Category | Full sensor name | Source code on GitHub |
|---|---|---|---|
| column | text | column/text/text_min_length |
sensors/column/text |
Jinja2 SQL templates
The templates used to generate the SQL query for each data source supported by DQOps is shown below.
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH({{lib.render_column_cast_to_string('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_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/clickhouse.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH({{lib.render_column_cast_to_string('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_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/databricks.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH({{lib.render_target_column('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_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/db2.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS VARCHAR(4096)))
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS VARCHAR))
) AS actual_value
{{- lib.render_data_grouping_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/hana.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH({{ lib.render_column_cast_to_string('analyzed_table') }})
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS CHAR))
) AS actual_value
{{- lib.render_data_grouping_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/mysql.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH(CAST({{ lib.render_target_column('analyzed_table') }} AS CHAR))
) AS actual_value
{{- lib.render_data_grouping_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/oracle.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH({{ lib.render_target_column('analyzed_table') }})
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH({{ lib.render_target_column('analyzed_table') }}::VARCHAR)
) AS actual_value
{{- lib.render_data_grouping_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/presto.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH({{ lib.render_column_cast_to_string('analyzed_table') }})
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH({{ lib.render_target_column('analyzed_table') }}::VARCHAR)
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
WHERE {{ lib.render_target_column('original_table') }} IS NOT NULL
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH({{ lib.render_target_column('analyzed_table') }}::VARCHAR)
) AS actual_value
{{- lib.render_data_grouping_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
MIN(
LENGTH({{lib.render_target_column('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_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/spark.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH({{lib.render_target_column('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_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/sqlserver.sql.jinja2' as lib with context -%}
SELECT
MIN(
LEN({{lib.render_target_column('analyzed_table')}})
) AS actual_value
{{- lib.render_data_grouping_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/teradata.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH(CAST({{lib.render_target_column('analyzed_table')}} AS VARCHAR(4096)))
) AS actual_value
{{- lib.render_data_grouping_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/trino.sql.jinja2' as lib with context -%}
SELECT
MIN(
LENGTH({{ lib.render_column_cast_to_string('analyzed_table') }})
) AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
What's next
- Learn how the data quality sensors are defined in DQOps and what is the definition of all Jinja2 macros used in the templates
- Understand how DQOps runs data quality checks, rendering templates to SQL queries