Skip to content

Average delay

Description

The table timeliness check average_delay calculates an average timestamp difference between corresponding records (the ones that appear in the same row) in two specified columns.

Setting time series mode=timestamp_column is essential here since the rule for this check needs a certain number of records to run properly.

Timestamp difference part is configurable with time_scale parameter.

Info

The average is calculated with an absolute values of the timestamp differences.

Daily average on BigQuery example

Sample data

The table is bigquery-public-data.austin_crime.crime, here are the first 5 rows for the column unique_key, clearance_date, timestamp:

+-------------+     +----------------------+     +----------------------+
| unique_key  | ... |    clearance_date    | ... |      timestamp       | ...
+-------------+     +----------------------+     +----------------------+ 
|  2015150483 | ... | 2015-01-27T12:00:00Z | ... | 2015-01-15T12:00:00Z | ...
| 20151542027 | ... | 2015-07-24T12:00:00Z | ... | 2015-06-03T12:00:00Z | ...
| 20152001758 | ... | 2015-08-15T12:00:00Z | ... | 2015-07-19T12:00:00Z | ...
| 20151340282 | ... | 2015-06-15T12:00:00Z | ... | 2015-05-14T12:00:00Z | ...
| 20151871268 | ... | 2015-10-27T12:00:00Z | ... | 2015-07-06T12:00:00Z | ...
+-------------+     +----------------------+     +----------------------+

For this check we will be using clearance_date, and timestamp column to calculate timestamp difference between them.

Check configuration

After adding connection and importing tables you can access the table configuration by running.

table edit -c=<connection_name> -t=austin_crime.crime

The YAML configuration looks like this (all the code necessary to define this check is highlighted below):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
# yaml-language-server: $schema=https://cloud.dqo.ai/dqo-yaml-schema/TableYaml-schema.json
apiVersion: dqo/v1
kind: table
spec:
  target:
    schema_name: austin_crime
    table_name: crime
  time_series:
    mode: timestamp_column
    timestamp_column: timestamp
    time_gradient: day
  checks:
    timeliness:
      average_delay:
        parameters:
          column1: "clearance_date"
          column2: "timestamp"
        rules:
          moving_average:
            low:
              max_percent_above: 5.0
              max_percent_below: 5.0
            medium:
              max_percent_above: 10.0
              max_percent_below: 10.0
            high:
              max_percent_above: 15.0
              max_percent_below: 15.0
  columns:
    unique_key:
      type_snapshot:
        column_type: INT64
        nullable: true
    ...
    clearance_date:
      type_snapshot:
        column_type: TIMESTAMP
        nullable: true
    ...
    timestamp:
      type_snapshot:
        column_type: TIMESTAMP
        nullable: true
    ...
Notice that column timestamp has two roles here:

  • it is used to calculate the timestamp difference
  • it serves as timestamp column in time_series, truncated into a DAY (in this case).

Sensor

Sensor for this check is table/timeliness/average_delay which calculates a timestamp difference between two columns

SELECT
    AVG(ABS(TIMESTAMP_DIFF(analyzed_table.clearance_date, analyzed_table.timestamp, DAY))) AS actual_value, CAST(analyzed_table.`timestamp` AS date) AS time_period
FROM `bigquery-public-data`.`austin_crime`.`crime` AS analyzed_table
GROUP BY time_period
ORDER BY time_period

Finished executing a sensor for a check average_delay on the table austin_crime.crime using a sensor definition table/timeliness/average_delay, sensor result count: 1096

Results returned by the sensor:
+------------------+-----------+
|actual_value      |time_period|
+------------------+-----------+
|18.989583333333336|2014-01-01 | <--- Here are the dates from
+------------------+-----------+      the column `timestamp`.
|19.49122807017543 |2014-01-02 |
+------------------+-----------+
|21.253968253968246|2014-01-03 |
+------------------+-----------+
|19.614457831325293|2014-01-04 |
+------------------+-----------+
|19.990196078431367|2014-01-05 |
+------------------+-----------+
|17.62015503875969 |2014-01-06 |
+------------------+-----------+
|24.454545454545457|2014-01-07 |
+------------------+-----------+
|16.343749999999996|2014-01-08 |
+------------------+-----------+
|30.16666666666667 |2014-01-09 |
+------------------+-----------+
|21.54455445544554 |2014-01-10 |
+------------------+-----------+

