total min match percent

total min match percent checks

Description
Column level check that ensures that there are no more than a maximum percentage of difference of min of a table column and of a min of another table column.

---

profile total min match percent

Check description
Verifies that the percentage of difference in total min of a column in a table and total min of a column of another table does not exceed the set number.

Check name	Check type	Time scale	Sensor definition	Quality rule
profile_total_min_match_percent	profiling		total_min_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_min_match_percent

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

dqo> check run -ch=profile_total_min_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_min_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_min_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_min_match_percent

Check structure (Yaml)

      profiling_checks:
        accuracy:
          profile_total_min_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_min_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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        MIN(referenced_table.`customer_id`)
    FROM `your-google-project-id`.`<target_schema>`.`dim_customer` AS referenced_table
    ) AS expected_value,
    MIN(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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

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

SELECT
    (SELECT
        MIN(referenced_table."customer_id")
    FROM "your_postgresql_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    MIN(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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        MIN(referenced_table."customer_id")
    FROM "your_redshift_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    MIN(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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        MIN(referenced_table."customer_id")
    FROM "your_snowflake_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    MIN(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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

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

---

daily total min match percent

Check description
Verifies that the percentage of difference in total min of a column in a table and total min 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_min_match_percent	recurring	daily	total_min_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_min_match_percent

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

dqo> check run -ch=daily_total_min_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_min_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_min_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_min_match_percent

Check structure (Yaml)

      recurring_checks:
        daily:
          accuracy:
            daily_total_min_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_min_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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        MIN(referenced_table.`customer_id`)
    FROM `your-google-project-id`.`<target_schema>`.`dim_customer` AS referenced_table
    ) AS expected_value,
    MIN(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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

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

SELECT
    (SELECT
        MIN(referenced_table."customer_id")
    FROM "your_postgresql_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    MIN(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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        MIN(referenced_table."customer_id")
    FROM "your_redshift_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    MIN(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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        MIN(referenced_table."customer_id")
    FROM "your_snowflake_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    MIN(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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

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

---

monthly total min match percent

Check description
Verifies that the percentage of difference in total min of a column in a table and total min 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_min_match_percent	recurring	monthly	total_min_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_min_match_percent

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

dqo> check run -ch=monthly_total_min_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_min_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_min_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_min_match_percent

Check structure (Yaml)

      recurring_checks:
        monthly:
          accuracy:
            monthly_total_min_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_min_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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        MIN(referenced_table.`customer_id`)
    FROM `your-google-project-id`.`<target_schema>`.`dim_customer` AS referenced_table
    ) AS expected_value,
    MIN(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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

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

SELECT
    (SELECT
        MIN(referenced_table."customer_id")
    FROM "your_postgresql_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    MIN(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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        MIN(referenced_table."customer_id")
    FROM "your_redshift_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    MIN(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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

SELECT
    (SELECT
        MIN(referenced_table."customer_id")
    FROM "your_snowflake_database"."<target_schema>"."dim_customer" AS referenced_table
    ) AS expected_value,
    MIN(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
        MIN(referenced_table.{{ lib.quote_identifier(parameters.referenced_column) }})
    FROM {{ render_referenced_table(parameters.referenced_table) }} AS referenced_table
    ) AS expected_value,
    MIN({{ lib.render_target_column('analyzed_table')}}) AS actual_value
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}

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

---