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
- 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