Skip to content

Current delay

Description

The current_delay sensor calculates the timestamp difference between last record (the newest one appointed by MAX() function) and a CURRENT_TIMESTAMP().

When to use

Current delay check is mostly used, when we deal with a dataset that is updated frequently.

Used sensor

Current delay

Accepted rules

Max count

Although there are several options for the rule choice, the most logical one to use is max_count...

Parameters

This checks has two parameters that configure date format to parse:

  • column: str
    name of the column to calculate timestamp difference with CURRENT_TIMESTAMP()
  • time_scale: str (Optional)
    time scale to measure timestamp difference,the default value is DAY, acceptable values: MONTH, WEEK, DAY, HOUR, MINUTE, SECOND (default DAY)

How to use

 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
# yaml-language-server: $schema=https://cloud.dqo.ai/dqo-yaml-schema/TableYaml-schema.json
apiVersion: dqo/v1
kind: table
spec:
  target:
    schema_name: dqo_ai_test_data
    table_name: test_average_delay_5877745271850405297
  time_series:
    mode: current_time
    time_gradient: day
  checks:
    timeliness:
      current_delay:
        parameters:
          column: "timestamp_column"
        rules:
          max_count:
            low:
              max_value: 0.0
            medium:
              max_value: 1.0
            high:
              max_value: 2.0
  columns:
    id:
      type_snapshot:
        column_type: INT64
        nullable: true
    dates:
      type_snapshot:
        column_type: DATETIME
        nullable: true
    timestamp_column:
      type_snapshot:
        column_type: TIMESTAMP
        nullable: true

SELECT
    TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),
                   MAX(analyzed_table.timestamp_column),
                   DAY) AS actual_value, CAST(CURRENT_TIMESTAMP() AS date) AS time_period
FROM `dqo-ai-testing`.`dqo_ai_test_data`.`test_average_delay_5877745271850405297` AS analyzed_table
GROUP BY time_period
ORDER BY time_period

 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
# yaml-language-server: $schema=https://cloud.dqo.ai/dqo-yaml-schema/TableYaml-schema.json
apiVersion: dqo/v1
kind: table
spec:
  target:
    schema_name: dqo_ai_test_data
    table_name: test_average_delay_5877745271850405297
  time_series:
    mode: current_time
    time_gradient: day
  checks:
    timeliness:
      current_delay:
        parameters:
          column: "dates"
          time_scale: "HOUR"
        rules:
          max_count:
            low:
              max_value: 0.0
            medium:
              max_value: 1.0
            high:
              max_value: 2.0
  columns:
    id:
      type_snapshot:
        column_type: INT64
        nullable: true
    dates:
      type_snapshot:
        column_type: DATETIME
        nullable: true
    timestamp_column:
      type_snapshot:
        column_type: DATETIME
        nullable: true

SELECT
    TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),
                   SAFE_CAST(MAX(analyzed_table.dates) AS TIMESTAMP),
                   HOUR) AS actual_value, CAST(CURRENT_TIMESTAMP() AS date) AS time_period
FROM `dqo-ai-testing`.`dqo_ai_test_data`.`test_average_delay_5877745271850405297` AS analyzed_table
GROUP BY time_period
ORDER BY time_period