Skip to content

Last updated: July 22, 2025

DQOps data quality accuracy sensors, SQL examples

All data quality sensors in the accuracy category supported by DQOps are listed below. Those sensors are measured on a table level.


total row count match percent

Table level sensor that calculates the percentage of the difference of the total row count of all rows in the tested table and the total row count of the other (reference) table.

Sensor summary

The total row count match percent sensor is documented below.

Target Category Full sensor name Source code on GitHub
table accuracy table/accuracy/total_row_count_match_percent sensors/table/accuracy

Sensor parameters

Field name Description Allowed data type Required Allowed values
referenced_table The name of the reference table. DQOps accepts the name in two forms: a fully qualified name including the schema name, for example landing_zone.customer_raw, or only a table name. When only a table name is used, DQOps assumes that the table is in the same schema as the analyzed table, and prefixes the name with the schema and optionally database name. string

Jinja2 SQL templates

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

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

{%- macro render_referenced_table(referenced_table) -%}
{%- if referenced_table.find(".") < 0 -%}
   {{ lib.quote_identifier(lib.macro_project_name) }}.{{ lib.quote_identifier(lib.macro_schema_name) }}.{{- lib.quote_identifier(referenced_table) -}}
{%- else -%}
   {{ referenced_table }}
{%- endif -%}
{%- endmacro -%}

