Last updated: July 22, 2025

DQOps data quality custom sql sensors, SQL examples

All data quality sensors in the custom sql category supported by DQOps are listed below. Those sensors are measured on a column level.

---

import custom result

Column level sensor that uses a custom SQL SELECT statement to retrieve a result of running a custom data quality check on a column by a custom data quality check, hardcoded in the data pipeline. The result is retrieved by querying a separate logging table, whose schema is not fixed. The logging table should have columns that identify a table and a column for which they store custom data quality check results, and a severity column of the data quality issue. The SQL query that is configured in this external data quality results importer must be a complete SELECT statement that queries a dedicated logging table, created by the data engineering team.

Sensor summary

The import custom result sensor is documented below.

Target	Category	Full sensor name	Source code on GitHub
column	custom_sql	column/custom_sql/import_custom_result	sensors/column/custom_sql

Sensor parameters

Field name	Description	Allowed data type	Required	Allowed values
sql_query	A custom SELECT statement that queries a logging table with custom results of data quality checks executed by the data pipeline. The query must return a result column named severity. The values of the severity column must be: 0 - data quality check passed, 1 - warning issue, 2 - error severity issue, 3 - fatal severity issue. The query can return actual_value and expected_value results that are imported into DQOps data lake. The query can use a {table_name} placeholder that is replaced with a table name for which the results are imported, and a {column_name} placeholder replaced with the column name.	string

Jinja2 SQL templates

The templates used to generate the SQL query for each data source supported by DQOps is shown below.

BigQueryClickHouseDatabricksDB2DuckDBHANAMariaDBMySQLOraclePostgreSQLPrestoQuestDBRedshiftSnowflakeSparkSQL ServerTeradataTrino

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/clickhouse.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/databricks.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/db2.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/hana.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/mysql.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/oracle.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/presto.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/spark.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/teradata.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

{% import '/dialects/trino.sql.jinja2' as lib with context -%}
{{ parameters.sql_query | replace('{table_name}', target_table.table_name)
                        | replace('{schema_name}', target_table.schema_name)
                        | replace('{column_name}', column_name) }}

---

sql aggregated expression

Column level sensor that executes a given SQL expression on a column.

Sensor summary

The sql aggregated expression sensor is documented below.

Target	Category	Full sensor name	Source code on GitHub
column	custom_sql	column/custom_sql/sql_aggregated_expression	sensors/column/custom_sql

Sensor parameters

Field name	Description	Allowed data type	Required	Allowed values
sql_expression	SQL aggregate expression that returns a numeric value calculated from rows. The expression is evaluated on a whole table or within a GROUP BY clause for daily partitions and/or data groups. The expression can use {table} and {column} placeholder that are replaced with a full table name and the analyzed column name.	string

Jinja2 SQL templates

The templates used to generate the SQL query for each data source supported by DQOps is shown below.

BigQueryClickHouseDatabricksDB2DuckDBHANAMariaDBMySQLOraclePostgreSQLPrestoQuestDBRedshiftSnowflakeSparkSQL ServerTeradataTrino

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/clickhouse.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/databricks.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/db2.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/hana.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/mysql.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/oracle.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/presto.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/spark.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/teradata.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/trino.sql.jinja2' as lib with context -%}
SELECT
    ({{ parameters.sql_expression | replace('{column}', lib.render_target_column('analyzed_table')) |
        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}) AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

---

sql condition failed count

Column level sensor that uses a custom SQL condition (an SQL expression that returns a boolean value) to count rows that do not meet the condition.

Sensor summary

The sql condition failed count sensor is documented below.

Target	Category	Full sensor name	Source code on GitHub
column	custom_sql	column/custom_sql/sql_condition_failed_count	sensors/column/custom_sql

Sensor parameters

Field name	Description	Allowed data type	Required	Allowed values
sql_condition	SQL condition (expression) that returns true or false. The condition is evaluated for each row. The expression can use {table} and {column} placeholder that are replaced with a full table name and the analyzed column name.	string

Jinja2 SQL templates

The templates used to generate the SQL query for each data source supported by DQOps is shown below.

BigQueryClickHouseDatabricksDB2DuckDBHANAMariaDBMySQLOraclePostgreSQLPrestoQuestDBRedshiftSnowflakeSparkSQL ServerTeradataTrino

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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/clickhouse.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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/databricks.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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/db2.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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/hana.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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 NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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 NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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
) 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')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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/presto.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
    COALESCE(SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                THEN 1
            ELSE 0
        END
    ), 0) AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) 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 NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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/snowflake.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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/spark.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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/sqlserver.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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/teradata.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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/trino.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', '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
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

---

sql condition failed percent

Column level sensor that uses a custom SQL condition (an SQL expression that returns a boolean value) to count the percentage of rows that do not meet the condition.

Sensor summary

The sql condition failed percent sensor is documented below.

Target	Category	Full sensor name	Source code on GitHub
column	custom_sql	column/custom_sql/sql_condition_failed_percent	sensors/column/custom_sql

Sensor parameters

Field name	Description	Allowed data type	Required	Allowed values
sql_condition	SQL condition (expression) that returns true or false. The condition is evaluated for each row. The expression can use {table} and {column} placeholder that are replaced with a full table name and the analyzed column name.	string

Jinja2 SQL templates

The templates used to generate the SQL query for each data source supported by DQOps is shown below.

