timeliness_delayed_ingestion.yaml

# Copyright 2022 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
entities:
  INGESTION_TABLE_DAY_LEVEL:
    source_database: BIGQUERY
    resource_type: BIGQUERY
    table_name: ingestion_day_level
    dataset_name: <my_bigquery_dataset_id>
    project_name: <my-gcp-project-id>
    columns:
      INVOICE_ID:
        name: invoice_id
        data_type: FLOAT
        description: |-
          invoice id
      DATE_OF_DAY:
        name: date_of_day
        data_type: DATE
        description: |-
          ingestion date

  INGESTION_TABLE_MONTH_LEVEL:
    source_database: BIGQUERY
    resource_type: BIGQUERY
    table_name: ingestion_month_level
    dataset_name: <my_bigquery_dataset_id>
    project_name: <my-gcp-project-id>
    columns:
      CLIENT_CD:
        name: client_cd
        data_type: INTEGER
        description: |-
          client cd
      MONTH_ID:
        name: month_id
        data_type: INTEGER
        description: |-
          month id %y%m

  INGESTION_TABLE_TIMESTAMP_LEVEL:
    source_database: BIGQUERY
    resource_type: BIGQUERY
    table_name: ingestion_timestamp_level
    dataset_name: <my_bigquery_dataset_id>
    project_name: <my-gcp-project-id>
    columns:
      DANA_INGESTION_TIMESTAMP:
        name: dana_ingestion_timestamp
        data_type: TIMESTAMP
        description: |-
          ingestion timestamp
      SALES_MANAGER_ID:
        name: salesManagerId
        data_type: STRING
        description: |-
          sales manager id


row_filters:
  NONE:
    filter_sql_expr: 'True'


rule_dimensions:
  - timeliness
  - correctness
  - integrity
  - conformity
  - completeness
  - uniqueness
  - accuracy
  - validity


rules:
  NO_DELAYED_INGESTION_DAY_LEVEL:
    rule_type: CUSTOM_SQL_STATEMENT
    dimension: timeliness
    params:
      custom_sql_arguments:
        - ingestion_date_day
        - elapsed_time_days
      custom_sql_statement: |-
        select * from
        (select count(*) as n
          from data a
          where $ingestion_date_day >= date_sub(current_date(), interval $elapsed_time_days day) 
        )
        where n = 0

  NO_DELAYED_INGESTION_MONTH_LEVEL:
    rule_type: CUSTOM_SQL_STATEMENT
    dimension: timeliness
    params:
      custom_sql_arguments:
        - ingestion_date_month
        - elapsed_time_months
      custom_sql_statement: |-
        select * from
        (select count(*) as n
          from data a
          where parse_date('%Y%m',  cast($ingestion_date_month as string)) >= date_sub(date_trunc(current_date(), month), interval $elapsed_time_months month) 
        )
        where n = 0

  NO_DELAYED_INGESTION_TIMESTAMP_LEVEL:
    rule_type: CUSTOM_SQL_STATEMENT
    dimension: timeliness
    params:
      custom_sql_arguments:
        - ingestion_timestamp
        - elapsed_time_hours
      custom_sql_statement: |-
        select * from
        (select count(*) as n
          from data a
          where $ingestion_timestamp >= timestamp_sub(current_timestamp(), interval $elapsed_time_hours hour) 
        )
        where n = 0


rule_bindings:
  T1_NO_DELAYED_INGESTION_DAY_LEVEL_SHOULD_FAIL:
    entity_id: INGESTION_TABLE_DAY_LEVEL
    column_id: INVOICE_ID
    row_filter_id: NONE
    rule_ids:
      - NO_DELAYED_INGESTION_DAY_LEVEL:
          ingestion_date_day: date_of_day
          elapsed_time_days: 1
    metadata:
      brand: one

  T1_NO_DELAYED_INGESTION_DAY_LEVEL_SHOULD_SUCCEED:
    entity_id: INGESTION_TABLE_DAY_LEVEL
    column_id: INVOICE_ID
    row_filter_id: NONE
    rule_ids:
      - NO_DELAYED_INGESTION_DAY_LEVEL:
          ingestion_date_day: date_of_day
          elapsed_time_days: 11
    metadata:
      brand: one

  T1_NO_DELAYED_INGESTION_MONTH_LEVEL_SHOULD_SUCCEED:
    entity_id: INGESTION_TABLE_MONTH_LEVEL
    column_id: CLIENT_CD
    row_filter_id: NONE
    rule_ids:
      - NO_DELAYED_INGESTION_MONTH_LEVEL:
          ingestion_date_month: month_id
          elapsed_time_months: 1
    metadata:
      brand: one

  T1_NO_DELAYED_INGESTION_MONTH_LEVEL_SHOULD_FAIL:
    entity_id: INGESTION_TABLE_MONTH_LEVEL
    column_id: CLIENT_CD
    row_filter_id: NONE
    rule_ids:
      - NO_DELAYED_INGESTION_MONTH_LEVEL:
          ingestion_date_month: month_id
          elapsed_time_months: 0
    metadata:
      brand: one

  T1_NO_DELAYED_INGESTION_TIMESTAMP_LEVEL_SHOULD_SUCCEED:
    entity_id: INGESTION_TABLE_TIMESTAMP_LEVEL
    column_id: SALES_MANAGER_ID
    row_filter_id: NONE
    rule_ids:
      - NO_DELAYED_INGESTION_TIMESTAMP_LEVEL:
          ingestion_timestamp: dana_ingestion_timestamp
          elapsed_time_hours: 1200
    metadata:
      brand: one

  T1_NO_DELAYED_INGESTION_TIMESTAMP_LEVEL_SHOULD_FAIL:
    entity_id: INGESTION_TABLE_TIMESTAMP_LEVEL
    column_id: SALES_MANAGER_ID
    row_filter_id: NONE
    rule_ids:
      - NO_DELAYED_INGESTION_TIMESTAMP_LEVEL:
          ingestion_timestamp: dana_ingestion_timestamp
          elapsed_time_hours: 10
    metadata:
      brand: one