max match

max match checks

Description
Column level check that ensures that compares the maximum value in the tested column to maximum value in a reference column from the reference table. Compares the maximum values for each group of data. The data is grouped using a GROUP BY clause and groups are matched between the tested (parent) table and the reference table (the source of truth).

profile max match

Check description
Verifies that percentage of the difference between the maximum value in a tested column in a parent table and the maximum value in a column in the reference table. The difference must be below defined percentage thresholds.

Check name	Check type	Time scale	Sensor definition	Quality rule
profile_max_match	profiling		max_value	diff_percent

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

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

 It is also possible to run this check on a specific connection. In order to do this, add 
 It is additionally feasible to run this check on a specific table. In order to do this, 
 It is furthermore viable to combine run this check on a specific column. In order to do 
 Check structure (Yaml)           
 Sample configuration (Yaml)
 ap ki sp                                    

interface/check/#dqo-check-enable">check enable command href="#__codelineno-0-1">dqo> check enable -c=connection_name -ch=profile_max_match eck-run">check run command href="#__codelineno-1-1">dqo> check run -ch=profile_max_match the connection name to the below href="#__codelineno-2-1">dqo> check run -c=connection_name -ch=profile_max_match add the table name to the below href="#__codelineno-3-1">dqo> check run -c=connection_name -t=table_name -ch=profile_max_match this, add the column name to the below href="#__codelineno-4-1">dqo> check run -c=connection_name -t=table_name -col=column_name -ch=profile_max_match href="#__codelineno-5-1">      profiling_checks: comparisons: compare_to_source_of_truth_table: reference_column: source_of_truth_column_name profile_max_match: warning: max_diff_percent: 0.0 error: max_diff_percent: 1.0 fatal: max_diff_percent: 5.0 href="#__codelineno-6-1"># yaml-language-server: $schema=https://cloud.dqo.ai/dqo-yaml-schema/TableYaml-schema.json iVersion: dqo/v1 nd: table ec: 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 table_comparisons: compare_to_source_of_truth_table: reference_table_connection_name: <source_of_truth_connection_name> reference_table_schema_name: <source_of_truth_schema_name> reference_table_name: <source_of_truth_table_name> check_type: profiling grouping_columns: - compared_table_column_name: country reference_table_column_name: country_column_name_on_reference_table - compared_table_column_name: state reference_table_column_name: state_column_name_on_reference_table columns: target_column: class="w">      profiling_checks: class="hll">        comparisons: class="hll">          compare_to_source_of_truth_table: class="hll">            reference_column: source_of_truth_column_name class="hll">            profile_max_match: class="hll">              warning: class="hll">                max_diff_percent: 0.0 class="hll">              error: class="hll">                max_diff_percent: 1.0 class="hll">              fatal: class="hll">                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 country: labels: - column used as the first grouping key for calculating aggregated values used for the table comparison state: labels: - column used as the first grouping key for calculating aggregated values used for the table comparison
BigQuery
Sensor template for BigQueryRendered SQL for BigQuery


{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.`target_column`) AS actual_value,
    DATE_TRUNC(CAST(CURRENT_TIMESTAMP() AS DATE), MONTH) AS time_period,
    TIMESTAMP(DATE_TRUNC(CAST(CURRENT_TIMESTAMP() AS DATE), MONTH)) AS time_period_utc
FROM `your-google-project-id`.`<target_schema>`.`<target_table>` AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




MySQL
Sensor template for MySQLRendered SQL for MySQL


{% import '/dialects/mysql.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.`target_column`) AS actual_value,
    DATE_FORMAT(LOCALTIMESTAMP, '%Y-%m-01 00:00:00') AS time_period,
    FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_FORMAT(LOCALTIMESTAMP, '%Y-%m-01 00:00:00'))) AS time_period_utc
FROM `<target_table>` AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Oracle
Sensor template for OracleRendered SQL for Oracle


{% import '/dialects/oracle.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
    {{- lib.render_where_clause(table_alias_prefix='original_table') }}
) analyzed_table
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    time_period,
    time_period_utc
