- name: stg_order

tests:

- elementary.dimension_anomalies:

dimensions:

- Status

- Price

where_expression: "Status in ('Delivered') and Price >= 50"

np,Date,Price,Status,event_occured
1001,2024-01-01,50.00,Delivered,2024-01-01
1002,2024-01-01,75.00,Shipped,2024-01-01
1003,2024-01-02,60.00,Delivered,2024-01-01
1004,2024-01-02,45.00,Returned,2024-01-02
1005,2024-01-03,80.00,Shipped,2024-12-31
1006,2024-01-03,30.00,Delivered,2024-12-31
1007,2024-01-04,65.00,Not Shipped,2024-01-04
1008,2024-01-04,55.00,Delivered,2024-01-04
1009,2024-01-05,90.00,Shipped,2024-01-04
1010,2024-01-05,40.00,Delivered,2024-01-05
1011,2023-12-31,70.00,Shipped,2023-12-31
1012,2023-12-31,25.00,Delivered,2023-12-31
1013,2023-12-30,85.00,Delivered,2023-12-30
1014,2023-12-30,50.00,Shipped,2023-12-30
1015,2023-12-29,95.00,Delivered,2023-12-29
1016,2023-12-29,35.00,Shipped,2023-12-29
1017,2023-12-28,60.00,Returned,2023-12-28
1018,2023-12-28,75.00,Not Shipped,2023-12-28
1019,2023-12-27,55.00,Delivered,2023-12-26
1020,2023-12-27,40.00,Shipped,2023-12-26
1021,2023-12-30,85.00,Delivered,2023-12-30
1022,2023-12-30,50.00,Not Shipped,2023-12-30
1023,2023-12-29,95.00,Delivered,2023-12-29
1024,2023-12-29,35.00,Shipped,2023-12-29
1025,2023-12-28,60.00,Returned,2023-12-28
1026,2023-12-28,75.00,Shipped,2023-12-28
1027,2023-12-27,55.00,Delivered,2023-12-27
1028,2023-12-27,40.00,Shipped,2023-12-27

- name: stg_order
    tests:
      - elementary.dimension_anomalies:
          timestamp_column: Date
          dimensions:
            - Status
            - Price
          where_expression: "Status in ('Delivered') and Price >= 50"
          training_period:
            period: day
            count: 14`

-- depends_on: `hive_metastore`.`dbt_dataops_elementary`.`monitors_runs`
    -- depends_on: `hive_metastore`.`dbt_dataops_elementary`.`data_monitoring_metrics`
    -- depends_on: `hive_metastore`.`dbt_dataops_elementary`.`dbt_run_results`
        
        

        
        
        
        

        
        
        
        


        
        
        

        

        
        
        


        

        
        
        

        
        
        


        select * from (with anomaly_scores as (
        select
            id,
            metric_id,
            test_execution_id,
            test_unique_id,
            detected_at,
            full_table_name,
            column_name,
            metric_name,
            anomaly_score,
            anomaly_score_threshold,
            anomalous_value,
            bucket_start,
            bucket_end,
            bucket_seasonality,
            metric_value,
            min_metric_value,
            max_metric_value,
            training_avg,
            training_stddev,
            training_set_size,
            training_start,
            training_end,
            dimension,
            dimension_value,
            
    case
        when dimension is not null then 
    'The last ' || metric_name || ' value for dimension ' || dimension || ' - ' ||
    case when dimension_value is null then 'NULL' else dimension_value end || ' is ' || cast(round(cast(metric_value as decimal(28,6)), 3) as string) ||
    '. The average for this metric is ' || cast(round(cast(training_avg as decimal(28,6)), 3) as string) || '.'

        when metric_name = 'freshness' then 
    'Last update was at ' || anomalous_value || ', ' || cast(abs(round(cast(metric_value/3600 as decimal(28,6)), 2)) as string) || ' hours ago. Usually the table is updated within ' || cast(abs(round(cast(training_avg/3600 as decimal(28,6)), 2)) as string) || ' hours.'

        when column_name is null then 
    'The last ' || metric_name || ' value is ' || cast(round(cast(metric_value as decimal(28,6)), 3) as string) ||
    '. The average for this metric is ' || cast(round(cast(training_avg as decimal(28,6)), 3) as string) || '.'

        when column_name is not null then 
    'In column ' || column_name || ', the last ' || metric_name || ' value is ' || cast(round(cast(metric_value as decimal(28,6)), 3) as string) ||
    '. The average for this metric is ' || cast(round(cast(training_avg as decimal(28,6)), 3) as string) || '.'

        else null
    end as anomaly_description
,
            max(bucket_end) over (partition by test_execution_id) as max_bucket_end
        from `hive_metastore`.`dbt_dataops_elementary`.`test_35e0c4b410_elementary_dimension_anomalies_stg_order_Status__Price__Date__day__14__anomaly_scores__tmp_20240109084610812846`
      ),
      anomaly_scores_with_is_anomalous as (
        select
          *,
case when
            anomaly_score is not null and
            (
              
  (
    metric_value = 0 and 
    
      1 = 2
    
  )
 or
              (
                case when metric_name IN 
        ( 'freshness' ,  'event_freshness'  )
 then
            anomaly_score > 3
    else
        
        abs(anomaly_score) > 3
    

     end and
                
      (1 = 1)
  

  and

  
      (1 = 1)
  
              )
            ) and
            bucket_end >= 
    dateadd(day, cast('-2' as integer), cast(max_bucket_end as timestamp))

          then TRUE else FALSE end as is_anomalous
        from anomaly_scores
      )

      select
        metric_value as value,
        training_avg as average,
        
        case
        when is_anomalous = TRUE and 'both' = 'spike' then
         lag(metric_value) over (partition by full_table_name, column_name, metric_name, dimension, dimension_value, bucket_seasonality order by bucket_end)
        when is_anomalous = TRUE and 'both' != 'spike' then
         lag(min_metric_value) over (partition by full_table_name, column_name, metric_name, dimension, dimension_value, bucket_seasonality order by bucket_end)
        when 'both' = 'spike' then metric_value
        else min_metric_value end as min_value,
        case
        when is_anomalous = TRUE and 'both' = 'drop' then
         lag(metric_value) over (partition by full_table_name, column_name, metric_name, dimension, dimension_value, bucket_seasonality order by bucket_end)
        when is_anomalous = TRUE and 'both' != 'drop' then
         lag(max_metric_value) over (partition by full_table_name, column_name, metric_name, dimension, dimension_value, bucket_seasonality order by bucket_end)
        when 'both' = 'drop' then metric_value
        else max_metric_value end as max_value,
        bucket_start as start_time,
        bucket_end as end_time,
        *
      from anomaly_scores_with_is_anomalous
      order by bucket_end, dimension_value) results
    where is_anomalous = true

training_period