BigQueryClickHouseDatabricksDB2DuckDBHANAMariaDBMySQLOraclePostgreSQLPrestoQuestDBRedshiftSnowflakeSparkSQL ServerTeradataTrino

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/clickhouse.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/databricks.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/db2.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/hana.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/mysql.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/oracle.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/presto.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE CAST(100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) AS DOUBLE) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
    COALESCE(100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}}), 100.0)
    AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/spark.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}

SELECT
    CASE
        WHEN COUNT_BIG({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT_BIG({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/teradata.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/trino.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT ({{ lib.render_target_column('analyzed_table')}}) = 0 THEN 100.0
        ELSE CAST(100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
                AND NOT ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                            replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) AS DOUBLE) / COUNT({{ lib.render_target_column('analyzed_table')}})
    END AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

---

sql condition passed count

Column level sensor that uses a custom SQL condition (an SQL expression that returns a boolean value) to count rows that meet the condition.

Sensor summary

The sql condition passed count sensor is documented below.

Target	Category	Full sensor name	Source code on GitHub
column	custom_sql	column/custom_sql/sql_condition_passed_count	sensors/column/custom_sql

Sensor parameters

Field name	Description	Allowed data type	Required	Allowed values
sql_condition	SQL condition (expression) that returns true or false. The condition is evaluated for each row. The expression can use {table} and {column} placeholder that are replaced with a full table name and the analyzed column name.	string

Jinja2 SQL templates

The templates used to generate the SQL query for each data source supported by DQOps is shown below.

BigQueryClickHouseDatabricksDB2DuckDBHANAMariaDBMySQLOraclePostgreSQLPrestoQuestDBRedshiftSnowflakeSparkSQL ServerTeradataTrino

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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/clickhouse.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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/databricks.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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/db2.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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/hana.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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 ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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 ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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
) 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')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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/presto.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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
) 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 ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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/snowflake.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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/spark.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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/sqlserver.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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/teradata.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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/trino.sql.jinja2' as lib with context -%}
SELECT
    SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table')}} IS NOT NULL
            AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                    replace('{table}', lib.render_target_table()) | replace('{alias}', '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
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

---

sql condition passed percent

Column level sensor that uses a custom SQL condition (an SQL expression that returns a boolean value) to count the percentage of rows that meet the condition.

Sensor summary

The sql condition passed percent sensor is documented below.

Target	Category	Full sensor name	Source code on GitHub
column	custom_sql	column/custom_sql/sql_condition_passed_percent	sensors/column/custom_sql

Sensor parameters

Field name	Description	Allowed data type	Required	Allowed values
sql_condition	SQL condition (expression) that returns true or false. The condition is evaluated for each row. The expression can use {table} and {column} placeholder that are replaced with a full table name and the analyzed column name.	string

Jinja2 SQL templates

The templates used to generate the SQL query for each data source supported by DQOps is shown below.

BigQueryClickHouseDatabricksDB2DuckDBHANAMariaDBMySQLOraclePostgreSQLPrestoQuestDBRedshiftSnowflakeSparkSQL ServerTeradataTrino

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/clickhouse.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/databricks.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/db2.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/hana.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/mysql.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/oracle.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/presto.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE CAST(100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) AS DOUBLE) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
    COALESCE(100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }}), 100.0)
    AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/spark.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT_BIG({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT_BIG({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/teradata.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE 100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/trino.sql.jinja2' as lib with context -%}
SELECT
    CASE
        WHEN COUNT({{ lib.render_target_column('analyzed_table') }}) = 0 THEN 100.0
        ELSE CAST(100.0 * SUM(
            CASE
                WHEN {{ lib.render_target_column('analyzed_table') }} IS NOT NULL
                AND ({{ parameters.sql_condition | replace('{column}', lib.render_target_column('analyzed_table')) |
                        replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }})
                    THEN 1
                ELSE 0
            END
        ) AS DOUBLE) / COUNT({{ lib.render_target_column('analyzed_table') }})
    END AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

---

sql invalid value count

Column level sensor that uses a custom SQL query to count rows of invalid values.

Sensor summary

The sql invalid value count sensor is documented below.

Target	Category	Full sensor name	Source code on GitHub
column	custom_sql	column/custom_sql/sql_invalid_value_count	sensors/column/custom_sql

Sensor parameters

Field name	Description	Allowed data type	Required	Allowed values
sql_query	SQL query that returns invalid values. The expression can use {table} and {column} placeholder that are replaced with a full table name and the analyzed column name.	string

Jinja2 SQL templates

The templates used to generate the SQL query for each data source supported by DQOps is shown below.

BigQueryClickHouseDatabricksDB2DuckDBHANAMariaDBMySQLOraclePostgreSQLPrestoQuestDBRedshiftSnowflakeSparkSQL ServerTeradataTrino

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/clickhouse.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/databricks.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/db2.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/hana.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/mysql.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/oracle.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) analyzed_table

{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/presto.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/spark.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/teradata.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

{% import '/dialects/trino.sql.jinja2' as lib with context -%}
SELECT
    COUNT(*) as actual_value
FROM (
    {{ parameters.sql_query | replace('{column}', lib.render_target_column('analyzed_table')) | replace('{table}', lib.render_target_table()) | replace('{alias}', 'analyzed_table') }}
) AS analyzed_table

---

What's next

• Learn how the data quality sensors are defined in DQOps and what is the definition of all Jinja2 macros used in the templates
• Understand how DQOps runs data quality checks, rendering templates to SQL queries