The table above is the exact same as the one you would see on the provider's platform (in this case BigQuery).

The query returned two columns: actual_value which is an average delay in days for a day, and time_period, configured with time_series. With mode=timestamp_column the goal of time_period is to analyze the data on an extended period of time.

Rule

Definition of the rule for this check can be found in lines 18-28:

18
19
20
21
22
23
24
25
26
27
28
rules:
  moving_average:
    low:
      max_percent_above: 5.0
      max_percent_below: 5.0
    medium:
      max_percent_above: 10.0
      max_percent_below: 10.0
    high:
      max_percent_above: 15.0
      max_percent_below: 15.0

The moving_average rule as the name suggests itself, calculates a moving average based on the previous records. Each severity level has two parameters max_percent_above, and max_percent_below. Let's suppose that prev_value is an actual_value for n readings, then for n+1 reading the severity level is calculated as 

(1 + max_percent_above/100) * prev_value < actual_value < (1 - max_percent_below/100) * prev_value
for each severity level. 
Finished executing rules (thresholds) for a check average_delay on the table austin_crime.crime, verified rules count: 1089─────────────────────────────────────────────────────────────────────────────

Rule evaluation results:
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+
|actual_value      |expected_value    |time_period     |time_gradient|dimension_id|connection_hash    |connection_name|provider|table_hash         |schema_name |table_name|check_hash         |check_name   |quality_dimension|sensor_name                   |executed_at             |duration_ms|severity|rule_hash          |rule_name     |high_lower_bound  |high_upper_bound  |medium_lower_bound|medium_upper_bound|low_lower_bound   |low_upper_bound   |
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+
|16.343749999999996|20.202019151505546|2014-01-08T00:00|day          |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-23T10:07:05.106Z|3691       |3       |7342948477520614636|moving_average|17.171716278779712|23.232322024231376|18.18181723635499 |22.222221066656104|19.191918193930267|21.212120109080825|
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+
|30.16666666666667 |19.71973550756735 |2014-01-09T00:00|day          |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-23T10:07:05.106Z|3691       |3       |7342948477520614636|moving_average|16.761775181432245|22.67769583370245 |17.747761956810617|21.691709058324086|18.73374873218898 |20.705722282945718|
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+
|21.54455445544554 |20.880505636356165|2014-01-10T00:00|day          |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-23T10:07:05.106Z|3691       |0       |7342948477520614636|moving_average|17.74842979090274 |24.012581481809587|18.79245507272055 |22.968556199991784|19.836480354538356|21.924530918173975|
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+
|18.800000000000004|20.946910518265103|2014-01-11T00:00|day          |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-23T10:07:05.106Z|3691       |2       |7342948477520614636|moving_average|17.804873940525336|24.088947096004866|18.852219466438594|23.041601570091615|19.899564992351845|21.99425604417836 |
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+
|14.464285714285712|20.751736834786456|2014-01-12T00:00|day          |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-23T10:07:05.106Z|3691       |3       |7342948477520614636|moving_average|17.638976309568488|23.86449736000442 |18.67656315130781 |22.826910518265105|19.714149993047133|21.78932367652578 |
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+
|16.686956521739123|20.22778257474473 |2014-01-13T00:00|day          |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-23T10:07:05.106Z|3691       |3       |7342948477520614636|moving_average|17.19361518853302 |23.261949960956436|18.20500431727026 |22.250560832219204|19.21639344600749 |21.23917170348197 |
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+
|15.955752212389385|19.955411339898145|2014-01-14T00:00|day          |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-23T10:07:05.106Z|3691       |3       |7342948477520614636|moving_average|16.962099638913422|22.948723040882864|17.95987020590833 |21.950952473887963|18.957640772903236|20.953181906893054|
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+
|16.70833333333334 |19.669721402218947|2014-01-15T00:00|day          |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-23T10:07:05.106Z|3691       |3       |7342948477520614636|moving_average|16.719263191886103|22.620179612551787|17.702749261997052|21.63669354244084 |18.686235332107998|20.653207472329896|
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+
|20.782608695652172|19.506774973647516|2014-01-16T00:00|day          |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-23T10:07:05.106Z|3691       |1       |7342948477520614636|moving_average|16.58075872760039 |22.432791219694643|17.556097476282766|21.45745247101227 |18.53143622496514 |20.482113722329892|
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+
|19.05             |19.599016446895856|2014-01-17T00:00|day          |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-23T10:07:05.106Z|3691       |0       |7342948477520614636|moving_average|16.65916397986148 |22.538868913930234|17.639114802206272|21.558918091585443|18.619065624551062|20.57896726924065 |
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+

