current_date() - 1

current_date() - 1

anomaly_exclude_metrics

- elementary.volume_anomalies: # Monitors the row count of table over time per time bucket
              # Known issue that detection date is included in training period: <https://github.com/elementary-data/elementary/issues/1491>
              # The following suggestion excluded the detection date from both the detection and training period <https://github.com/elementary-data/elementary/issues/1491#issuecomment-2384317102>
              # anomaly_exclude_metrics: "MAX(bucket_end) OVER (partition by metric_name, full_table_name, column_name, dimension, dimension_value, bucket_seasonality order by bucket_end asc rows between unbounded preceding and unbounded following) = bucket_end"
              description: |
                An anomaly detection test to compare volume of tickets created during the last complete day, against the
                average of the last 7 weeks for that same day (within 3 standard deviations). This is because volumes 
                vastly differ depending on the day of the week.
              severity: warn
              timestamp_column: created_at # We could use received_at, but we want to capture volume of new tickets
              anomaly_sensitivity: 3 # Default
              seasonality: day_of_week # Volumes differ day to day so we want to compare against the same day of the week
              detection_period: # We want to detect anomalies in volume from the last complete day
                period: day
                count: 1
              training_period: # We want a larger training period to detect anomalies against the previous day
                period: week
                count: 1 # When seasonality: day_of_week is configured, training_period is by default multiplied by 7.
              time_bucket: # We want to count new rows per day
                period: day
                count: 1
              tags: [ 'elementary' ]

The last row_count value is 46409.000. The average for this metric is 44981.000.

select count(1) from my_table 
where created_at::date = '2025-02-19'; 
-- 46409

with cte as (
    select 
        created_at::date as created_dt,
        count(1) as c 
    from my_table 
    where 1=1 
    and created_dt > '2025-02-19'::date - 7*7
    and dayname(created_dt) = 'Wed'
    group by created_dt
    order by created_dt
)
select avg(c)::int from cte
;

with cte as (
    select 
        created_at::date as created_dt,
        count(1) as c 
    from my_table 
    where 1=1 
    and created_dt < '2025-02-19'
    and created_dt > '2025-02-19'::date - 7*7
    and dayname(created_dt) = 'Wed'
    group by created_dt
    order by created_dt
)
select avg(c)::int from cte
;