Skip to content

Average delay

The query for this check calculates the average timestamp difference between two columns provided by the user . It performed by using SQL function:

  • AVG().
  • ABS().
  • TIMESTAMP_DIFF() in bigquery.
  • TIMESTAMPDIFF() in snowflake.

In addition, the sensor protects against operations on columns other than timestamp. For this purpose, it uses column casting to the timestamp type.

Jinja Template

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    AVG(ABS(TIMESTAMP_DIFF({{ lib.render_average_delay(parameters.column1, parameters.column2, parameters.time_scale) }}))) AS actual_value
    {{- lib.render_dimension_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() -}}

{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
SELECT
    AVG(ABS(TIMESTAMPDIFF({{ lib.render_average_delay(parameters.column1, parameters.column2, parameters.time_scale) }}))) AS actual_value
    {{- lib.render_dimension_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() -}}