set_based.yaml

entities:
  TEST_DATA:
    source_database: BIGQUERY
    table_name: <table_id>
    dataset_name: <dataset_id>
    project_name: <project_id>
    columns:
      UNIQUE_KEY:
        name: unique_key
        data_type: INTEGER
      DESCRIPT:
        name: descript
        data_type: STRING
      DATE:
        name: date
        data_type: DATE
      TIME:
        name: time
        data_type: TIME
      ADDRESS:
        name: address
        data_type: STRING
      LON:
        name: longitude
        data_type: FLOAT
      LAT:
        name: latitude
        data_type: FLOAT
      LOCATION:
        name: location
        data_type: STRING
      TIMESTAMP:
        name: timestamp
        data_type: TIMESTAMP

  TAXI_TRIPS:
    source_database: BIGQUERY
    table_name: <table_id>
    dataset_name: <dataset_id>
    project_name: <project_id>
    columns:
      UNIQUE_KEY:
        name: unique_key
        data_type: STRING
      TAXI_ID:
        name: taxi_id
        data_type: STRING
      TRIP_START_TIMESTAMP:
        name: trip_start_timestamp
        data_type: TIMESTAMP
      TRIP_END_TIMESTAMP:
        name: trip_end_timestamp
        data_type: TIMESTAMP
      PAYMENT_TYPE:
        name: payment_type
        data_type: STRING
      FARE:
        name: fare
        data_type: FLOAT
      TIPS:
        name: tips
        data_type: FLOAT
      TOLLS:
        name: tolls
        data_type: FLOAT
      EXTRAS:
        name: extras
        data_type: FLOAT
      TRIP_TOTAL:
        name: trip_total
        data_type: FLOAT
      PICKUP_LOCATION:
        name: pickup_location
        data_type: STRING
      DROPOFF_LOCATION:
        name: dropoff_location
        data_type: STRING
 

rules:
  DISTINCT_VALUES:
    rule_type: CUSTOM_SQL_STATEMENT
    custom_sql_arguments:
      n_max
    params:
      custom_sql_statement: |-
        select count(distinct $column) as n
        from data
        having n > $n_max

  Z_SCORE_OUTLIER:
    rule_type: CUSTOM_SQL_STATEMENT
    custom_sql_arguments:
      z_limit
    params:
      custom_sql_statement: |-
        with stats as (
          select avg($column) as mu, stddev($column) as sigma
          from data
        ),
        z_scores as (
          select $column, mu, sigma, abs(mu - $column)/sigma as z
          from data
          join stats on true
        )
        select * 
        from z_scores
        where z > $z_limit


rule_bindings:
  DISTINCT_VALUES:
    entity_id: TEST_DATA
    column_id: UNIQUE_KEY
    row_filter_id: NONE
    rule_ids:
      - DISTINCT_VALUES:
          n_max: 1000

  OUTLIER_DETECTION:
    entity_id: TAXI_TRIPS
    column_id: TIPS
    row_filter_id: NONE
    rule_ids:
      - Z_SCORE_OUTLIER:
          z_limit: 3