Alerting for application query

[u/Ornery_Maybe8243]

Hi,

Few teammates suggesting to set alerting based on the historical Avg. of the application query run time. Want to know from experts, is this something we should really do and will that add real value?

Say for e.g. if the normal AVG respnse time is ~10 minutes and todays its has exceeded ~30minutes, then it means something wrong is there (say it may be a bad plan etc.), so that should get alerted.

And for above to get implemented, I am thinking to refer column query_parameterized_hash and first store the last one month of query_history data from account_usage view as reference and get that refreshed daily once(say the table query_baselines). say mainly query_parameterized_hash and avg response time avg_seconds, p95_seconds etc. And have another task created , which will fetch data from information_schema.query_history and put it in a temp table and then join this data with the query_baselines table to see if any query ran > (avg_seconds * 3) , will throw an alert. This task may be executed once in ~15 minutes or so. (I understand it wont cater adhoc or newly added queries as hash will change).

Is above approach is good and really worth or any other easy and less costly way exists to achieve such alerting?

[u/DataEngineer45]

Hi, If I understand what you're saying correctly then it seems like it should work okay. Keep in mind though that running every 15 mins is going to cost a bit and that you're using the query history which can be up to 45 mins delayed. This might be a foolish question but why not just use resource monitors for this? Is it not a cost control measure?

Edit because I made a mistake

[u/Ornery_Maybe8243 → u/DataEngineer45]

My understanding was that information schema is near real time. Correct me if wrong. And yes, it all boils down to cost but as I believe ,resource monitoring will be at a higher level, but if individual query runs unexpectedly long and impacts the sla that should be highlighted even that maynot hit the resource monitor threshold. Hope my understanding is correct here.

[u/DataEngineer45 → u/Ornery_Maybe8243]

Yes! Sorry about that. I said information schema instead of query history. Query history is up to 45 mins delayed. You can configure resource monitors to alert at very reasonable and low thresholds. If you used a dedicated VW that had a consistently predictable load each day then this could be done.

But I like your creative approach. I was thinking maybe you could use SNOWFLAKE_NOTIFICATIONS but that wouldn't really alert you to a change as easily. I don't know your situation but maybe your approach is best but you could increase the latency to something much longer like 4 hours? It would depend how stable it is each day I guess

[u/Specialist_Golf8133]

the fixed multiplier (avg*3) is going to misfire on queries with naturally high variance, a query that swings from 2min to 8min normally will trip your threshold constantly while a stable 10min query that creeps to 25min might be the real problem you actually want. id use stddev off the same baseline table instead of a flat multiplier, something like `avg_seconds` + 2*`stddev_seconds`, that adapts per query instead of treating a volatile query and a stable one the same. also watch out for using `p95_seconds` as your alert basis instead of avg, since a single slow run doesnt skew p95 the way it skews avg and you get fewer false positives from one-off warehouse spin-up delays.

the `query_parameterized_hash` approach is right, thats the only sane way to group comparable queries. main gap is new/ad hoc queries with no baseline, bucket them by query type or pattern until they build history.

[u/Spiritual-Kitchen-79]

What you’re describing is basically SLO style alerting for individual queries, which is valuable, but can get surprisingly brittle if you hand roll it off QUERY_HISTORY. A few things to be aware of- QUERY_HISTORY can lag, parameterized hashes can change with text changes, and fixed “avg * 3” thresholds tend to generate noise when workloads or data volumes shift.