strings column sensors
expected strings in top values count
Full sensor name
DescriptionColumn level sensor that counts how many expected string values are among the TOP most popular values in the column. The sensor will first count the number of occurrences of each column's value and will pick the TOP X most popular values (configurable by the 'top' parameter). Then, it will compare the list of most popular values to the given list of expected values that should be most popular. This sensor will return the number of expected values that were found within the 'top' most popular column values. This sensor is useful for analyzing string columns that have several very popular values, these could be the country codes of the countries with the most number of customers. The sensor can detect if any of the most popular value (an expected value) is no longer one of the top X most popular values.
Parameters
| Field name | Description | Allowed data type | Is it required? | Allowed values |
|---|---|---|---|---|
| expected_values | List of expected string values that should be found in the tested column among the TOP most popular (highest distinct count) column values. | string_list | ||
| top | The number of the most popular values (with the highest distinct count) that are analyzed to find the expected values. | long |
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
{%- macro extract_in_list(values_list) -%}
{%- for i in values_list -%}
{%- if not loop.last -%}
{{lib.make_text_constant(i)}}{{", "}}
{%- else -%}
{{lib.make_text_constant(i)}}
{%- endif -%}
{%- endfor -%}
{%- endmacro -%}
{%- macro render_from_subquery() -%}
FROM
(
SELECT
top_col_values.top_value as top_value,
top_col_values.time_period as time_period,
top_col_values.time_period_utc as time_period_utc,
RANK() OVER(PARTITION BY top_col_values.time_period {{- render_data_grouping('top_col_values', indentation = ' ') }}
ORDER BY top_col_values.total_values) as top_values_rank {{- render_data_grouping('top_col_values', indentation = ' ') }}
FROM
(
SELECT
{{ lib.render_target_column('analyzed_table') }} AS top_value,
COUNT(*) AS total_values
{{- lib.render_data_grouping_projections('analyzed_table', indentation = ' ') }}
{{- lib.render_time_dimension_projection('analyzed_table', indentation = ' ') }}
FROM
{{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause(indentation = ' ') }}
{{- lib.render_group_by(indentation = ' ') }}, top_value
{{- lib.render_order_by(indentation = ' ') }}, total_values
) AS top_col_values
) AS top_values
WHERE top_values_rank <= {{ parameters.top }}
{%- endmacro -%}
{%- macro render_data_grouping(table_alias_prefix = '', indentation = '') -%}
{%- if lib.data_groupings is not none and (lib.data_groupings | length()) > 0 -%}
{%- for attribute in lib.data_groupings -%}
{{ ',' }}
{%- with data_grouping_level = lib.data_groupings[attribute] -%}
{%- if data_grouping_level.source == 'tag' -%}
{{ indentation }}{{ lib.make_text_constant(data_grouping_level.tag) }}
{%- elif data_grouping_level.source == 'column_value' -%}
{{ indentation }}{{ table_alias_prefix }}.grouping_{{ attribute }}
{%- endif -%}
{%- endwith %}
{%- endfor -%}
{%- endif -%}
{%- endmacro -%}
SELECT
{%- if 'expected_values' not in parameters or parameters.expected_values|length == 0 %}
NULL AS actual_value,
MAX(0) AS expected_value
{{- lib.render_data_grouping_projections('analyzed_table') }}
{{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{%- else %}
COUNT(DISTINCT
CASE
WHEN top_values.top_value IN ({{ extract_in_list(parameters.expected_values) }}) THEN top_values.top_value
ELSE NULL
END
) AS actual_value,
MAX({{ parameters.expected_values | length }}) AS expected_value,
top_values.time_period,
top_values.time_period_utc
{{- render_data_grouping('top_values', indentation = lib.eol() ~ ' ') }}
{{ render_from_subquery() }}
{%- endif -%}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/mysql.sql.jinja2' as lib with context -%}
{%- macro extract_in_list(values_list) -%}
{%- for i in values_list -%}
{%- if not loop.last -%}
{{lib.make_text_constant(i)}}{{", "}}
{%- else -%}
{{lib.make_text_constant(i)}}
{%- endif -%}
{%- endfor -%}
{%- endmacro -%}
{%- macro render_from_subquery() -%}
FROM
(
SELECT
top_col_values.top_value as top_value,
top_col_values.time_period as time_period,
top_col_values.time_period_utc as time_period_utc,
RANK() OVER(PARTITION BY top_col_values.time_period {{- render_data_grouping('top_col_values', indentation = ' ') }}
ORDER BY top_col_values.total_values) as top_values_rank {{- render_data_grouping('top_col_values', indentation = ' ') }}
FROM
(
SELECT
{{ lib.render_target_column('analyzed_table') }} AS top_value,
COUNT(*) AS total_values
{{- lib.render_data_grouping_projections('analyzed_table', indentation = ' ') }}
{{- lib.render_time_dimension_projection('analyzed_table', indentation = ' ') }}
FROM
{{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause(indentation = ' ') }}
{{- lib.render_group_by(indentation = ' ') }}, top_value
{{- lib.render_order_by(indentation = ' ') }}, total_values
) AS top_col_values
) AS top_values
WHERE top_values_rank <= {{ parameters.top }}
{%- endmacro -%}
{%- macro render_data_grouping(table_alias_prefix = '', indentation = '') -%}
{%- if lib.data_groupings is not none and (lib.data_groupings | length()) > 0 -%}
{%- for attribute in lib.data_groupings -%}
{{ ',' }}
{%- with data_grouping_level = lib.data_groupings[attribute] -%}
{%- if data_grouping_level.source == 'tag' -%}
{{ indentation }}{{ lib.make_text_constant(data_grouping_level.tag) }}
{%- elif data_grouping_level.source == 'column_value' -%}
{{ indentation }}{{ table_alias_prefix }}.grouping_{{ attribute }}
{%- endif -%}
{%- endwith %}
{%- endfor -%}
{%- endif -%}
{%- endmacro -%}
SELECT
{%- if 'expected_values' not in parameters or parameters.expected_values|length == 0 %}
NULL AS actual_value,
MAX(0) AS expected_value
{{- lib.render_data_grouping_projections('analyzed_table') }}
{{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{%- else %}
COUNT(DISTINCT
CASE
WHEN top_values.top_value IN ({{ extract_in_list(parameters.expected_values) }}) THEN top_values.top_value
ELSE NULL
END
) AS actual_value,
MAX({{ parameters.expected_values | length }}) AS expected_value,
top_values.time_period,
top_values.time_period_utc
{{- render_data_grouping('top_values', indentation = lib.eol() ~ ' ') }}
{{ render_from_subquery() }}
{%- endif -%}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
{%- macro extract_in_list(values_list) -%}
{%- for i in values_list -%}
{%- if not loop.last -%}
{{lib.make_text_constant(i)}}{{", "}}
{%- else -%}
{{lib.make_text_constant(i)}}
{%- endif -%}
{%- endfor -%}
{%- endmacro -%}
{%- macro render_from_subquery() -%}
FROM
(
SELECT
top_col_values.top_value as top_value,
top_col_values.time_period as time_period,
top_col_values.time_period_utc as time_period_utc,
RANK() OVER(PARTITION BY top_col_values.time_period {{- render_data_grouping('top_col_values', indentation = ' ') }}
ORDER BY top_col_values.total_values) as top_values_rank {{- render_data_grouping('top_col_values', indentation = ' ') }}
FROM
(
SELECT
{{ lib.render_target_column('analyzed_table') }} AS top_value,
COUNT(*) AS total_values
{{- lib.render_data_grouping_projections('analyzed_table', indentation = ' ') }}
{{- lib.render_time_dimension_projection('analyzed_table', indentation = ' ') }}
FROM
{{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause(indentation = ' ') }}
{{- lib.render_group_by(indentation = ' ') }}, top_value
{{- lib.render_order_by(indentation = ' ') }}, total_values
) AS top_col_values
) AS top_values
WHERE top_values_rank <= {{ parameters.top }}
{%- endmacro -%}
{%- macro render_data_grouping(table_alias_prefix = '', indentation = '') -%}
{%- if lib.data_groupings is not none and (lib.data_groupings | length()) > 0 -%}
{%- for attribute in lib.data_groupings -%}
{{ ',' }}
{%- with data_grouping_level = lib.data_groupings[attribute] -%}
{%- if data_grouping_level.source == 'tag' -%}
{{ indentation }}{{ lib.make_text_constant(data_grouping_level.tag) }}
{%- elif data_grouping_level.source == 'column_value' -%}
{{ indentation }}{{ table_alias_prefix }}.grouping_{{ attribute }}
{%- endif -%}
{%- endwith %}
{%- endfor -%}
{%- endif -%}
{%- endmacro -%}
SELECT
{%- if 'expected_values' not in parameters or parameters.expected_values|length == 0 %}
NULL AS actual_value,
MAX(0) AS expected_value
{{- lib.render_data_grouping_projections('analyzed_table') }}
{{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{%- else %}
COUNT(DISTINCT
CASE
WHEN top_values.top_value IN ({{ extract_in_list(parameters.expected_values) }}) THEN top_values.top_value
ELSE NULL
END
) AS actual_value,
MAX({{ parameters.expected_values | length }}) AS expected_value,
top_values.time_period,
top_values.time_period_utc
{{- render_data_grouping('top_values', indentation = lib.eol() ~ ' ') }}
{{ render_from_subquery() }}
{%- endif -%}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
{%- macro extract_in_list(values_list) -%}
{%- for i in values_list -%}
{%- if not loop.last -%}
{{lib.make_text_constant(i)}}{{", "}}
{%- else -%}
{{lib.make_text_constant(i)}}
{%- endif -%}
{%- endfor -%}
{%- endmacro -%}
{%- macro render_from_subquery() -%}
FROM
(
SELECT
top_col_values.top_value as top_value,
top_col_values.time_period as time_period,
top_col_values.time_period_utc as time_period_utc,
RANK() OVER(PARTITION BY top_col_values.time_period {{- render_data_grouping('top_col_values', indentation = ' ') }}
ORDER BY top_col_values.total_values) as top_values_rank {{- render_data_grouping('top_col_values', indentation = ' ') }}
FROM
(
SELECT
{{ lib.render_target_column('analyzed_table') }} AS top_value,
COUNT(*) AS total_values
{{- lib.render_data_grouping_projections('analyzed_table', indentation = ' ') }}
{{- lib.render_time_dimension_projection('analyzed_table', indentation = ' ') }}
FROM
{{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause(indentation = ' ') }}
{{- lib.render_group_by(indentation = ' ') }}, top_value
{{- lib.render_order_by(indentation = ' ') }}, total_values
) AS top_col_values
) AS top_values
WHERE top_values_rank <= {{ parameters.top }}
{%- endmacro -%}
{%- macro render_data_grouping(table_alias_prefix = '', indentation = '') -%}
{%- if lib.data_groupings is not none and (lib.data_groupings | length()) > 0 -%}
{%- for attribute in lib.data_groupings -%}
{{ ',' }}
{%- with data_grouping_level = lib.data_groupings[attribute] -%}
{%- if data_grouping_level.source == 'tag' -%}
{{ indentation }}{{ lib.make_text_constant(data_grouping_level.tag) }}
{%- elif data_grouping_level.source == 'column_value' -%}
{{ indentation }}{{ table_alias_prefix }}.grouping_{{ attribute }}
{%- endif -%}
{%- endwith %}
{%- endfor -%}
{%- endif -%}
{%- endmacro -%}
SELECT
{%- if 'expected_values' not in parameters or parameters.expected_values|length == 0 %}
NULL AS actual_value,
MAX(0) AS expected_value
{{- lib.render_data_grouping_projections('analyzed_table') }}
{{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{%- else %}
COUNT(DISTINCT
CASE
WHEN top_values.top_value IN ({{ extract_in_list(parameters.expected_values) }}) THEN top_values.top_value
ELSE NULL
END
) AS actual_value,
MAX({{ parameters.expected_values | length }}) AS expected_value,
top_values.time_period,
top_values.time_period_utc
{{- render_data_grouping('top_values', indentation = lib.eol() ~ ' ') }}
{{ render_from_subquery() }}
{%- endif -%}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
{%- macro extract_in_list(values_list) -%}
{%- for i in values_list -%}
{%- if not loop.last -%}
{{lib.make_text_constant(i)}}{{", "}}
{%- else -%}
{{lib.make_text_constant(i)}}
{%- endif -%}
{%- endfor -%}
{%- endmacro -%}
{%- macro render_from_subquery() -%}
FROM
(
SELECT
top_col_values.top_value as top_value,
top_col_values.time_period as time_period,
top_col_values.time_period_utc as time_period_utc,
RANK() OVER(PARTITION BY top_col_values.time_period {{- render_data_grouping('top_col_values', indentation = ' ') }}
ORDER BY top_col_values.total_values) as top_values_rank {{- render_data_grouping('top_col_values', indentation = ' ') }}
FROM
(
SELECT
{{ lib.render_target_column('analyzed_table') }} AS top_value,
COUNT(*) AS total_values
{{- lib.render_data_grouping_projections('analyzed_table', indentation = ' ') }}
{{- lib.render_time_dimension_projection('analyzed_table', indentation = ' ') }}
FROM
{{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause(indentation = ' ') }}
{{- lib.render_group_by(indentation = ' ') }}, top_value
{{- lib.render_order_by(indentation = ' ') }}, total_values
) AS top_col_values
) AS top_values
WHERE top_values_rank <= {{ parameters.top }}
{%- endmacro -%}
{%- macro render_data_grouping(table_alias_prefix = '', indentation = '') -%}
{%- if lib.data_groupings is not none and (lib.data_groupings | length()) > 0 -%}
{%- for attribute in lib.data_groupings -%}
{{ ',' }}
{%- with data_grouping_level = lib.data_groupings[attribute] -%}
{%- if data_grouping_level.source == 'tag' -%}
{{ indentation }}{{ lib.make_text_constant(data_grouping_level.tag) }}
{%- elif data_grouping_level.source == 'column_value' -%}
{{ indentation }}{{ table_alias_prefix }}.grouping_{{ attribute }}
{%- endif -%}
{%- endwith %}
{%- endfor -%}
{%- endif -%}
{%- endmacro -%}
SELECT
{%- if 'expected_values' not in parameters or parameters.expected_values|length == 0 %}
NULL AS actual_value,
MAX(0) AS expected_value
{{- lib.render_data_grouping_projections('analyzed_table') }}
{{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{%- else %}
COUNT(DISTINCT
CASE
WHEN top_values.top_value IN ({{ extract_in_list(parameters.expected_values) }}) THEN top_values.top_value
ELSE NULL
END
) AS actual_value,
MAX({{ parameters.expected_values | length }}) AS expected_value,
top_values.time_period,
top_values.time_period_utc
{{- render_data_grouping('top_values', indentation = lib.eol() ~ ' ') }}
{{ render_from_subquery() }}
{%- endif -%}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}
{%- macro extract_in_list(values_list) -%}
{%- for i in values_list -%}
{%- if not loop.last -%}
{{lib.make_text_constant(i)}}{{", "}}
{%- else -%}
{{lib.make_text_constant(i)}}
{%- endif -%}
{%- endfor -%}
{%- endmacro -%}
{%- macro render_from_subquery() -%}
FROM
(
SELECT
top_col_values.top_value as top_value,
top_col_values.time_period as time_period,
top_col_values.time_period_utc as time_period_utc,
RANK() OVER(PARTITION BY top_col_values.time_period {{- render_data_grouping('top_col_values', indentation = ' ') }}
ORDER BY top_col_values.total_values) as top_values_rank {{- render_data_grouping('top_col_values', indentation = ' ') }}
FROM
(
SELECT
{{ lib.render_target_column('analyzed_table') }} AS top_value,
COUNT_BIG(*) AS total_values
{{- lib.render_data_grouping_projections('analyzed_table', indentation = ' ') }}
{{- lib.render_time_dimension_projection('analyzed_table', indentation = ' ') }}
FROM
{{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause(indentation = ' ') }}
{%- if (lib.data_groupings is not none and (lib.data_groupings | length()) > 0) or (lib.time_series.mode is not none and lib.time_series.mode != 'current_time') -%}
{{- lib.render_group_by(indentation = ' ') }}, {{ lib.render_target_column('analyzed_table') }}
{%- else %}
GROUP BY {{ lib.render_target_column('analyzed_table') }}
{%- endif %}
) AS top_col_values
) AS top_values
WHERE top_values_rank <= {{ parameters.top }}
{%- endmacro -%}
{%- macro render_data_grouping(table_alias_prefix = '', indentation = '') -%}
{%- if lib.data_groupings is not none and (lib.data_groupings | length()) > 0 -%}
{%- for attribute in lib.data_groupings -%}
{{ ',' }}
{%- with data_grouping_level = lib.data_groupings[attribute] -%}
{%- if data_grouping_level.source == 'tag' -%}
{{ indentation }}{{ lib.make_text_constant(data_grouping_level.tag) }}
{%- elif data_grouping_level.source == 'column_value' -%}
{{ indentation }}{{ table_alias_prefix }}.grouping_{{ attribute }}
{%- endif -%}
{%- endwith %}
{%- endfor -%}
{%- endif -%}
{%- endmacro -%}
SELECT
{%- if 'expected_values' not in parameters or parameters.expected_values|length == 0 %}
NULL AS actual_value,
MAX(0) AS expected_value
{{- lib.render_data_grouping_projections('analyzed_table') }}
{{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{%- else %}
COUNT_BIG(DISTINCT
CASE
WHEN top_values.top_value IN ({{ extract_in_list(parameters.expected_values) }}) THEN top_values.top_value
ELSE NULL
END
) AS actual_value,
MAX({{ parameters.expected_values | length }}) AS expected_value,
{%- if (lib.data_groupings is not none and (lib.data_groupings | length) > 0) -%}
{%- for attribute in lib.data_groupings -%}
top_values.grouping_{{ attribute }}{{ ', ' }}
{%- endfor -%}
{%- endif -%}
top_values.time_period,
top_values.time_period_utc
{{ render_from_subquery() }}
{%- endif %}
GROUP BY time_period, time_period_utc
{%- if (lib.data_groupings is not none and (lib.data_groupings | length) > 0) -%}
{%- for attribute in lib.data_groupings -%}
{{ ', ' }}top_values.grouping_{{ attribute }}
{%- endfor -%}
{%- endif -%}
expected strings in use count
Full sensor name
DescriptionColumn level sensor that counts how many expected string values are used in a tested column. Finds unique column values from the set of expected string values and counts them. This sensor is useful to analyze string columns that have a low number of unique values and it should be tested if all possible values from the list of expected values are used in any row. The typical type of columns analyzed using this sensor are currency, country, status or gender columns.
Parameters
| Field name | Description | Allowed data type | Is it required? | Allowed values |
|---|---|---|---|---|
| expected_values | List of expected string values that should be found in the tested column. | string_list |
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
{%- macro extract_in_list(values_list) -%}
{%- for i in values_list -%}
{%- if not loop.last -%}
{{lib.make_text_constant(i)}}{{", "}}
{%- else -%}
{{lib.make_text_constant(i)}}
{%- endif -%}
{%- endfor -%}
{% endmacro -%}
{%- macro actual_value() -%}
{%- if 'expected_values' not in parameters or parameters.expected_values|length == 0 -%}
NULL
{%- else -%}
COUNT(DISTINCT
CASE
WHEN {{ lib.render_target_column('analyzed_table') }} IN ({{ extract_in_list(parameters.expected_values) }})
THEN {{ lib.render_target_column('analyzed_table') }}
ELSE NULL
END
)
{%- endif -%}
{% endmacro -%}
SELECT
{{ actual_value() }} AS actual_value,
MAX({{ parameters.expected_values | length }}) AS expected_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 -%}
{%- macro extract_in_list(values_list) -%}
{%- for i in values_list -%}
{%- if not loop.last -%}
{{lib.make_text_constant(i)}}{{", "}}
{%- else -%}
{{lib.make_text_constant(i)}}
{%- endif -%}
{%- endfor -%}
{% endmacro -%}
{%- macro render_else() -%}
{%- if parameters.expected_values|length == 0 -%}
0
{%- else -%}
COUNT(DISTINCT
CASE
WHEN {{ lib.render_target_column('analyzed_table') }} IN ({{ extract_in_list(parameters.expected_values) }})
THEN {{ lib.render_target_column('analyzed_table') }}
ELSE NULL
END
)
{%- endif -%}
{% endmacro -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN MAX(0)
ELSE {{render_else()}}
END AS actual_value,
MAX({{ parameters.expected_values | length }}) AS expected_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/postgresql.sql.jinja2' as lib with context -%}
{%- macro extract_in_list(values_list) -%}
{%- for i in values_list -%}
{%- if not loop.last -%}
{{lib.make_text_constant(i)}}{{", "}}
{%- else -%}
{{lib.make_text_constant(i)}}
{%- endif -%}
{%- endfor -%}
{% endmacro -%}
{%- macro render_else() -%}
{%- if parameters.expected_values|length == 0 -%}
NULL
{%- else -%}
COUNT(DISTINCT
CASE
WHEN {{ lib.render_target_column('analyzed_table') }} IN ({{ extract_in_list(parameters.expected_values) }})
THEN {{ lib.render_target_column('analyzed_table') }}
ELSE NULL
END
)
{%- endif -%}
{% endmacro -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN NULL
ELSE {{render_else()}}
END AS actual_value,
MAX({{ parameters.expected_values | length }}) AS expected_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/redshift.sql.jinja2' as lib with context -%}
{%- macro extract_in_list(values_list) -%}
{%- for i in values_list -%}
{%- if not loop.last -%}
{{lib.make_text_constant(i)}}{{", "}}
{%- else -%}
{{lib.make_text_constant(i)}}
{%- endif -%}
{%- endfor -%}
{% endmacro -%}
{%- macro render_else() -%}
{%- if parameters.expected_values|length == 0 -%}
NULL
{%- else -%}
COUNT(DISTINCT
CASE
WHEN {{ lib.render_target_column('analyzed_table') }} IN ({{ extract_in_list(parameters.expected_values) }})
THEN {{ lib.render_target_column('analyzed_table') }}
ELSE NULL
END
)
{%- endif -%}
{% endmacro -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN NULL
ELSE {{render_else()}}
END AS actual_value,
MAX({{ parameters.expected_values | length }}) AS expected_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 -%}
{%- macro extract_in_list(values_list) -%}
{%- for i in values_list -%}
{%- if not loop.last -%}
{{lib.make_text_constant(i)}}{{", "}}
{%- else -%}
{{lib.make_text_constant(i)}}
{%- endif -%}
{%- endfor -%}
{% endmacro -%}
{%- macro render_else() -%}
{%- if parameters.expected_values|length == 0 -%}
NULL
{%- else -%}
COUNT(DISTINCT
CASE
WHEN {{ lib.render_target_column('analyzed_table') }} IN ({{ extract_in_list(parameters.expected_values) }})
THEN {{ lib.render_target_column('analyzed_table') }}
ELSE NULL
END
)
{%- endif -%}
{% endmacro -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN NULL
ELSE {{render_else()}}
END AS actual_value,
MAX({{ parameters.expected_values | length }}) AS expected_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 -%}
{%- macro extract_in_list(values_list) -%}
{%- for i in values_list -%}
{%- if not loop.last -%}
{{lib.make_text_constant(i)}}{{", "}}
{%- else -%}
{{lib.make_text_constant(i)}}
{%- endif -%}
{%- endfor -%}
{% endmacro -%}
{%- macro render_else() -%}
{%- if parameters.expected_values|length == 0 -%}
NULL
{%- else -%}
COUNT_BIG(DISTINCT
CASE
WHEN {{ lib.render_target_column('analyzed_table') }} IN ({{ extract_in_list(parameters.expected_values) }})
THEN {{ lib.render_target_column('analyzed_table') }}
ELSE NULL
END
)
{%- endif -%}
{% endmacro -%}
SELECT
CASE
WHEN COUNT_BIG(*) = 0 THEN NULL
ELSE {{render_else()}}
END AS actual_value,
MAX({{ parameters.expected_values | length }}) AS expected_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() -}}
string boolean placeholder percent
Full sensor name
DescriptionColumn level sensor that calculates the number of rows with a boolean placeholder string column value.
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LOWER({{ lib.render_target_column('analyzed_table')}}) IN ('true', 'false', 't', 'f', 'y', 'n', 'yes', 'no', '1', '0')
THEN 1
ELSE 0
END
) / COUNT(*)
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(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LOWER({{ lib.render_target_column('analyzed_table')}}) IN ('true', 'false', 't', 'f', 'y', 'n', 'yes', 'no', '1', '0')
THEN 1
ELSE 0
END
) / COUNT(*)
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(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LOWER({{ lib.render_target_column('analyzed_table')}}) IN ('true', 'false', 't', 'f', 'y', 'n', 'yes', 'no', '1', '0')
THEN 1
ELSE 0
END
) / COUNT(*)
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
{{- lib.render_where_clause(table_alias_prefix='original_table') }}) analyzed_table
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LOWER({{ lib.render_target_column('analyzed_table')}}) IN ('true', 'false', 't', 'f', 'y', 'n', 'yes', 'no', '1', '0')
THEN 1
ELSE 0
END
) / COUNT(*)
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/redshift.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LOWER({{ lib.render_target_column('analyzed_table')}}) IN ('true', 'false', 't', 'f', 'y', 'n', 'yes', 'no', '1', '0')
THEN 1
ELSE 0
END
) / COUNT(*)
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(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LOWER({{ lib.render_target_column('analyzed_table')}}) IN ('true', 'false', 't', 'f', 'y', 'n', 'yes', 'no', '1', '0')
THEN 1
ELSE 0
END
) / COUNT(*)
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(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LOWER({{ lib.render_target_column('analyzed_table')}}) IN ('true', 'false', 't', 'f', 'y', 'n', 'yes', 'no', '1', '0')
THEN 1
ELSE 0
END
) / COUNT_BIG(*)
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() -}}
string datatype detect
Full sensor name
DescriptionColumn level sensor that analyzes all values in a text column and detects the data type of the values. The sensor returns a value that identifies the detected data type of a column: 1 - integers, 2 - floats, 3 - dates, 4 - timestamps, 5 - booleans, 6 - strings, 7 - mixed data types.
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN NULL
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN REGEXP_CONTAINS(SAFE_CAST({{ lib.render_target_column('analyzed_table') }} AS STRING), r"^[-+]?\d+$") IS TRUE
THEN 1
ELSE 0
END
)
THEN 1
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN REGEXP_CONTAINS(SAFE_CAST({{ lib.render_target_column('analyzed_table') }} AS STRING), r"^[+-]?([0-9]*[.])[0-9]+$") IS TRUE
THEN 1
ELSE 0
END
)
THEN 2
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN REGEXP_CONTAINS(SAFE_CAST({{ lib.render_target_column('analyzed_table') }} AS STRING), r"^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4}))$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$") IS TRUE
THEN 1
ELSE 0
END
)
THEN 3
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN REGEXP_CONTAINS(SAFE_CAST({{ lib.render_target_column('analyzed_table') }} AS STRING), r"^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(\b(am|pm|AM|PM)\b)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(\b(am|pm|AM|PM)\b)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(\b(am|pm|AM|PM)\b)?)$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(\b(am|pm|AM|PM)\b)?)$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(\b(am|pm|AM|PM)\b)?)$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(\b(am|pm|AM|PM)\b)?)$") IS TRUE
THEN 1
ELSE 0
END
)
THEN 4
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN REGEXP_CONTAINS(SAFE_CAST({{ lib.render_target_column('analyzed_table') }} AS STRING), r"^(\b(true|false|TRUE|FALSE|yes|no|YES|NO|y|n|Y|N|t|f|T|F)\b)$") IS TRUE
THEN 1
ELSE 0
END
)
THEN 5
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table') }} IS NULL OR
REGEXP_CONTAINS(SAFE_CAST({{ lib.render_target_column('analyzed_table') }} AS STRING), r"^[-+]?\d+$") IS TRUE OR
REGEXP_CONTAINS(SAFE_CAST({{ lib.render_target_column('analyzed_table') }} AS STRING), r"^[+-]?([0-9]*[.])[0-9]+$") IS TRUE OR
REGEXP_CONTAINS(SAFE_CAST({{ lib.render_target_column('analyzed_table') }} AS STRING), r"^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4}))$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$") IS TRUE OR
REGEXP_CONTAINS(SAFE_CAST({{ lib.render_target_column('analyzed_table') }} AS STRING),r"^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(\b(am|pm|AM|PM)\b)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(\b(am|pm|AM|PM)\b)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(\b(am|pm|AM|PM)\b)?)$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(\b(am|pm|AM|PM)\b)?)$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(\b(am|pm|AM|PM)\b)?)$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(\b(am|pm|AM|PM)\b)?)$") IS TRUE OR
REGEXP_CONTAINS(SAFE_CAST({{ lib.render_target_column('analyzed_table') }} AS STRING), r"^(\b(true|false|TRUE|FALSE|yes|no|YES|NO|y|n|Y|N|t|f|T|F)\b)$") IS TRUE
THEN 0
WHEN TRIM(SAFE_CAST({{ lib.render_target_column('analyzed_table') }} AS STRING)) <> ''
THEN 1
ELSE 0
END
)
THEN 6
ELSE 7
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 NULL
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN REGEXP_LIKE({{ lib.render_target_column('analyzed_table') }}, '^[-+]?\\d+$') IS TRUE
THEN 1
ELSE 0
END
)
THEN 1
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN REGEXP_LIKE({{ lib.render_target_column('analyzed_table') }}, '^[+-]?([0-9]*[.])[0-9]+$') IS TRUE
THEN 1
ELSE 0
END
)
THEN 2
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN REGEXP_LIKE({{ lib.render_target_column('analyzed_table') }}, '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4}))$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$') IS TRUE
THEN 1
ELSE 0
END
)
THEN 3
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN REGEXP_LIKE({{ lib.render_target_column('analyzed_table') }}, '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])\\:([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])\\:([0-5][0-9])\\:([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])\\:([0-5][0-9])\\:([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])\\:([0-5][0-9])\\:([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])\\:([0-5][0-9])\\:([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])\\:([0-5][0-9])\\:([0-5][0-9])[\s]?(am|pm|AM|PM)?)$') IS TRUE
THEN 1
ELSE 0
END
)
THEN 4
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN REGEXP_LIKE({{ lib.render_target_column('analyzed_table') }}, '^(true|false|TRUE|FALSE|yes|no|YES|NO|y|n|Y|N|t|f|T|F)$') IS TRUE
THEN 1
ELSE 0
END
)
THEN 5
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table') }} IS NULL OR
REGEXP_LIKE({{ lib.render_target_column('analyzed_table') }}, '^[-+]?\d+$') IS TRUE OR
REGEXP_LIKE({{ lib.render_target_column('analyzed_table') }}, '^[+-]?([0-9]*[.])[0-9]+$') IS TRUE OR
REGEXP_LIKE({{ lib.render_target_column('analyzed_table') }}, '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4}))$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$') IS TRUE OR
REGEXP_LIKE({{ lib.render_target_column('analyzed_table') }}, '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])\\:([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])\\:([0-5][0-9])\\:([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])\\:([0-5][0-9])\\:([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])\\:([0-5][0-9])\\:([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])\\:([0-5][0-9])\\:([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])\\:([0-5][0-9])\\:([0-5][0-9])[\s]?(am|pm|AM|PM)?)$') IS TRUE OR
REGEXP_LIKE({{ lib.render_target_column('analyzed_table') }}, '^(true|false|TRUE|FALSE|yes|no|YES|NO|y|n|Y|N|t|f|T|F)$') IS TRUE
THEN 0
WHEN TRIM({{ lib.render_target_column('analyzed_table') }}) <> ''
THEN 1
ELSE 0
END
)
THEN 6
ELSE 7
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/postgresql.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN NULL
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^[-+]?\d+$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 1
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^[+-]?([0-9]*[.])[0-9]+$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 2
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4}))$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 3
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 4
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^(true|false|TRUE|FALSE|yes|no|YES|NO|y|n|Y|N|t|f|T|F)$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 5
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table') }} IS NULL OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^[-+]?\d+$' IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^[+-]?([0-9]*[.])[0-9]+$' IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4}))$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$' IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$' IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^(true|false|TRUE|FALSE|yes|no|YES|NO|y|n|Y|N|t|f|T|F)$' IS TRUE
THEN 0
WHEN TRIM(CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT)) <> ''
THEN 1
ELSE 0
END
)
THEN 6
ELSE 7
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/redshift.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN NULL
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^[-+]?\d+$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 1
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^[+-]?([0-9]*[.])[0-9]+$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 2
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4}))$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 3
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 4
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^(true|false|TRUE|FALSE|yes|no|YES|NO|y|n|Y|N|t|f|T|F)$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 5
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table') }} IS NULL OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^[-+]?\d+$' IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^[+-]?([0-9]*[.])[0-9]+$' IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4}))$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$' IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$' IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) ~ '^(true|false|TRUE|FALSE|yes|no|YES|NO|y|n|Y|N|t|f|T|F)$' IS TRUE
THEN 0
WHEN TRIM(CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT)) <> ''
THEN 1
ELSE 0
END
)
THEN 6
ELSE 7
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 NULL
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) REGEXP '^[-+]?\d+$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 1
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) REGEXP '^[+-]?([0-9]*[.])[0-9]+$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 2
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) REGEXP '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4}))$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 3
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) REGEXP '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 4
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) REGEXP '^(true|false|TRUE|FALSE|yes|no|YES|NO|y|n|Y|N|t|f|T|F)$' IS TRUE
THEN 1
ELSE 0
END
)
THEN 5
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table') }} IS NULL OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) REGEXP '^[-+]?\d+$' IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) REGEXP '^[+-]?([0-9]*[.])[0-9]+$' IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) REGEXP '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4}))$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$' IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) REGEXP '^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$' IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT) REGEXP '^(true|false|TRUE|FALSE|yes|no|YES|NO|y|n|Y|N|t|f|T|F)$' IS TRUE
THEN 0
WHEN TRIM(CAST({{ lib.render_target_column('analyzed_table') }} AS TEXT)) <> ''
THEN 1
ELSE 0
END
)
THEN 6
ELSE 7
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/postgresql.sql.jinja2' as lib with context -%}
{% macro make_text_constant(string) -%}
{{ '\'' }}{{ string | replace('\'', '\'\'') }}{{ '\'' }}
{%- endmacro %}
{%- macro render_regex(regex) -%}
{{ make_text_constant(regex) }}
{%- endmacro -%}
SELECT
CASE
WHEN COUNT_BIG({{ lib.render_target_column('analyzed_table') }}) = 0 THEN NULL
WHEN COUNT_BIG({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS NVARCHAR(MAX)) LIKE {{ render_regex('^[-+]?\d+$') }} IS TRUE
THEN 1
ELSE 0
END
)
THEN 1
WHEN COUNT_BIG({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS NVARCHAR(MAX)) LIKE {{ render_regex('^[+-]?([0-9]*[.])[0-9]+$') }} IS TRUE
THEN 1
ELSE 0
END
)
THEN 2
WHEN COUNT_BIG({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS NVARCHAR(MAX)) LIKE {{ render_regex('^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4}))$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$') }} IS TRUE
THEN 1
ELSE 0
END
)
THEN 3
WHEN COUNT_BIG({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS NVARCHAR(MAX)) LIKE {{ render_regex('^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$') }} IS TRUE
THEN 1
ELSE 0
END
)
THEN 4
WHEN COUNT_BIG({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN CAST({{ lib.render_target_column('analyzed_table') }} AS NVARCHAR(MAX)) LIKE {{ render_regex('^(true|false|TRUE|FALSE|yes|no|YES|NO|y|n|Y|N|t|f|T|F)$') }} IS TRUE
THEN 1
ELSE 0
END
)
THEN 5
WHEN COUNT_BIG({{ lib.render_target_column('analyzed_table') }}) =
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table') }} IS NULL OR
CAST({{ lib.render_target_column('analyzed_table') }} AS NVARCHAR(MAX)) LIKE {{ render_regex('^[-+]?\d+$') }} IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS NVARCHAR(MAX)) LIKE {{ render_regex('^[+-]?([0-9]*[.])[0-9]+$') }} IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS NVARCHAR(MAX)) LIKE {{ render_regex('^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4}))$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4}))$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01]))$') }} IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS NVARCHAR(MAX)) LIKE {{ render_regex('^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[/](0[1-9]|1[0-2])[/](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[-](0[1-9]|1[0-2])[-](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((0[1-9]|[1][0-9]|[2][0-9]|3[01])[.](0[1-9]|1[0-2])[.](\d{4})[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[/](0[1-9]|1[0-2])[/](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[-](0[1-9]|1[0-2])[-](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$|^((\d{4})[.](0[1-9]|1[0-2])[.](0[1-9]|[1][0-9]|[2][0-9]|3[01])[\s]([0]|[00]|2[0-3]|[01][0-9])[:]([0-5][0-9])[:]([0-5][0-9])[\s]?(am|pm|AM|PM)?)$') }} IS TRUE OR
CAST({{ lib.render_target_column('analyzed_table') }} AS NVARCHAR(MAX)) LIKE {{ render_regex('^(true|false|TRUE|FALSE|yes|no|YES|NO|y|n|Y|N|t|f|T|F)$') }} IS TRUE
THEN 0
WHEN TRIM(CAST({{ lib.render_target_column('analyzed_table') }} AS NVARCHAR(MAX))) <> ''
THEN 1
ELSE 0
END
)
THEN 6
ELSE 7
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() -}}
string empty count
Full sensor name
DescriptionColumn level sensor that calculates the number of rows with an empty string.
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
{% macro render_column_cast_to_string(analyzed_table_to_render) -%}
{%- if (lib.target_column_data_type == 'STRING') -%}
{{ lib.render_target_column(analyzed_table_to_render) }}
{%- elif (lib.target_column_data_type == 'BIGNUMERIC') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DECIMAL') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BIGDECIMAL') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'FLOAT64') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INT64') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'NUMERIC') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'SMALLINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INTEGER') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BIGINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TINYINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BYTEINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DATE') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DATETIME') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TIME') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TIMESTAMP') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BOOLEAN') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- else -%}
{{ lib.render_target_column(analyzed_table_to_render) }}
{%- endif -%}
{% endmacro -%}
SELECT
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
AND {{ render_column_cast_to_string('analyzed_table')}} = ''
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 {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
AND {{ lib.render_target_column('analyzed_table')}} = ''
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 {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
AND {{ lib.render_target_column('analyzed_table')}} = ''
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
{{- lib.render_where_clause(table_alias_prefix='original_table') }}) analyzed_table
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
AND LENGTH({{ 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/redshift.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
AND LENGTH({{ 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
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
AND LENGTH({{ 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
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
AND LEN({{ 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() -}}
string empty percent
Full sensor name
DescriptionColumn level sensor that calculates the percentage of rows with an empty string.
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
{% macro render_column_cast_to_string(analyzed_table_to_render) -%}
{%- if (lib.target_column_data_type == 'STRING') -%}
{{ lib.render_target_column(analyzed_table_to_render) }}
{%- elif (lib.target_column_data_type == 'BIGNUMERIC') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DECIMAL') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BIGDECIMAL') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'FLOAT64') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INT64') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'NUMERIC') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'SMALLINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INTEGER') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BIGINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TINYINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BYTEINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DATE') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DATETIME') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TIME') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TIMESTAMP') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BOOLEAN') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- else -%}
{{ lib.render_target_column(analyzed_table_to_render) }}
{%- endif -%}
{% endmacro -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
AND {{ render_column_cast_to_string('analyzed_table')}} = ''
THEN 1
ELSE 0
END
) / COUNT(*)
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(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
AND {{ lib.render_target_column('analyzed_table')}} = ''
THEN 1
ELSE 0
END
) / COUNT(*)
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(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
AND {{ lib.render_target_column('analyzed_table')}} = ''
THEN 1
ELSE 0
END
) / COUNT(*)
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
{{- lib.render_where_clause(table_alias_prefix='original_table') }}) analyzed_table
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
AND LENGTH({{ lib.render_target_column('analyzed_table')}}) = 0
THEN 1
ELSE 0
END
) / COUNT(*)
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/redshift.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
AND LENGTH({{ lib.render_target_column('analyzed_table')}}) = 0
THEN 1
ELSE 0
END
) / COUNT(*)
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(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
AND LENGTH({{ lib.render_target_column('analyzed_table')}}) = 0
THEN 1
ELSE 0
END
) / COUNT(*)
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(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
AND LEN({{ lib.render_target_column('analyzed_table')}}) = 0
THEN 1
ELSE 0
END
) / COUNT_BIG(*)
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() -}}
string invalid email count
Full sensor name
DescriptionColumn level sensor that calculates the number of rows with an invalid emails value in a column.
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN REGEXP_CONTAINS(CAST({{ lib.render_target_column('analyzed_table') }} AS STRING), r"^[A-Za-z]+[A-Za-z0-9.]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$")
THEN 0
ELSE 1
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 REGEXP_LIKE({{ lib.render_target_column('analyzed_table') }}, '^[A-Za-z]+[A-Za-z0-9.]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$')
THEN 0
ELSE 1
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/postgresql.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN {{lib.render_target_column('analyzed_table')}} !~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)*$'
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/redshift.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN {{lib.render_target_column('analyzed_table')}} !~ '^([a-z0-9_\.-]+)@([\da-z\.-]+)\.([a-z]{2,6})$'
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 {{ lib.render_target_column('analyzed_table') }} REGEXP '^[A-Za-z]+[A-Za-z0-9.]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'
THEN 0
ELSE 1
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 {{ lib.render_target_column('analyzed_table') }} LIKE '%_@__%.__%' AND PATINDEX('%[^a-z,0-9,@,.,_]%', REPLACE({{ lib.render_target_column('analyzed_table') }}, '-', 'a')) = 0
THEN 0
ELSE 1
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() -}}
string invalid ip4 address count
Full sensor name
DescriptionColumn level sensor that calculates the number of rows with an invalid IP4 address value in a column.
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN REGEXP_CONTAINS(CAST({{ lib.render_target_column('analyzed_table') }} AS STRING), r"^((25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[0-9][0-9]|[0-9])[.]){3}(25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[0-9][0-9]|[0-9])$")
THEN 0
ELSE 1
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 REGEXP_LIKE({{ lib.render_target_column('analyzed_table') }}, '^((25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[0-9][0-9]|[0-9])[.]){3}(25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[0-9][0-9]|[0-9])$')
THEN 0
ELSE 1
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/postgresql.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} ~ '^((25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[0-9][0-9]|[0-9])[.]){3}(25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[0-9][0-9]|[0-9])$'
THEN 0
ELSE 1
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/redshift.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} ~ '^((25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[0-9][0-9]|[0-9])[.]){3}(25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[0-9][0-9]|[0-9])$'
THEN 0
ELSE 1
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 {{ lib.render_target_column('analyzed_table') }} REGEXP '^((25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[0-9][0-9]|[0-9])[.]){3}(25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[0-9][0-9]|[0-9])$'
THEN 0
ELSE 1
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 ({{ lib.render_target_column('analyzed_table') }} LIKE '[0-9].[0-9].[0-9].[0-9]'
OR {{ lib.render_target_column('analyzed_table') }} LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9]'
OR {{ lib.render_target_column('analyzed_table') }} LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9]')
AND (PATINDEX('%[^0-9.]%', {{ lib.render_target_column('analyzed_table') }}) = 0
OR PATINDEX('%[^0-9.]%', {{ lib.render_target_column('analyzed_table') }}) = 0
OR PATINDEX('%[^0-9.]%', {{ lib.render_target_column('analyzed_table') }}) = 0)
THEN 0
ELSE 1
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() -}}
string invalid ip6 address count
Full sensor name
DescriptionColumn level sensor that calculates the number of rows with an invalid IP6 address value in a column.
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN REGEXP_CONTAINS(CAST({{ lib.render_target_column('analyzed_table') }} AS STRING), r"^(([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,7}:|([0-9a-fA-F]{1,4}:){1,6}:[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,5}(:[0-9a-fA-F]{1,4}){1,2}|([0-9a-fA-F]{1,4}:){1,4}(:[0-9a-fA-F]{1,4}){1,3}|([0-9a-fA-F]{1,4}:){1,3}(:[0-9a-fA-F]{1,4}){1,4}|([0-9a-fA-F]{1,4}:){1,2}(:[0-9a-fA-F]{1,4}){1,5}|[0-9a-fA-F]{1,4}:((:[0-9a-fA-F]{1,4}){1,6})|:((:[0-9a-fA-F]{1,4}){1,7}|:)|fe80:(:[0-9a-fA-F]{0,4}){0,4}%[0-9a-zA-Z]{1,}|::(ffff(:0{1,4}){0,1}:){0,1}((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])|([0-9a-fA-F]{1,4}:){1,4}:((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9]))$")
THEN 0
ELSE 1
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 REGEXP_LIKE({{ lib.render_target_column('analyzed_table') }}, '(([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,7}:|([0-9a-fA-F]{1,4}:){1,6}:[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,5}(:[0-9a-fA-F]{1,4}){1,2}|([0-9a-fA-F]{1,4}:){1,4}(:[0-9a-fA-F]{1,4}){1,3}|([0-9a-fA-F]{1,4}:){1,3}(:[0-9a-fA-F]{1,4}){1,4}|([0-9a-fA-F]{1,4}:){1,2}(:[0-9a-fA-F]{1,4}){1,5}|[0-9a-fA-F]{1,4}:((:[0-9a-fA-F]{1,4}){1,6})|:((:[0-9a-fA-F]{1,4}){1,7}|:)|fe80:(:[0-9a-fA-F]{0,4}){0,4}%[0-9a-zA-Z]{1,}|::(ffff(:0{1,4}){0,1}:){0,1}((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])|([0-9a-fA-F]{1,4}:){1,4}:((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9]))')
THEN 0
ELSE 1
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/postgresql.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} ~ '^(([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,7}:|([0-9a-fA-F]{1,4}:){1,6}:[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,5}(:[0-9a-fA-F]{1,4}){1,2}|([0-9a-fA-F]{1,4}:){1,4}(:[0-9a-fA-F]{1,4}){1,3}|([0-9a-fA-F]{1,4}:){1,3}(:[0-9a-fA-F]{1,4}){1,4}|([0-9a-fA-F]{1,4}:){1,2}(:[0-9a-fA-F]{1,4}){1,5}|[0-9a-fA-F]{1,4}:((:[0-9a-fA-F]{1,4}){1,6})|:((:[0-9a-fA-F]{1,4}){1,7}|:)|fe80:(:[0-9a-fA-F]{0,4}){0,4}%[0-9a-zA-Z]{1,}|::(ffff(:0{1,4}){0,1}:){0,1}((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])|([0-9a-fA-F]{1,4}:){1,4}:((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9]))$'
THEN 0
ELSE 1
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/redshift.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} ~ '^(([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,7}:|([0-9a-fA-F]{1,4}:){1,6}:[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,5}(:[0-9a-fA-F]{1,4}){1,2}|([0-9a-fA-F]{1,4}:){1,4}(:[0-9a-fA-F]{1,4}){1,3}|([0-9a-fA-F]{1,4}:){1,3}(:[0-9a-fA-F]{1,4}){1,4}|([0-9a-fA-F]{1,4}:){1,2}(:[0-9a-fA-F]{1,4}){1,5}|[0-9a-fA-F]{1,4}:((:[0-9a-fA-F]{1,4}){1,6})|:((:[0-9a-fA-F]{1,4}){1,7}|:)|fe80:(:[0-9a-fA-F]{0,4}){0,4}%[0-9a-zA-Z]{1,}|::(ffff(:0{1,4}){0,1}:){0,1}((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])|([0-9a-fA-F]{1,4}:){1,4}:((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9]))$'
THEN 0
ELSE 1
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 ({{ lib.render_target_column('analyzed_table') }} REGEXP '^(([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,7}:|([0-9a-fA-F]{1,4}:){1,6}:[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,5}(:[0-9a-fA-F]{1,4}){1,2}|([0-9a-fA-F]{1,4}:){1,4}(:[0-9a-fA-F]{1,4}){1,3}|([0-9a-fA-F]{1,4}:){1,3}(:[0-9a-fA-F]{1,4}){1,4}|([0-9a-fA-F]{1,4}:){1,2}(:[0-9a-fA-F]{1,4}){1,5}|[0-9a-fA-F]{1,4}:((:[0-9a-fA-F]{1,4}){1,6})|:((:[0-9a-fA-F]{1,4}){1,7}|:)|fe80:(:[0-9a-fA-F]{0,4}){0,4}%[0-9a-zA-Z]{1,}|::(ffff(:0{1,4}){0,1}:){0,1}((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])|([0-9a-fA-F]{1,4}:){1,4}:((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9]))$')
THEN 0
ELSE 1
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 {{ lib.render_target_column('analyzed_table') }} LIKE '[0-9a-fA-F]{1,4}:[0-9a-fA-F]{1,4}:[0-9a-fA-F]{1,4}:[0-9a-fA-F]{1,4}:[0-9a-fA-F]{1,4}:[0-9a-fA-F]{1,4}:[0-9a-fA-F]{1,4}:[0-9a-fA-F]{1,4}'
THEN 0
ELSE 1
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() -}}
string invalid uuid count
Full sensor name
DescriptionColumn level sensor that calculates the number of rows with an invalid uuid value in a column.
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN REGEXP_CONTAINS(CAST({{ lib.render_target_column('analyzed_table') }} AS STRING), r"^[0-9a-fA-F]{8}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{12}$")
THEN 0
ELSE 1
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 REGEXP_LIKE({{ lib.render_target_column('analyzed_table') }}, '^[0-9a-fA-F]{8}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{12}$')
THEN 0
ELSE 1
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/postgresql.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} ~ '^[0-9a-fA-F]{8}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{12}$'
THEN 0
ELSE 1
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/redshift.sql.jinja2' as lib with context -%}
SELECT
SUM(
CASE
WHEN {{ lib.render_target_column('analyzed_table')}} ~ '^[0-9a-fA-F]{8}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{12}$'
THEN 0
ELSE 1
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 {{ lib.render_target_column('analyzed_table') }} REGEXP '^[0-9a-fA-F]{8}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{4}[\s-]?[0-9a-fA-F]{12}$'
THEN 0
ELSE 1
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 TRY_CONVERT(UNIQUEIDENTIFIER,{{ lib.render_target_column('analyzed_table') }}) IS NULL
THEN 0
ELSE 1
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() -}}
string length above max length count
Full sensor name
DescriptionColumn level sensor that calculates the count of values that are longer than a given length in a column.
Parameters
| Field name | Description | Allowed data type | Is it 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 |
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
{% macro render_column_cast_to_string(analyzed_table_to_render) -%}
{%- if (lib.target_column_data_type == 'STRING') -%}
{{ lib.render_target_column(analyzed_table_to_render) }}
{%- elif (lib.target_column_data_type == 'BIGNUMERIC') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DECIMAL') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BIGDECIMAL') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'FLOAT64') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INT64') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'NUMERIC') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'SMALLINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INTEGER') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BIGINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TINYINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BYTEINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DATE') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DATETIME') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TIME') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TIMESTAMP') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BOOLEAN') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- else -%}
{{ lib.render_target_column(analyzed_table_to_render) }}
{%- endif -%}
{% endmacro -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ 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/mysql.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('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
{{- lib.render_where_clause(table_alias_prefix='original_table') }}) analyzed_table
{{- 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')}}) >= {{(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/redshift.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('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/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() -}}
string length above max length percent
Full sensor name
DescriptionColumn level sensor that calculates the percentage of values that are longer than a given length in a column.
Parameters
| Field name | Description | Allowed data type | Is it 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 |
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
{% macro render_column_cast_to_string(analyzed_table_to_render) -%}
{%- if (lib.target_column_data_type == 'STRING') -%}
{{ lib.render_target_column(analyzed_table_to_render) }}
{%- elif (lib.target_column_data_type == 'BIGNUMERIC') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DECIMAL') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BIGDECIMAL') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'FLOAT64') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INT64') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'NUMERIC') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'SMALLINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INTEGER') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BIGINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TINYINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BYTEINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DATE') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DATETIME') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TIME') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TIMESTAMP') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BOOLEAN') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- else -%}
{{ lib.render_target_column(analyzed_table_to_render) }}
{%- endif -%}
{% endmacro -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ render_column_cast_to_string('analyzed_table')}}) >= {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT(*)
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(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}) >= {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT(*)
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(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}) >= {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT(*)
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
{{- lib.render_where_clause(table_alias_prefix='original_table') }}) analyzed_table
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}) >= {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT(*)
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/redshift.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}) >= {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT(*)
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(*) = 0 THEN 100.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(*)
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(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LEN({{ lib.render_target_column('analyzed_table')}}) >= {{(parameters.max_length)}}
THEN 1
ELSE 0
END
)/ COUNT_BIG(*)
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() -}}
string length below min length count
Full sensor name
DescriptionColumn level sensor that calculates the count of values that are shorter than a given length in a column.
Parameters
| Field name | Description | Allowed data type | Is it 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 |
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
{% macro render_column_cast_to_string(analyzed_table_to_render) -%}
{%- if (lib.target_column_data_type == 'STRING') -%}
{{ lib.render_target_column(analyzed_table_to_render) }}
{%- elif (lib.target_column_data_type == 'BIGNUMERIC') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DECIMAL') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BIGDECIMAL') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'FLOAT64') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INT64') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'NUMERIC') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'SMALLINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INTEGER') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BIGINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TINYINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BYTEINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DATE') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DATETIME') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TIME') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TIMESTAMP') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BOOLEAN') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- else -%}
{{ lib.render_target_column(analyzed_table_to_render) }}
{%- endif -%}
{% endmacro -%}
SELECT
SUM(
CASE
WHEN LENGTH({{ 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/mysql.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('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
{{- lib.render_where_clause(table_alias_prefix='original_table') }}) analyzed_table
{{- 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')}}) <= {{(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/redshift.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('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/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() -}}
string length below min length percent
Full sensor name
DescriptionColumn level sensor that calculates the percentage of values that are shorter than a given length in a column.
Parameters
| Field name | Description | Allowed data type | Is it 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 |
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
{% macro render_column_cast_to_string(analyzed_table_to_render) -%}
{%- if (lib.target_column_data_type == 'STRING') -%}
{{ lib.render_target_column(analyzed_table_to_render) }}
{%- elif (lib.target_column_data_type == 'BIGNUMERIC') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DECIMAL') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BIGDECIMAL') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'FLOAT64') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INT64') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'NUMERIC') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'SMALLINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INTEGER') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BIGINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TINYINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BYTEINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DATE') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DATETIME') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TIME') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TIMESTAMP') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BOOLEAN') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- else -%}
{{ lib.render_target_column(analyzed_table_to_render) }}
{%- endif -%}
{% endmacro -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ render_column_cast_to_string('analyzed_table')}}) <= {{(parameters.min_length)}}
THEN 1
ELSE 0
END
)/ COUNT(*)
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(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}) <= {{(parameters.min_length)}}
THEN 1
ELSE 0
END
)/ COUNT(*)
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(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}) <= {{(parameters.min_length)}}
THEN 1
ELSE 0
END
)/ COUNT(*)
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
{{- lib.render_where_clause(table_alias_prefix='original_table') }}) analyzed_table
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}) <= {{(parameters.min_length)}}
THEN 1
ELSE 0
END
)/ COUNT(*)
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/redshift.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LENGTH({{ lib.render_target_column('analyzed_table')}}) <= {{(parameters.min_length)}}
THEN 1
ELSE 0
END
)/ COUNT(*)
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(*) = 0 THEN 100.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(*)
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(*) = 0 THEN 100.0
ELSE 100.0 * SUM(
CASE
WHEN LEN({{ lib.render_target_column('analyzed_table')}}) <= {{(parameters.min_length)}}
THEN 1
ELSE 0
END
)/ COUNT_BIG(*)
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() -}}
string length in range percent
Full sensor name
DescriptionColumn level sensor that calculates percentage of strings with a length below the indicated length in a column.
Parameters
| Field name | Description | Allowed data type | Is it required? | Allowed values |
|---|---|---|---|---|
| min_length | Sets a minimal string length | integer | ||
| max_length | Sets a maximal string length. | integer |
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
{% macro render_column_cast_to_string(analyzed_table_to_render) -%}
{%- if (lib.target_column_data_type == 'STRING') -%}
{{ lib.render_target_column(analyzed_table_to_render) }}
{%- elif (lib.target_column_data_type == 'BIGNUMERIC') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'DECIMAL') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BIGDECIMAL') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'FLOAT64') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INT64') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'NUMERIC') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'SMALLINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'INTEGER') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BIGINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'TINYINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- elif (lib.target_column_data_type == 'BYTEINT') -%}
SAFE_CAST({{ lib.render_target_column(analyzed_table_to_render) }} AS STRING)
{%- else -%}
{{ lib.render_target_column(analyzed_table_to_render) }}
{%- endif -%}
{% endmacro -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN NULL
ELSE
100.0 * SUM(
CASE
WHEN LENGTH( {{ 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/mysql.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN NULL
ELSE
100.0 * SUM(
CASE
WHEN LENGTH( {{ lib.render_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/oracle.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN NULL
ELSE
100.0 * SUM(
CASE
WHEN LENGTH( {{ lib.render_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
{{- lib.render_where_clause(table_alias_prefix='original_table') }}) analyzed_table
{{- 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 NULL
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/redshift.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN NULL
ELSE
100.0 * SUM(
CASE
WHEN LENGTH( {{ lib.render_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/snowflake.sql.jinja2' as lib with context -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN NULL
ELSE
100.0 * SUM(
CASE
WHEN LENGTH( {{ lib.render_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 NULL
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_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() -}}
string match date regex percent
Full sensor name
DescriptionColumn level sensor that calculates the percentage of values that does fit a given date regex in a column.
Parameters
| Field name | Description | Allowed data type | Is it required? | Allowed values |
|---|---|---|---|---|
| date_formats | Desired date format. Sensor will try to parse the column records and cast the data using this format. | enum | YYYY-MM-DD DD/MM/YYYY Month D, YYYY YYYY/MM/DD MM/DD/YYYY |
SQL Template (Jinja2)
{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
{% macro render_date_formats(date_formats) %}
{%- if date_formats == 'YYYY-MM-DD'-%}
'%Y-%m-%d'
{%- elif date_formats == 'MM/DD/YYYY' -%}
'%m/%d/%Y'
{%- elif date_formats == 'DD/MM/YYYY' -%}
'%d/%m/%Y'
{%- elif date_formats == 'YYYY/MM/DD'-%}
'%Y/%m/%d'
{%- elif date_formats == 'Month D, YYYY'-%}
'%b %d, %Y'
{%- endif -%}
{% endmacro -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN NULL
ELSE 100.0 * SUM(
CASE
WHEN SAFE.PARSE_DATE({{render_date_formats(parameters.date_formats)}}, {{ lib.render_target_column('analyzed_table') }}) IS NOT NULL
THEN 1
ELSE 0
END
) / COUNT(*)
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 -%}
{% macro render_date_formats(date_formats) %}
{%- if date_formats == 'YYYY-MM-DD'-%}
'%Y-%m-%d'
{%- elif date_formats == 'MM/DD/YYYY' -%}
'%m/%d/%Y'
{%- elif date_formats == 'DD/MM/YYYY' -%}
'%d/%m/%Y'
{%- elif date_formats == 'YYYY/MM/DD'-%}
'%Y/%m/%d'
{%- elif date_formats == 'Month D, YYYY'-%}
'%b %d, %Y'
{%- endif -%}
{% endmacro -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN NULL
ELSE 100.0 * SUM(
CASE
WHEN STR_TO_DATE({{ lib.render_target_column('analyzed_table') }}, {{render_date_formats(parameters.date_formats)}}) IS NOT NULL
THEN 1
ELSE 0
END
) / COUNT(*)
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/postgresql.sql.jinja2' as lib with context -%}
{% macro render_date_formats(date_formats) %}
{%- if date_formats == 'YYYY-MM-DD'-%}
'%YYYY-%MM-%DD'
{%- elif date_formats == 'MM/DD/YYYY' -%}
'%MM/%DD/%YYYY'
{%- elif date_formats == 'DD/MM/YYYY' -%}
'%DD/%MM/%YYYY'
{%- elif date_formats == 'YYYY/MM/DD'-%}
'%YYYY/%MM/%DD'
{%- elif date_formats == 'Month D, YYYY'-%}
'%Month %DD,%YYYY'
{%- endif -%}
{% endmacro -%}
SELECT
CASE
WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN NULL
ELSE 100.0 * SUM(
CASE
WHEN TO_DATE({{ lib.render_target_column('analyzed_table') }}::VARCHAR, {{render_date_formats(parameters.date_formats)}}) IS NOT NULL
THEN 1
ELSE 0
END
) / COUNT(*)
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() -}}