Have a look at severity column and see that severity levels are assigned according to the thresholds defined in the rule.

Check summary

Check evaluation summary briefly informs us about check execution:

Check evaluation summary per table:
+------------+------------------+------+--------------+-------------+------------+---------------+-------------+
|Connection  |Table             |Checks|Sensor results|Valid results|Alerts (low)|Alerts (medium)|Alerts (high)|
+------------+------------------+------+--------------+-------------+------------+---------------+-------------+
|connection_1|austin_crime.crime|1     |1096          |232          |201         |181            |475          |
+------------+------------------+------+--------------+-------------+------------+---------------+-------------+

Info

Notice that the number of sensor results is 1096 but verified rules count is 1089. What's the reason for the difference? The explanation is very simple. The average is calculated based on specified numer of previous days. In this example, time window used to calculate averages equals 7 days, so the rule evaluate the sensor result starting from 8th result.

Example with time_gradient and time_scale

Sample data

Here we use the same data as in the previous example, no let's go ahead to check configuration.

Check configuration

Here is YAML file (the difference between this and previous example is highlighted):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
# yaml-language-server: $schema=https://cloud.dqo.ai/dqo-yaml-schema/TableYaml-schema.json
apiVersion: dqo/v1
kind: table
spec:
  target:
    schema_name: austin_crime
    table_name: crime
  time_series:
    mode: timestamp_column
    timestamp_column: timestamp
    time_gradient: week
  checks:
    timeliness:
      average_delay:
        parameters:
          column1: "clearance_date"
          column2: "timestamp"
          time_scale: HOUR
        rules:
          moving_average:
            low:
              max_percent_above: 10.0
              max_percent_below: 8.0
            medium:
              max_percent_above: 20.0
              max_percent_below: 20.0
            high:
              max_percent_above: 25.0
              max_percent_below: 25.0
  columns:
    unique_key:
      type_snapshot:
        column_type: INT64
        nullable: true
    ...
    clearance_date:
      type_snapshot:
        column_type: TIMESTAMP
        nullable: true
    ...
    timestamp:
      type_snapshot:
        column_type: TIMESTAMP
        nullable: true
    ...
in this case timestamp difference will be calculated in hours, and time_series will be truncated to WEEK. Bound for severity levels were lso changes just to show the fact that they do not need to be symmetrical.

Sensor

Sensor for this check is table/timeliness/average_delay which calculates an average timestamp difference between two columns, this time in hours.

SELECT
    AVG(ABS(TIMESTAMP_DIFF(analyzed_table.clearance_date, analyzed_table.timestamp, HOUR))) AS actual_value, DATE_TRUNC(CAST(analyzed_table.`timestamp` AS date), week) AS time_period
FROM `bigquery-public-data`.`austin_crime`.`crime` AS analyzed_table
GROUP BY time_period
ORDER BY time_period

Finished executing a sensor for a check average_delay on the table austin_crime.crime using a sensor definition table/timeliness/average_delay, sensor result count: 157

