total not null count match percent

total not null count match percent checks

Description
Column level check that ensures that there are no more than a maximum percentage of difference of the row count of a tested table's column (counting the not null values) and of an row count of another (reference) table, also counting all rows with not null values.

profile total not null count match percent

Check description
Verifies that the percentage of difference in total not null count of a column in a table and total not null count of a column of another table does not exceed the set number. Stores the most recent captured value for each day when the data quality check was evaluated.

Check name	Check type	Time scale	Sensor definition	Quality rule
profile_total_not_null_count_match_percent	profiling		total_not_null_count_match_percent	diff_percent

Enable check (Shell)
To enable this check provide connection name and check name in check enable command

dqo> check enable -c=connection_name -ch=profile_total_not_null_count_match_percent

Run check (Shell)
To run this check provide check name in check run command

dqo> check run -ch=profile_total_not_null_count_match_percent

It is also possible to run this check on a specific connection. In order to do this, add the connection name to the below

dqo> check run -c=connection_name -ch=profile_total_not_null_count_match_percent

It is additionally feasible to run this check on a specific table. In order to do this, add the table name to the below

dqo> check run -c=connection_name -t=table_name -ch=profile_total_not_null_count_match_percent

It is furthermore viable to combine run this check on a specific column. In order to do this, add the column name to the below

dqo> check run -c=connection_name -t=table_name -col=column_name -ch=profile_total_not_null_count_match_percent

Check structure (Yaml)

      profiling_checks:
        accuracy:
          profile_total_not_null_count_match_percent:
            parameters:
              referenced_table: dim_customer
              referenced_column: customer_id
            warning:
              max_diff_percent: 0.0
            error:
              max_diff_percent: 1.0
            fatal:
              max_diff_percent: 5.0

Sample configuration (Yaml)

# yaml-language-server: $schema=https://cloud.dqo.ai/dqo-yaml-schema/TableYaml-schema.json
apiVersion: dqo/v1
kind: table
spec:
  timestamp_columns:
    event_timestamp_column: col_event_timestamp
    ingestion_timestamp_column: col_inserted_at
  incremental_time_window:
    daily_partitioning_recent_days: 7
    monthly_partitioning_recent_months: 1
  columns:
    target_column:
      profiling_checks:
        accuracy:
          profile_total_not_null_count_match_percent:
            parameters:
              referenced_table: dim_customer
              referenced_column: customer_id
            warning:
              max_diff_percent: 0.0
            error:
              max_diff_percent: 1.0
            fatal:
              max_diff_percent: 5.0
      labels:
      - This is the column that is analyzed for data quality issues
    col_event_timestamp:
      labels:
      - optional column that stores the timestamp when the event/transaction happened
    col_inserted_at:
      labels:
      - optional column that stores the timestamp when row was ingested

BigQuery

Sensor template for BigQueryRendered SQL for BigQuery

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table.`customer_id`)
    FROM `your-google-project-id`.`<target_schema>`.`dim_customer` AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table.`target_column`) AS actual_value
FROM `your-google-project-id`.`<target_schema>`.`<target_table>` AS analyzed_table

MySQL

Sensor template for MySQLRendered SQL for MySQL

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

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

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

SELECT
    (SELECT
        COUNT(referenced_table.`customer_id`)
    FROM `dim_customer` AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table.`target_column`) AS actual_value
FROM `<target_table>` AS analyzed_table

PostgreSQL

Sensor template for PostgreSQLRendered SQL for PostgreSQL

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table."customer_id")
    FROM "your_postgresql_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table."target_column") AS actual_value
FROM "your_postgresql_database"."<target_schema>"."<target_table>" AS analyzed_table

Redshift

Sensor template for RedshiftRendered SQL for Redshift

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table."customer_id")
    FROM "your_redshift_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table."target_column") AS actual_value
FROM "your_redshift_database"."<target_schema>"."<target_table>" AS analyzed_table

Snowflake

Sensor template for SnowflakeRendered SQL for Snowflake

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table."customer_id")
    FROM "your_snowflake_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table."target_column") AS actual_value
FROM "your_snowflake_database"."<target_schema>"."<target_table>" AS analyzed_table

SQL Server

Sensor template for SQL ServerRendered SQL for SQL Server

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table.[customer_id])
    FROM [your_sql_server_database].[<target_schema>].[dim_customer] AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table.[target_column]) AS actual_value