SELECT
    (SELECT
        COUNT(*)
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/clickhouse.sql.jinja2' as lib with context -%}

SELECT
    (SELECT
        COUNT(*)
    FROM {{ lib.render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/databricks.sql.jinja2' as lib with context -%}

{%- macro render_referenced_table(referenced_table) -%}
{%- if referenced_table.find(".") < 0 -%}
   {{ lib.quote_identifier(lib.macro_schema_name) }}.{{- lib.quote_identifier(referenced_table) -}}
{%- else -%}
   {{ referenced_table }}
{%- endif -%}
{%- endmacro -%}

SELECT
    (SELECT
        COUNT(*)
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}

SELECT
    (SELECT
        COUNT(*)
    FROM {{ lib.render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/hana.sql.jinja2' as lib with context -%}

SELECT
    (SELECT
        COUNT(*)
    FROM {{ lib.render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}

{%- macro render_referenced_table(referenced_table) -%}
{%- if referenced_table.find(".") < 0 -%}
   {{ lib.quote_identifier(lib.macro_project_name) }}.{{ lib.quote_identifier(lib.macro_schema_name) }}.{{- lib.quote_identifier(referenced_table) -}}
{%- else -%}
   {{ referenced_table }}
{%- endif -%}
{%- endmacro -%}

SELECT
    (SELECT
        COUNT(*)
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/mysql.sql.jinja2' as lib with context -%}

{%- macro render_referenced_table(referenced_table) -%}
{%- if referenced_table.find(".") < 0 -%}
   {{ lib.quote_identifier(lib.macro_project_name) }}.{{ lib.quote_identifier(lib.macro_schema_name) }}.{{- lib.quote_identifier(referenced_table) -}}
{%- else -%}
   {{ referenced_table }}
{%- endif -%}
{%- endmacro -%}

SELECT
    (SELECT
        COUNT(*)
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}

{%- macro render_referenced_table(referenced_table) -%}
{%- if referenced_table.find(".") < 0 -%}
   {{ lib.quote_identifier(lib.macro_database_name) }}.{{ lib.quote_identifier(lib.macro_schema_name) }}.{{- lib.quote_identifier(referenced_table) -}}
{%- else -%}
   {{ referenced_table }}
{%- endif -%}
{%- endmacro -%}

SELECT
    (SELECT
        COUNT(*)
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/presto.sql.jinja2' as lib with context -%}

{%- macro render_referenced_table(referenced_table) -%}
{%- if referenced_table.find(".") < 0 -%}
   {{ lib.quote_identifier(lib.macro_database_name) }}.{{ lib.quote_identifier(lib.macro_schema_name) }}.{{- lib.quote_identifier(referenced_table) -}}
{%- else -%}
   {{ referenced_table }}
{%- endif -%}
{%- endmacro -%}

SELECT
    (SELECT
        COUNT(*)
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/questdb.sql.jinja2' as lib with context -%}

{%- macro render_referenced_table(referenced_table) -%}
{%- if referenced_table.find(".") < 0 -%}
   {{ lib.quote_identifier(lib.macro_database_name) }}.{{ lib.quote_identifier(lib.macro_schema_name) }}.{{- lib.quote_identifier(referenced_table) -}}
{%- else -%}
   {{ referenced_table }}
{%- endif -%}
{%- endmacro -%}

SELECT
    (SELECT
        COUNT()
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT() AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/redshift.sql.jinja2' as lib with context -%}

{%- macro render_referenced_table(referenced_table) -%}
{%- if referenced_table.find(".") < 0 -%}
   {{ lib.quote_identifier(lib.macro_database_name) }}.{{ lib.quote_identifier(lib.macro_schema_name) }}.{{- lib.quote_identifier(referenced_table) -}}
{%- else -%}
   {{ referenced_table }}
{%- endif -%}
{%- endmacro -%}

SELECT
    (SELECT
        COUNT(*)
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}

{%- macro render_referenced_table(referenced_table) -%}
{%- if referenced_table.find(".") < 0 -%}
   {{ lib.quote_identifier(lib.macro_database_name) }}.{{ lib.quote_identifier(lib.macro_schema_name) }}.{{- lib.quote_identifier(referenced_table) -}}
{%- else -%}
   {{ referenced_table }}
{%- endif -%}
{%- endmacro -%}

SELECT
    (SELECT
        COUNT(*)
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/spark.sql.jinja2' as lib with context -%}

{%- macro render_referenced_table(referenced_table) -%}
{%- if referenced_table.find(".") < 0 -%}
   {{ lib.quote_identifier(lib.macro_database_name) }}.{{ lib.quote_identifier(lib.macro_schema_name) }}.{{- lib.quote_identifier(referenced_table) -}}
{%- else -%}
   {{ referenced_table }}
{%- endif -%}
{%- endmacro -%}

SELECT
    (SELECT
        COUNT(*)
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}

{%- macro render_referenced_table(referenced_table) -%}
{%- if referenced_table.find(".") < 0 -%}
   {{ lib.quote_identifier(lib.macro_database_name) }}.{{ lib.quote_identifier(lib.macro_schema_name) }}.{{- lib.quote_identifier(referenced_table) -}}
{%- else -%}
   {{ referenced_table }}
{%- endif -%}
{%- endmacro -%}

SELECT
    (SELECT
        COUNT_BIG(*)
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT_BIG(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/teradata.sql.jinja2' as lib with context -%}

{%- macro render_referenced_table(referenced_table) -%}
{%- if referenced_table.find(".") < 0 -%}
   {{ lib.quote_identifier(lib.macro_database_name) }}.{{ lib.quote_identifier(lib.macro_schema_name) }}.{{- lib.quote_identifier(referenced_table) -}}
{%- else -%}
   {{ referenced_table }}
{%- endif -%}
{%- endmacro -%}

SELECT
    (SELECT
        COUNT(*)
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{% import '/dialects/trino.sql.jinja2' as lib with context -%}

{%- macro render_referenced_table(referenced_table) -%}
{%- if referenced_table.find(".") < 0 -%}
   {{ lib.quote_identifier(lib.macro_catalog_name) }}.{{ lib.quote_identifier(lib.macro_schema_name) }}.{{- lib.quote_identifier(referenced_table) -}}
{%- else -%}
   {{ referenced_table }}
{%- endif -%}
{%- endmacro -%}

SELECT
    (SELECT
        COUNT(*)
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT(*) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

What's next