Results returned by the sensor:
+------------------+-----------+
|actual_value      |time_period|
+------------------+-----------+
|478.33890214797145|2013-12-29 |
+------------------+-----------+
|514.0026212319792 |2014-01-05 |
+------------------+-----------+
|403.77358490566087|2014-01-12 |
+------------------+-----------+
|501.0707350901528 |2014-01-19 |
+------------------+-----------+
|465.6066024759285 |2014-01-26 |
+------------------+-----------+
|436.38818565400845|2014-02-02 |
+------------------+-----------+
|433.71428571428555|2014-02-09 |
+------------------+-----------+
|412.4756756756754 |2014-02-16 |
+------------------+-----------+
|446.2222222222224 |2014-02-23 |
+------------------+-----------+
|452.79754601226966|2014-03-02 |
+------------------+-----------+

Here actual_value is expressed in hours, and time_period is truncated into weeks.

Rule

Definition of the rule for this check can be found in lines 19-29:

18
19
20
21
22
23
24
25
26
27
28
rules:
  moving_average:
    low:
      max_percent_above: 10.0
      max_percent_below: 8.0
    medium:
      max_percent_above: 20.0
      max_percent_below: 20.0
    high:
      max_percent_above: 25.0
      max_percent_below: 25.0

Here we have changed the bound. Notice that for low the bounds are asymmetrical, which is acceptable. It is presented here just to show that it is possible.

Finished executing rules (thresholds) for a check average_delay on the table austin_crime.crime, verified rules count: 150

Rule evaluation results:
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+-----------------+------------------+------------------+------------------+------------------+
|actual_value      |expected_value    |time_period     |time_gradient|dimension_id|connection_hash    |connection_name|provider|table_hash         |schema_name |table_name|check_hash         |check_name   |quality_dimension|sensor_name                   |executed_at             |duration_ms|severity|rule_hash          |rule_name     |high_lower_bound  |high_upper_bound |medium_lower_bound|medium_upper_bound|low_lower_bound   |low_upper_bound   |
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+-----------------+------------------+------------------+------------------+------------------+
|412.4756756756754 |461.8421310314266 |2014-02-16T00:00|week         |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-24T07:02:16.600Z|1365       |1       |7342948477520614636|moving_average|346.38159827357   |577.3026637892833|369.47370482514134|554.2105572377119 |424.89476054891253|508.02634413456934|
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+-----------------+------------------+------------------+------------------+------------------+
|446.2222222222224 |455.6713241119578 |2014-02-23T00:00|week         |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-24T07:02:16.600Z|1365       |0       |7342948477520614636|moving_average|341.75349308396835|569.5891551399473|364.5370592895663 |546.8055889343493 |419.2176181830012 |501.2384565231536 |
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+-----------------+------------------+------------------+------------------+------------------+
|452.79754601226966|454.6214239019872 |2014-03-02T00:00|week         |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-24T07:02:16.600Z|1365       |0       |7342948477520614636|moving_average|340.9660679264904 |568.276779877484 |363.69713912158977|545.5457086823847 |418.25170998982827|500.083566292186  |
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+-----------------+------------------+------------------+------------------+------------------+
|492.84634760705313|454.4390361130155 |2014-03-09T00:00|week         |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-24T07:02:16.600Z|1365       |0       |7342948477520614636|moving_average|340.82927708476166|568.0487951412694|363.5512288904124 |545.3268433356186 |418.08391322397426|499.8829397243171 |
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+-----------------+------------------+------------------+------------------+------------------+
|372.0155239327293 |457.9306098852007 |2014-03-16T00:00|week         |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-24T07:02:16.600Z|1365       |1       |7342948477520614636|moving_average|343.4479574139005 |572.4132623565008|366.34448790816054|549.5167318622408 |421.2961610943847 |503.7236708737208 |
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+-----------------+------------------+------------------+------------------+------------------+
|376.4658227848098 |450.77101938916144|2014-03-23T00:00|week         |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-24T07:02:16.600Z|1365       |1       |7342948477520614636|moving_average|338.0782645418711 |563.4637742364519|360.61681551132915|540.9252232669937 |414.70933783802855|495.84812132807764|
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+-----------------+------------------+------------------+------------------+------------------+
|543.5795006570306 |445.0552350349805 |2014-03-30T00:00|week         |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-24T07:02:16.600Z|1365       |2       |7342948477520614636|moving_average|333.79142627623537|556.3190437937257|356.04418802798443|534.0662820419766 |409.4508162321821 |489.5607585384786 |
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+-----------------+------------------+------------------+------------------+------------------+
|432.6720000000002 |452.09268257941267|2014-04-06T00:00|week         |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-24T07:02:16.600Z|1365       |0       |7342948477520614636|moving_average|339.0695119345595 |565.1158532242658|361.67414606353015|542.5112190952951 |415.92526797305965|497.30195083735396|
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+-----------------+------------------+------------------+------------------+------------------+
|443.88932806324095|448.83076099741476|2014-04-13T00:00|week         |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-24T07:02:16.600Z|1365       |0       |7342948477520614636|moving_average|336.6230707480611 |561.0384512467684|359.06460879793184|538.5969131968977 |412.9243001176216 |493.7138370971563 |
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+-----------------+------------------+------------------+------------------+------------------+
|390.2374670184702 |443.8226686282191 |2014-04-20T00:00|week         |0           |1846543424268075406|connection_1   |bigquery|6504362088336968271|austin_crime|crime     |1027837643811265661|average_delay|timeliness       |table/timeliness/average_delay|2022-05-24T07:02:16.600Z|1365       |1       |7342948477520614636|moving_average|332.8670014711643 |554.7783357852738|355.0581349025753 |532.587202353863  |408.31685513796157|488.20493549104106|
+------------------+------------------+----------------+-------------+------------+-------------------+---------------+--------+-------------------+------------+----------+-------------------+-------------+-----------------+------------------------------+------------------------+-----------+--------+-------------------+--------------+------------------+-----------------+------------------+------------------+------------------+------------------+