FROM [your_sql_server_database].[<target_schema>].[<target_table>] AS analyzed_table

daily total not null count match percent

Check description
Verifies that the percentage of difference in total not null count of a column in a table and total not null count of a column of another table does not exceed the set number. Stores the most recent captured value for each day when the data quality check was evaluated.

Check name	Check type	Time scale	Sensor definition	Quality rule
daily_total_not_null_count_match_percent	recurring	daily	total_not_null_count_match_percent	diff_percent

Enable check (Shell)
To enable this check provide connection name and check name in check enable command

dqo> check enable -c=connection_name -ch=daily_total_not_null_count_match_percent

Run check (Shell)
To run this check provide check name in check run command

dqo> check run -ch=daily_total_not_null_count_match_percent

It is also possible to run this check on a specific connection. In order to do this, add the connection name to the below

dqo> check run -c=connection_name -ch=daily_total_not_null_count_match_percent

It is additionally feasible to run this check on a specific table. In order to do this, add the table name to the below

dqo> check run -c=connection_name -t=table_name -ch=daily_total_not_null_count_match_percent

It is furthermore viable to combine run this check on a specific column. In order to do this, add the column name to the below

dqo> check run -c=connection_name -t=table_name -col=column_name -ch=daily_total_not_null_count_match_percent

Check structure (Yaml)

      recurring_checks:
        daily:
          accuracy:
            daily_total_not_null_count_match_percent:
              parameters:
                referenced_table: dim_customer
                referenced_column: customer_id
              warning:
                max_diff_percent: 0.0
              error:
                max_diff_percent: 1.0
              fatal:
                max_diff_percent: 5.0

Sample configuration (Yaml)

# yaml-language-server: $schema=https://cloud.dqo.ai/dqo-yaml-schema/TableYaml-schema.json
apiVersion: dqo/v1
kind: table
spec:
  timestamp_columns:
    event_timestamp_column: col_event_timestamp
    ingestion_timestamp_column: col_inserted_at
  incremental_time_window:
    daily_partitioning_recent_days: 7
    monthly_partitioning_recent_months: 1
  columns:
    target_column:
      recurring_checks:
        daily:
          accuracy:
            daily_total_not_null_count_match_percent:
              parameters:
                referenced_table: dim_customer
                referenced_column: customer_id
              warning:
                max_diff_percent: 0.0
              error:
                max_diff_percent: 1.0
              fatal:
                max_diff_percent: 5.0
      labels:
      - This is the column that is analyzed for data quality issues
    col_event_timestamp:
      labels:
      - optional column that stores the timestamp when the event/transaction happened
    col_inserted_at:
      labels:
      - optional column that stores the timestamp when row was ingested

BigQuery

Sensor template for BigQueryRendered SQL for BigQuery

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table.`customer_id`)
    FROM `your-google-project-id`.`<target_schema>`.`dim_customer` AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table.`target_column`) AS actual_value
FROM `your-google-project-id`.`<target_schema>`.`<target_table>` AS analyzed_table

MySQL

Sensor template for MySQLRendered SQL for MySQL

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

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

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

SELECT
    (SELECT
        COUNT(referenced_table.`customer_id`)
    FROM `dim_customer` AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table.`target_column`) AS actual_value
FROM `<target_table>` AS analyzed_table

PostgreSQL

Sensor template for PostgreSQLRendered SQL for PostgreSQL

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table."customer_id")
    FROM "your_postgresql_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table."target_column") AS actual_value
FROM "your_postgresql_database"."<target_schema>"."<target_table>" AS analyzed_table

Redshift

Sensor template for RedshiftRendered SQL for Redshift

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table."customer_id")
    FROM "your_redshift_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table."target_column") AS actual_value
FROM "your_redshift_database"."<target_schema>"."<target_table>" AS analyzed_table

Snowflake

Sensor template for SnowflakeRendered SQL for Snowflake

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table."customer_id")
    FROM "your_snowflake_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table."target_column") AS actual_value
FROM "your_snowflake_database"."<target_schema>"."<target_table>" AS analyzed_table

SQL Server

Sensor template for SQL ServerRendered SQL for SQL Server

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table.[customer_id])
    FROM [your_sql_server_database].[<target_schema>].[dim_customer] AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table.[target_column]) AS actual_value
FROM [your_sql_server_database].[<target_schema>].[<target_table>] AS analyzed_table

monthly total not null count match percent

