Date type percent

The query for this sensor calculates the percentage of records that can be interpreted as dates. It performed by casting values as dates, parsing them with a certain date format and casting as float.

The records that can be casted or parsed successfully are assigned value of 1, and any other values, 0. Those values are then summed (so the counting of valid values is effectively performed), divided by the number of records, and multiplicated by a 100.0 so that the results is in percent.

---

Jinja Template

BigQuerySnowflake

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    100.0 * SUM(
        CASE
            WHEN SAFE_CAST({{ lib.render_target_column('analyzed_table') }} AS FLOAT64) IS NOT NULL
            OR SAFE_CAST({{ lib.render_target_column('analyzed_table') }} AS DATE) IS NOT NULL
            OR SAFE.PARSE_DATE({{ lib.render_date_format(parameters.named_date_format, parameters.custom_date_format) }}, {{ lib.render_target_column('analyzed_table') }}) IS NOT NULL THEN 1
            ELSE 0
        END
    ) / COUNT(*) AS actual_value
    {{- lib.render_dimension_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
SELECT
    100.0 * SUM(
        CASE
            WHEN TRY_CAST({{ lib.render_target_column('analyzed_table') }} AS FLOAT) IS NOT NULL
            OR TRY_CAST({{ lib.render_target_column('analyzed_table') }} AS DATE) IS NOT NULL
            OR TRY_TO_DATE({{ lib.render_target_column('analyzed_table') }}, {{ lib.render_date_format(parameters.named_date_format, custom_date_format) }}) IS NOT NULL THEN 1
            ELSE 0
        END
    ) / COUNT(*) AS actual_value
    {{- lib.render_dimension_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}