accuracy table sensors

total row count match percent

Full sensor name

table/accuracy/total_row_count_match_percent

Description
Table level sensor that calculates 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.

Parameters

Field name	Description	Allowed data type	Is it required?	Allowed values
referenced_table	This field can be used to define the name of the table to be compared to. In order to define the name of the table, user should write correct name as a String.	string

SQL Template (Jinja2)

BigQueryMySQLPostgreSQLRedshiftSnowflakeSQL Server

{% 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/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/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/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(*)
    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() -}}

---