Hi. Could someone explain why Freshness tests have anomaly sensitivity if we just need to calculate time between updates, not data volumne? Thanks

Hey, freshness anomaly tests search for anomalies in the time between updates - any row that's added to the table is considered an update 🙂

Ok, but why do I get an error when there is an spike or drop in data for freshness tests if only time between update matters?

Can you please share a screenshot of the graph with the spikes?

1000296331.jpg

These are 12 hour buckets, correct?

Yes, I think so

This graph reflects the time between updates, it doesn't have anything to do with the volume 🙂 For every bucket (12 hours), the max amount of time between updates is the value of the bucket. Maybe smaller time buckets can provide better insights, and perhaps a seasonality feature because it seems like there's a pattern here. If an anomaly test doesn't fit your needs, maybe a more simple custom SQL test can be better for this case. Of course it will also show up in the Elementary UI.

Thanks!

Maybe something like this would work better? Testing against a clear threshold:

{% test freshness_not_stale(model, column_name, max_age_hours) %}

WITH latest_update AS (
    SELECT MAX({{ column_name }}) AS max_time
    FROM {{ model }}
),

check AS (
    SELECT
        max_time,
        CURRENT_TIMESTAMP AS current_time,
        DATETIME_DIFF(CURRENT_TIMESTAMP, max_time, MINUTE) AS minutes_diff
    FROM latest_update
)

SELECT *
FROM check
WHERE minutes_diff > {{ max_age_hours * 60 }}

{% endtest %}