FROM (
    SELECT
        original_table.*,
    TRUNC(CAST(CURRENT_TIMESTAMP AS DATE), 'MONTH') AS time_period,
    CAST(TRUNC(CAST(CURRENT_TIMESTAMP AS DATE), 'MONTH') AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
    FROM "<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




PostgreSQL
Sensor template for PostgreSQLRendered SQL for PostgreSQL


{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    DATE_TRUNC('MONTH', CAST(LOCALTIMESTAMP AS date)) AS time_period,
    CAST((DATE_TRUNC('MONTH', CAST(LOCALTIMESTAMP AS date))) AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
FROM "your_postgresql_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Redshift
Sensor template for RedshiftRendered SQL for Redshift


{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    DATE_TRUNC('MONTH', CAST(LOCALTIMESTAMP AS date)) AS time_period,
    CAST((DATE_TRUNC('MONTH', CAST(LOCALTIMESTAMP AS date))) AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
FROM "your_redshift_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Snowflake
Sensor template for SnowflakeRendered SQL for Snowflake


{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    DATE_TRUNC('MONTH', CAST(TO_TIMESTAMP_NTZ(LOCALTIMESTAMP()) AS date)) AS time_period,
    TO_TIMESTAMP(DATE_TRUNC('MONTH', CAST(TO_TIMESTAMP_NTZ(LOCALTIMESTAMP()) AS date))) AS time_period_utc
FROM "your_snowflake_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




SQL Server
Sensor template for SQL ServerRendered SQL for SQL Server


{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.[target_column]) AS actual_value,
    DATEADD(month, DATEDIFF(month, 0, SYSDATETIMEOFFSET()), 0) AS time_period,
    CAST((DATEADD(month, DATEDIFF(month, 0, SYSDATETIMEOFFSET()), 0)) AS DATETIME) AS time_period_utc
FROM [your_sql_server_database].[<target_schema>].[<target_table>] AS analyzed_table





daily max match
Check description

Verifies that percentage of the difference between the maximum value in a tested column in a parent table and the maximum value in a column in the reference table. The difference must be below defined percentage thresholds. 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_max_match
recurring
daily
max_value
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_max_match

Run check (Shell)

To run this check provide check name in check run command
dqo> check run -ch=daily_max_match

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_max_match

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_max_match

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_max_match

Check structure (Yaml)
      recurring_checks:
        daily:
          comparisons:
            compare_to_source_of_truth_table:
              reference_column: source_of_truth_column_name
              daily_max_match:
                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
  table_comparisons:
    compare_to_source_of_truth_table:
      reference_table_connection_name: <source_of_truth_connection_name>
      reference_table_schema_name: <source_of_truth_schema_name>
      reference_table_name: <source_of_truth_table_name>
      check_type: profiling
      grouping_columns:
      - compared_table_column_name: country
        reference_table_column_name: country_column_name_on_reference_table
      - compared_table_column_name: state
        reference_table_column_name: state_column_name_on_reference_table
  columns:
    target_column:
      recurring_checks:
        daily:
          comparisons:
            compare_to_source_of_truth_table:
              reference_column: source_of_truth_column_name
              daily_max_match:
                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
    country:
      labels:
      - column used as the first grouping key for calculating aggregated values used
        for the table comparison
    state:
      labels:
      - column used as the first grouping key for calculating aggregated values used
        for the table comparison

BigQuery
Sensor template for BigQueryRendered SQL for BigQuery


{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.`target_column`) AS actual_value,
    CAST(CURRENT_TIMESTAMP() AS DATE) AS time_period,
    TIMESTAMP(CAST(CURRENT_TIMESTAMP() AS DATE)) AS time_period_utc
FROM `your-google-project-id`.`<target_schema>`.`<target_table>` AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




MySQL
Sensor template for MySQLRendered SQL for MySQL


{% import '/dialects/mysql.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.`target_column`) AS actual_value,
    DATE_FORMAT(LOCALTIMESTAMP, '%Y-%m-%d 00:00:00') AS time_period,
    FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_FORMAT(LOCALTIMESTAMP, '%Y-%m-%d 00:00:00'))) AS time_period_utc
FROM `<target_table>` AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Oracle
Sensor template for OracleRendered SQL for Oracle


{% import '/dialects/oracle.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
    {{- lib.render_where_clause(table_alias_prefix='original_table') }}
) analyzed_table
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    time_period,
    time_period_utc
FROM (
    SELECT
        original_table.*,
    TRUNC(CAST(CURRENT_TIMESTAMP AS DATE)) AS time_period,
    CAST(TRUNC(CAST(CURRENT_TIMESTAMP AS DATE)) AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
    FROM "<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




PostgreSQL
Sensor template for PostgreSQLRendered SQL for PostgreSQL


{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    CAST(LOCALTIMESTAMP AS date) AS time_period,
    CAST((CAST(LOCALTIMESTAMP AS date)) AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
FROM "your_postgresql_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Redshift
Sensor template for RedshiftRendered SQL for Redshift


{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    CAST(LOCALTIMESTAMP AS date) AS time_period,
    CAST((CAST(LOCALTIMESTAMP AS date)) AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
FROM "your_redshift_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Snowflake
Sensor template for SnowflakeRendered SQL for Snowflake


{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    CAST(TO_TIMESTAMP_NTZ(LOCALTIMESTAMP()) AS date) AS time_period,
    TO_TIMESTAMP(CAST(TO_TIMESTAMP_NTZ(LOCALTIMESTAMP()) AS date)) AS time_period_utc
FROM "your_snowflake_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




SQL Server
Sensor template for SQL ServerRendered SQL for SQL Server


{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.[target_column]) AS actual_value,
    CAST(SYSDATETIMEOFFSET() AS date) AS time_period,
    CAST((CAST(SYSDATETIMEOFFSET() AS date)) AS DATETIME) AS time_period_utc
FROM [your_sql_server_database].[<target_schema>].[<target_table>] AS analyzed_table





monthly max match
Check description

Verifies that percentage of the difference between the maximum value in a tested column in a parent table and the maximum value in a column in the reference table. The difference must be below defined percentage thresholds. Stores the most recent captured value for each month when the data quality check was evaluated.  



Check name
Check type
Time scale
Sensor definition
Quality rule




monthly_max_match
recurring
monthly
max_value
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_max_match

Run check (Shell)

To run this check provide check name in check run command
dqo> check run -ch=monthly_max_match

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_max_match

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_max_match

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_max_match

Check structure (Yaml)
      recurring_checks:
        monthly:
          comparisons:
            compare_to_source_of_truth_table:
              reference_column: source_of_truth_column_name
              monthly_max_match:
                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
  table_comparisons:
    compare_to_source_of_truth_table:
      reference_table_connection_name: <source_of_truth_connection_name>
      reference_table_schema_name: <source_of_truth_schema_name>
      reference_table_name: <source_of_truth_table_name>
      check_type: profiling
      grouping_columns:
      - compared_table_column_name: country
        reference_table_column_name: country_column_name_on_reference_table
      - compared_table_column_name: state
        reference_table_column_name: state_column_name_on_reference_table
  columns:
    target_column:
      recurring_checks:
        monthly:
          comparisons:
            compare_to_source_of_truth_table:
              reference_column: source_of_truth_column_name
              monthly_max_match:
                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
    country:
      labels:
      - column used as the first grouping key for calculating aggregated values used
        for the table comparison
    state:
      labels:
      - column used as the first grouping key for calculating aggregated values used
        for the table comparison

BigQuery
Sensor template for BigQueryRendered SQL for BigQuery


{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.`target_column`) AS actual_value,
    DATE_TRUNC(CAST(CURRENT_TIMESTAMP() AS DATE), MONTH) AS time_period,
    TIMESTAMP(DATE_TRUNC(CAST(CURRENT_TIMESTAMP() AS DATE), MONTH)) AS time_period_utc
FROM `your-google-project-id`.`<target_schema>`.`<target_table>` AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




MySQL
Sensor template for MySQLRendered SQL for MySQL


{% import '/dialects/mysql.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.`target_column`) AS actual_value,
    DATE_FORMAT(LOCALTIMESTAMP, '%Y-%m-01 00:00:00') AS time_period,
    FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_FORMAT(LOCALTIMESTAMP, '%Y-%m-01 00:00:00'))) AS time_period_utc
FROM `<target_table>` AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Oracle
Sensor template for OracleRendered SQL for Oracle


{% import '/dialects/oracle.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
    {{- lib.render_where_clause(table_alias_prefix='original_table') }}
) analyzed_table
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    time_period,
    time_period_utc
FROM (
    SELECT
        original_table.*,
    TRUNC(CAST(CURRENT_TIMESTAMP AS DATE), 'MONTH') AS time_period,
    CAST(TRUNC(CAST(CURRENT_TIMESTAMP AS DATE), 'MONTH') AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
    FROM "<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




PostgreSQL
Sensor template for PostgreSQLRendered SQL for PostgreSQL


{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    DATE_TRUNC('MONTH', CAST(LOCALTIMESTAMP AS date)) AS time_period,
    CAST((DATE_TRUNC('MONTH', CAST(LOCALTIMESTAMP AS date))) AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
FROM "your_postgresql_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Redshift
Sensor template for RedshiftRendered SQL for Redshift


{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    DATE_TRUNC('MONTH', CAST(LOCALTIMESTAMP AS date)) AS time_period,
    CAST((DATE_TRUNC('MONTH', CAST(LOCALTIMESTAMP AS date))) AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
FROM "your_redshift_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Snowflake
Sensor template for SnowflakeRendered SQL for Snowflake


{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    DATE_TRUNC('MONTH', CAST(TO_TIMESTAMP_NTZ(LOCALTIMESTAMP()) AS date)) AS time_period,
    TO_TIMESTAMP(DATE_TRUNC('MONTH', CAST(TO_TIMESTAMP_NTZ(LOCALTIMESTAMP()) AS date))) AS time_period_utc
FROM "your_snowflake_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




SQL Server
Sensor template for SQL ServerRendered SQL for SQL Server


{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.[target_column]) AS actual_value,
    DATEADD(month, DATEDIFF(month, 0, SYSDATETIMEOFFSET()), 0) AS time_period,
    CAST((DATEADD(month, DATEDIFF(month, 0, SYSDATETIMEOFFSET()), 0)) AS DATETIME) AS time_period_utc
FROM [your_sql_server_database].[<target_schema>].[<target_table>] AS analyzed_table





daily partition max match
Check description

Verifies that percentage of the difference between the maximum value in a tested column in a parent table and the maximum value in a column in the reference table. The difference must be below defined percentage thresholds. Compares each daily partition (each day of data) between the compared table and the reference table (the source of truth).  



Check name
Check type
Time scale
Sensor definition
Quality rule




daily_partition_max_match
partitioned
daily
max_value
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_partition_max_match

Run check (Shell)

To run this check provide check name in check run command
dqo> check run -ch=daily_partition_max_match

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_partition_max_match

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_partition_max_match

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_partition_max_match

Check structure (Yaml)
      partitioned_checks:
        daily:
          comparisons:
            compare_to_source_of_truth_table:
              reference_column: source_of_truth_column_name
              daily_partition_max_match:
                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
  table_comparisons:
    compare_to_source_of_truth_table:
      reference_table_connection_name: <source_of_truth_connection_name>
      reference_table_schema_name: <source_of_truth_schema_name>
      reference_table_name: <source_of_truth_table_name>
      check_type: profiling
      grouping_columns:
      - compared_table_column_name: country
        reference_table_column_name: country_column_name_on_reference_table
      - compared_table_column_name: state
        reference_table_column_name: state_column_name_on_reference_table
  columns:
    target_column:
      partitioned_checks:
        daily:
          comparisons:
            compare_to_source_of_truth_table:
              reference_column: source_of_truth_column_name
              daily_partition_max_match:
                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
    country:
      labels:
      - column used as the first grouping key for calculating aggregated values used
        for the table comparison
    state:
      labels:
      - column used as the first grouping key for calculating aggregated values used
        for the table comparison

BigQuery
Sensor template for BigQueryRendered SQL for BigQuery


{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.`target_column`) AS actual_value,
    CAST(analyzed_table.`` AS DATE) AS time_period,
    TIMESTAMP(CAST(analyzed_table.`` AS DATE)) AS time_period_utc
FROM `your-google-project-id`.`<target_schema>`.`<target_table>` AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




MySQL
Sensor template for MySQLRendered SQL for MySQL


{% import '/dialects/mysql.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.`target_column`) AS actual_value,
    DATE_FORMAT(analyzed_table.``, '%Y-%m-%d 00:00:00') AS time_period,
    FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_FORMAT(analyzed_table.``, '%Y-%m-%d 00:00:00'))) AS time_period_utc
FROM `<target_table>` AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Oracle
Sensor template for OracleRendered SQL for Oracle


{% import '/dialects/oracle.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
    {{- lib.render_where_clause(table_alias_prefix='original_table') }}
) analyzed_table
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    time_period,
    time_period_utc
FROM (
    SELECT
        original_table.*,
    TRUNC(CAST(original_table."" AS DATE)) AS time_period,
    CAST(TRUNC(CAST(original_table."" AS DATE)) AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
    FROM "<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




PostgreSQL
Sensor template for PostgreSQLRendered SQL for PostgreSQL


{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    CAST(analyzed_table."" AS date) AS time_period,
    CAST((CAST(analyzed_table."" AS date)) AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
FROM "your_postgresql_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Redshift
Sensor template for RedshiftRendered SQL for Redshift


{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    CAST(analyzed_table."" AS date) AS time_period,
    CAST((CAST(analyzed_table."" AS date)) AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
FROM "your_redshift_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Snowflake
Sensor template for SnowflakeRendered SQL for Snowflake


{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    CAST(analyzed_table."" AS date) AS time_period,
    TO_TIMESTAMP(CAST(analyzed_table."" AS date)) AS time_period_utc
FROM "your_snowflake_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




SQL Server
Sensor template for SQL ServerRendered SQL for SQL Server


{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.[target_column]) AS actual_value,
    CAST(analyzed_table.[] AS date) AS time_period,
    CAST((CAST(analyzed_table.[] AS date)) AS DATETIME) AS time_period_utc
FROM [your_sql_server_database].[<target_schema>].[<target_table>] AS analyzed_table
GROUP BY CAST(analyzed_table.[] AS date), CAST(analyzed_table.[] AS date)
ORDER BY CAST(analyzed_table.[] AS date)





monthly partition max match
Check description

Verifies that percentage of the difference between the maximum value in a tested column in a parent table and the maximum value in a column in the reference table. The difference must be below defined percentage thresholds. Compares each monthly partition (each month of data) between the compared table and the reference table (the source of truth).  



Check name
Check type
Time scale
Sensor definition
Quality rule




monthly_partition_max_match
partitioned
monthly
max_value
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_partition_max_match

Run check (Shell)

To run this check provide check name in check run command
dqo> check run -ch=monthly_partition_max_match

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_partition_max_match

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_partition_max_match

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_partition_max_match

Check structure (Yaml)
      partitioned_checks:
        monthly:
          comparisons:
            compare_to_source_of_truth_table:
              reference_column: source_of_truth_column_name
              monthly_partition_max_match:
                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
  table_comparisons:
    compare_to_source_of_truth_table:
      reference_table_connection_name: <source_of_truth_connection_name>
      reference_table_schema_name: <source_of_truth_schema_name>
      reference_table_name: <source_of_truth_table_name>
      check_type: profiling
      grouping_columns:
      - compared_table_column_name: country
        reference_table_column_name: country_column_name_on_reference_table
      - compared_table_column_name: state
        reference_table_column_name: state_column_name_on_reference_table
  columns:
    target_column:
      partitioned_checks:
        monthly:
          comparisons:
            compare_to_source_of_truth_table:
              reference_column: source_of_truth_column_name
              monthly_partition_max_match:
                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
    country:
      labels:
      - column used as the first grouping key for calculating aggregated values used
        for the table comparison
    state:
      labels:
      - column used as the first grouping key for calculating aggregated values used
        for the table comparison

BigQuery
Sensor template for BigQueryRendered SQL for BigQuery


{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.`target_column`) AS actual_value,
    DATE_TRUNC(CAST(analyzed_table.`` AS DATE), MONTH) AS time_period,
    TIMESTAMP(DATE_TRUNC(CAST(analyzed_table.`` AS DATE), MONTH)) AS time_period_utc
FROM `your-google-project-id`.`<target_schema>`.`<target_table>` AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




MySQL
Sensor template for MySQLRendered SQL for MySQL


{% import '/dialects/mysql.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.`target_column`) AS actual_value,
    DATE_FORMAT(analyzed_table.``, '%Y-%m-01 00:00:00') AS time_period,
    FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_FORMAT(analyzed_table.``, '%Y-%m-01 00:00:00'))) AS time_period_utc
FROM `<target_table>` AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Oracle
Sensor template for OracleRendered SQL for Oracle


{% import '/dialects/oracle.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections_reference('analyzed_table') }}
    {{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
    SELECT
        original_table.*
        {{- lib.render_data_grouping_projections('original_table') }}
        {{- lib.render_time_dimension_projection('original_table') }}
    FROM {{ lib.render_target_table() }} original_table
    {{- lib.render_where_clause(table_alias_prefix='original_table') }}
) analyzed_table
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    time_period,
    time_period_utc
FROM (
    SELECT
        original_table.*,
    TRUNC(CAST(original_table."" AS DATE), 'MONTH') AS time_period,
    CAST(TRUNC(CAST(original_table."" AS DATE), 'MONTH') AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
    FROM "<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




PostgreSQL
Sensor template for PostgreSQLRendered SQL for PostgreSQL


{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    DATE_TRUNC('MONTH', CAST(analyzed_table."" AS date)) AS time_period,
    CAST((DATE_TRUNC('MONTH', CAST(analyzed_table."" AS date))) AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
FROM "your_postgresql_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Redshift
Sensor template for RedshiftRendered SQL for Redshift


{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    DATE_TRUNC('MONTH', CAST(analyzed_table."" AS date)) AS time_period,
    CAST((DATE_TRUNC('MONTH', CAST(analyzed_table."" AS date))) AS TIMESTAMP WITH TIME ZONE) AS time_period_utc
FROM "your_redshift_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




Snowflake
Sensor template for SnowflakeRendered SQL for Snowflake


{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table."target_column") AS actual_value,
    DATE_TRUNC('MONTH', CAST(analyzed_table."" AS date)) AS time_period,
    TO_TIMESTAMP(DATE_TRUNC('MONTH', CAST(analyzed_table."" AS date))) AS time_period_utc
FROM "your_snowflake_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY time_period, time_period_utc
ORDER BY time_period, time_period_utc




SQL Server
Sensor template for SQL ServerRendered SQL for SQL Server


{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}
SELECT
    MAX({{ lib.render_target_column('analyzed_table')}}) AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}



SELECT
    MAX(analyzed_table.[target_column]) AS actual_value,
    DATEFROMPARTS(YEAR(CAST(analyzed_table.[] AS date)), MONTH(CAST(analyzed_table.[] AS date)), 1) AS time_period,
    CAST((DATEFROMPARTS(YEAR(CAST(analyzed_table.[] AS date)), MONTH(CAST(analyzed_table.[] AS date)), 1)) AS DATETIME) AS time_period_utc
FROM [your_sql_server_database].[<target_schema>].[<target_table>] AS analyzed_table
GROUP BY DATEFROMPARTS(YEAR(CAST(analyzed_table.[] AS date)), MONTH(CAST(analyzed_table.[] AS date)), 1), DATEADD(month, DATEDIFF(month, 0, analyzed_table.[]), 0)
ORDER BY DATEFROMPARTS(YEAR(CAST(analyzed_table.[] AS date)), MONTH(CAST(analyzed_table.[] AS date)), 1)