Check summary

Check evaluation summary briefly informs us about check execution: cal

Check evaluation summary per table:
+------------+------------------+------+--------------+-------------+------------+---------------+-------------+
|Connection  |Table             |Checks|Sensor results|Valid results|Alerts (low)|Alerts (medium)|Alerts (high)|
+------------+------------------+------+--------------+-------------+------------+---------------+-------------+
|connection_1|austin_crime.crime|1     |157           |74           |56          |9              |11           |
+------------+------------------+------+--------------+-------------+------------+---------------+-------------+

When to use

This check be used when you have recorded timestamps of data processing events, and you would like to monitor an average timeliness of the data. For example if you had a data pipeline you can monitor an average timeliness of jobs executions.

Used sensor

table/timeliness/average_delay

Errors detected

Delayed events registered in a database.

Parameters

This checks has three parameters:

  • column1: str
    first column's name to calculate a timestamp difference
  • column2: str
    second column's name to calculate a timestamp difference
  • time_scale: str (optional, default = DAY)
    time scale to measure timestamp difference,the default value is DAY, acceptable values: MONTH, WEEK, DAY, HOUR, MINUTE, SECOND

Accepted rules

moving_average

Errors detected

Delayed periodical events in a database. Moving average is responsible for updating average delay and comparing actual_value to the defined thresholds.

Parameters

  • low:
    rule threshold for a low severity (1) alert
    • max_percent_above: float
      maximal percent value above the actual_value
    • max_percent_below: float
      maximal percent value below the actual_value
  • medium:
    rule threshold for a medium severity (2) alert
    • max_percent_above: float
      maximal percent value above the actual_value
    • max_percent_below: float
      maximal percent value below the actual_value
  • high:
    rule threshold for a high severity (3) alert
    • max_percent_above: float
      maximal percent value above the actual_value
    • max_percent_below: float
      maximal percent value below the actual_value