mean match

mean match checks

Description
Column level check that ensures that compares the mean (average) of the values in the tested column to the mean (average) of values in a reference column from the reference table. Compares the mean (average) value 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 mean match

Check description
Verifies that percentage of the difference between the mean (average) value in a tested column in a parent table and the mean (average) 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_mean_match	profiling		mean	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_mean_match eck-run">check run command href="#__codelineno-1-1">dqo> check run -ch=profile_mean_match the connection name to the below href="#__codelineno-2-1">dqo> check run -c=connection_name -ch=profile_mean_match add the table name to the below href="#__codelineno-3-1">dqo> check run -c=connection_name -t=table_name -ch=profile_mean_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_mean_match href="#__codelineno-5-1">      profiling_checks: comparisons: compare_to_source_of_truth_table: reference_column: source_of_truth_column_name profile_mean_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_mean_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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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 mean match
Check description

Verifies that percentage of the difference between the mean (average) value in a tested column in a parent table and the mean (average) 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_mean_match
recurring
daily
mean
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_mean_match

Run check (Shell)

To run this check provide check name in check run command
dqo> check run -ch=daily_mean_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_mean_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_mean_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_mean_match

Check structure (Yaml)
      recurring_checks:
        daily:
          comparisons:
            compare_to_source_of_truth_table:
              reference_column: source_of_truth_column_name
              daily_mean_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_mean_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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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 mean match
Check description

Verifies that percentage of the difference between the mean (average) value in a tested column in a parent table and the mean (average) 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_mean_match
recurring
monthly
mean
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_mean_match

Run check (Shell)

To run this check provide check name in check run command
dqo> check run -ch=monthly_mean_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_mean_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_mean_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_mean_match

Check structure (Yaml)
      recurring_checks:
        monthly:
          comparisons:
            compare_to_source_of_truth_table:
              reference_column: source_of_truth_column_name
              monthly_mean_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_mean_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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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 mean match
Check description

Verifies that percentage of the difference between the mean (average) value in a tested column in a parent table and the mean (average) 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_mean_match
partitioned
daily
mean
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_mean_match

Run check (Shell)

To run this check provide check name in check run command
dqo> check run -ch=daily_partition_mean_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_mean_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_mean_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_mean_match

Check structure (Yaml)
      partitioned_checks:
        daily:
          comparisons:
            compare_to_source_of_truth_table:
              reference_column: source_of_truth_column_name
              daily_partition_mean_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_mean_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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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 mean match
Check description

Verifies that percentage of the difference between the mean (average) value in a tested column in a parent table and the mean (average) 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_mean_match
partitioned
monthly
mean
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_mean_match

Run check (Shell)

To run this check provide check name in check run command
dqo> check run -ch=monthly_partition_mean_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_mean_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_mean_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_mean_match

Check structure (Yaml)
      partitioned_checks:
        monthly:
          comparisons:
            compare_to_source_of_truth_table:
              reference_column: source_of_truth_column_name
              monthly_partition_mean_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_mean_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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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
    AVG({{ 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
    AVG(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)