How do query monitoring rules work in Amazon Redshift WLM, and what would you use one for?

## Why the rules exist WLM queues decide who runs. They cannot decide when a running query has gone wrong. An analyst who forgets a join condition produces a cross join that will run for hours, consume memory, spill gigabytes of temp blocks and starve the queue it is in. Query monitoring rules (QMR) are the mechanism that notices and acts. ## Structure of a rule Rules are declared inside the same `wlm_json_configuration` parameter, attached to a queue via a `rules` array. Each rule has: - a `rule_name`, - a `predicate` array of up to three conditions, each a `metric_name`, an `operator` and a `value`, combined with AND, - an `action`. ```json { "query_group": ["adhoc"], "auto_wlm": true, "priority": "low", "rules": [ { "rule_name": "kill_runaway_adhoc", "predicate": [ { "metric_name": "query_execution_time", "operator": ">", "value": 1800 }, { "metric_name": "nested_loop_join_row_count", "operator": ">", "value": 1000000 } ], "action": "abort" } ] } ``` A queue may carry several rules; each is evaluated independently. ## The metrics you can predicate on The metrics describe what a query is doing right now, not what the planner estimated. The commonly used ones are `query_execution_time` (seconds of run time), `query_cpu_time`, `query_blocks_read`, `query_temp_blocks_to_disk` (how much it has spilled), `scan_row_count`, `join_row_count`, `nested_loop_join_row_count`, `return_row_count`, `cpu_skew` and `io_skew` (how unevenly the work landed across slices), and `spectrum_scan_size_mb` for external scans. The last one is a genuine cost control: on Spectrum you pay per byte scanned in S3, so a rule that aborts an ad-hoc query above a scan threshold is money saved rather than just capacity protected. `nested_loop_join_row_count` deserves a special mention because it is the signature of an accidental cross join — a nested loop producing millions of rows is almost never intentional in a warehouse. ## The four actions - **log** — records the violation and lets the query continue. This is where every new rule should start: run it in log mode for a week, look at what it would have killed, then decide. - **hop** — moves the query to another matching queue under manual WLM, so a heavy query is demoted rather than killed; if there is no other matching queue the query is cancelled. - **abort** — cancels the query. The user gets an error, which is the point: an analyst learns immediately that the query was wrong. - **change_query_priority** — under automatic WLM, drops (or raises) the running query's priority so it yields to other work instead of being killed. This is often the kindest action for a long-but-legitimate report. ## Sampling, and what rules cannot do Query metrics are collected at intervals rather than continuously. A query that starts and finishes inside one interval may never be measured, so short queries are effectively invisible to QMR. This is why rules are framed around thresholds that only a genuinely misbehaving query crosses, and why QMR is not a scheduler — it is a circuit breaker. ## Where firings show up `STL_WLM_RULE_ACTION` has one row per rule action taken, naming the rule and the query. `SVL_QUERY_METRICS` and `SVL_QUERY_METRICS_SUMMARY` carry the metric values per query so you can calibrate thresholds against real traffic instead of guessing. A useful habit is to query the metrics summary for the 99th percentile of `query_execution_time` and `scan_row_count` on the queue you intend to guard, then set the threshold comfortably above it. ## Design guidance - **Guard the ad-hoc queue, not the ETL queue.** A nightly load legitimately runs for an hour; an interactive query does not. Put aggressive rules where humans write SQL, and none — or only logging — where scheduled jobs run. - **Start in log mode.** Every team that ships an abort rule on day one eventually kills something important. - **Combine predicates.** A rule on execution time alone kills slow-but-valid reports; combining execution time with a nested loop row count or a large spill targets the pathological shape specifically. - **Tell users what happened.** An aborted query surfaces as a generic error; document the rules so an analyst can recognise the cause instead of retrying it four times. QMR is a differentiator in interviews rather than a screening question — plenty of competent Redshift users never configure one. Being able to describe the predicate/action structure and, more importantly, the log-first rollout discipline, marks someone who has operated a shared cluster.