Check description
Verifies that the percentage of difference in total not null count of a column in a table and total not null count of a column of another table does not exceed the set number. Stores the most recent row count for each month when the data quality check was evaluated.

Check name	Check type	Time scale	Sensor definition	Quality rule
monthly_total_not_null_count_match_percent	recurring	monthly	total_not_null_count_match_percent	diff_percent

Enable check (Shell)
To enable this check provide connection name and check name in check enable command

dqo> check enable -c=connection_name -ch=monthly_total_not_null_count_match_percent

Run check (Shell)
To run this check provide check name in check run command

dqo> check run -ch=monthly_total_not_null_count_match_percent

It is also possible to run this check on a specific connection. In order to do this, add the connection name to the below

dqo> check run -c=connection_name -ch=monthly_total_not_null_count_match_percent

It is additionally feasible to run this check on a specific table. In order to do this, add the table name to the below

dqo> check run -c=connection_name -t=table_name -ch=monthly_total_not_null_count_match_percent

It is furthermore viable to combine run this check on a specific column. In order to do this, add the column name to the below

dqo> check run -c=connection_name -t=table_name -col=column_name -ch=monthly_total_not_null_count_match_percent

Check structure (Yaml)

      recurring_checks:
        monthly:
          accuracy:
            monthly_total_not_null_count_match_percent:
              parameters:
                referenced_table: dim_customer
                referenced_column: customer_id
              warning:
                max_diff_percent: 0.0
              error:
                max_diff_percent: 1.0
              fatal:
                max_diff_percent: 5.0

Sample configuration (Yaml)

# yaml-language-server: $schema=https://cloud.dqo.ai/dqo-yaml-schema/TableYaml-schema.json
apiVersion: dqo/v1
kind: table
spec:
  timestamp_columns:
    event_timestamp_column: col_event_timestamp
    ingestion_timestamp_column: col_inserted_at
  incremental_time_window:
    daily_partitioning_recent_days: 7
    monthly_partitioning_recent_months: 1
  columns:
    target_column:
      recurring_checks:
        monthly:
          accuracy:
            monthly_total_not_null_count_match_percent:
              parameters:
                referenced_table: dim_customer
                referenced_column: customer_id
              warning:
                max_diff_percent: 0.0
              error:
                max_diff_percent: 1.0
              fatal:
                max_diff_percent: 5.0
      labels:
      - This is the column that is analyzed for data quality issues
    col_event_timestamp:
      labels:
      - optional column that stores the timestamp when the event/transaction happened
    col_inserted_at:
      labels:
      - optional column that stores the timestamp when row was ingested

BigQuery

Sensor template for BigQueryRendered SQL for BigQuery

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table.`customer_id`)
    FROM `your-google-project-id`.`<target_schema>`.`dim_customer` AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table.`target_column`) AS actual_value
FROM `your-google-project-id`.`<target_schema>`.`<target_table>` AS analyzed_table

MySQL

Sensor template for MySQLRendered SQL for MySQL

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

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

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

SELECT
    (SELECT
        COUNT(referenced_table.`customer_id`)
    FROM `dim_customer` AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table.`target_column`) AS actual_value
FROM `<target_table>` AS analyzed_table

PostgreSQL

Sensor template for PostgreSQLRendered SQL for PostgreSQL

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table."customer_id")
    FROM "your_postgresql_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table."target_column") AS actual_value
FROM "your_postgresql_database"."<target_schema>"."<target_table>" AS analyzed_table

Redshift

Sensor template for RedshiftRendered SQL for Redshift

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table."customer_id")
    FROM "your_redshift_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table."target_column") AS actual_value
FROM "your_redshift_database"."<target_schema>"."<target_table>" AS analyzed_table

Snowflake

Sensor template for SnowflakeRendered SQL for Snowflake

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table."customer_id")
    FROM "your_snowflake_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table."target_column") AS actual_value
FROM "your_snowflake_database"."<target_schema>"."<target_table>" AS analyzed_table

SQL Server

Sensor template for SQL ServerRendered SQL for SQL Server

{% 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(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    COUNT({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        COUNT(referenced_table.[customer_id])
    FROM [your_sql_server_database].[<target_schema>].[dim_customer] AS referenced_table
    ) AS expected_value,
    COUNT(analyzed_table.[target_column]) AS actual_value
FROM [your_sql_server_database].[<target_schema>].[<target_table>] AS analyzed_table