skip to content

What guardrails would you put on an analytical warehouse to stop one runaway query from starving everything else?

level: seniorimportance: should knowfreq 55%

answer

  1. stop it before it starts, if you can
  2. the planner's estimate is free to check
  3. one global timeout fits nobody
  4. demoting beats killing
  5. the account needs a limit too

basics

~20 s

Layer them: an admission-time check on estimated bytes scanned, a per-class statement timeout, caps on memory grant and spill, a result-size limit, and a spend monitor that suspends compute. Prefer demoting a query to killing it where possible.

solid answer

~50 s

Guardrails belong at three moments. **Before admission**, reject or reroute on the optimizer's estimated bytes to scan — the cheapest possible stop, since nothing has executed. **During execution**, enforce a statement timeout scoped per class (minutes for the interactive lane, hours for batch), a cap on memory grant and on how much a query may spill, and a limit on result rows returned so nobody accidentally pulls a billion rows into a client. **Across the account**, a spend or usage monitor that alerts and, at a higher threshold, suspends the pool, so a defect becomes a paused workload rather than an invoice. Prefer actions that degrade rather than destroy: demote an over-running interactive query into the batch class instead of cancelling it. And set the limits per class — a single global timeout is either too tight for ETL or too loose to protect dashboards.

code

text · 15 lines
text
# illustrative guardrail policy, per admission class
interactive:
  admit_if   estimated_scan_bytes < 200 GB    else route -> batch
  abort_if   runtime > 5 min
  abort_if   returned_rows > 1,000,000
  grant      small

batch:
  abort_if   runtime > 4 h
  abort_if   spilled_bytes > 500 GB
  grant      large

account:
  alert_if   daily_spend > budget * 0.8
  suspend_if daily_spend > budget

go deeper

for a junior

Know that warehouses can cancel a query that runs too long or returns too many rows, and that these limits exist to protect other users rather than to punish yours.

for a middle

Explain where each guardrail sits — admission-time estimate check, runtime timeout, memory and spill caps, result-size limit — and why the planner's estimate is the cheapest place to stop a bad query.

for a senior

Demonstrate how you would derive thresholds from measured distributions, roll a rule out in log-only mode first, and choose between demoting and aborting so users still get answers where possible.

for a principal

Own the account-level position: what the spend ceiling is, what happens when it is reached, and the policy that treats a rising rate of guardrail trips as a signal to redesign the workload rather than to loosen the limits.

## Three moments to intervene A runaway query — a missing join predicate producing a cross join, a filter that defeats pruning, a client fetching an unbounded result — can consume a warehouse's memory, I/O and budget. Guardrails are cheapest the earlier they fire. ### 1. At admission, before any work happens The planner produces an estimate of how much data the query will read before execution begins. A gate on that estimate is the highest-leverage guardrail available: it costs nothing to evaluate and it stops the query before it has consumed a single second of compute. Typical policies: - **Reject** queries whose estimated scan exceeds a threshold for the class, with an error explaining the limit and how to filter. - **Reroute** them into a batch class with a large memory grant and low concurrency, so they run without harming anyone. - **Require confirmation** for interactive tools — many clients can show the estimate to the analyst before submission. The weakness is estimate accuracy. Stale statistics, correlated predicates or opaque user-defined functions all produce wrong estimates, in both directions, so an admission gate must be backed by runtime enforcement. ### 2. During execution - **Statement timeout, scoped per class.** This is the fundamental one, and the important detail is *per class*. A single global timeout is wrong for everyone: five minutes kills legitimate nightly transforms, four hours does nothing to protect a dashboard. Set minutes for interactive, hours for batch. - **Memory grant cap.** Bounds how much of the node's memory one query may hold, so it cannot squeeze every neighbour into spilling. - **Spill limit.** A query that has already written hundreds of gigabytes of intermediate data to local disk is usually pathological — a bad join. Abort it, since it will otherwise saturate the disks everyone shares. - **Rows-returned limit.** Protects against a client pulling an unbounded result set, which stresses the coordinator and the network rather than the workers. - **CPU-time or elapsed-time growth relative to the class's norm**, where the platform supports it, catches queries that are slow for reasons the byte estimate did not predict. ### 3. Across the account Per-query limits do not stop **many** cheap-but-wasteful queries: a dashboard set to auto-refresh every ten seconds across hundreds of open tabs may never trip a single-query rule while consuming enormous capacity. Add usage and spend monitors at the pool or account level that alert at one threshold and **suspend compute** at a higher one. The design goal is that the worst outcome of a defect is a paused workload with an alert, not an unbounded bill discovered at month end. ## Abort, demote, or just log Every rule needs an action, and killing is the bluntest one: - **Log/alert only** is the right first setting for any new rule. Run it in observation mode long enough to see what it would have caught; you will nearly always find the initial threshold catches legitimate work. - **Demote** — move the query into a lower-priority class and let it continue — frees the protected lane without destroying work. For an interactive class this is usually the correct action: the analyst still gets an answer, just later. - **Abort** is right where the query is provably pathological (a runaway spill, an estimate far beyond anything the class should ever run) or where the class must never run long, such as a lane serving a customer-facing product. Always return an error message that names the limit that fired and the value that tripped it. A guardrail whose error says only "query cancelled" generates a support ticket instead of a fix. ## Setting the thresholds Derive them from data, not from taste. Take the distribution of runtime, bytes scanned and spill for each class over a representative period, and set the limit above the legitimate tail — commonly around the 99th percentile — rather than at a round number someone liked. Then review after every rollout: the guardrail that was generous last quarter can become a blocker after the fact table doubles. ## What guardrails do not do They bound damage; they do not create capacity or improve queries. A warehouse where guardrails fire constantly is telling you something upstream is wrong — no pruning on the biggest table, dashboards querying raw facts, or a class whose limits were never sized for the data it now holds. Treat a rising rate of guardrail trips as a workload-design signal, not as a reason to raise the limits.

  • Why is a single global statement timeout usually a bad policy?
    Because the legitimate runtime range in a warehouse spans several orders of magnitude. A timeout short enough to protect a two-second dashboard kills every nightly transform; one generous enough for a four-hour rebuild lets a runaway interactive query hold capacity for hours. Timeouts must be scoped per admission class, matched to what that class is meant to run, with the interactive lane strict and the batch lane loose.
  • How would you choose the threshold for an abort rule without blocking legitimate work?
    Measure first. Take the distribution of the metric — runtime, bytes scanned, spill — across the class over a representative window, then set the threshold above the legitimate tail, typically near the 99th percentile. Deploy the rule in log-only mode and review what it would have caught before making it enforce. Revisit after data growth or a major workload change, since a comfortable limit becomes a blocker as tables grow.
  • Which failure do per-query limits fail to catch?
    Aggregate waste from many individually reasonable queries — a dashboard auto-refreshing every ten seconds across hundreds of open sessions, or a retry loop resubmitting a failed job. Each query is well inside every per-query limit while the total consumption is enormous. That needs account or pool-level usage and spend monitors, plus rate limiting or caching at the application tier that generates the traffic.

A circuit breaker does not make the wiring better; it guarantees that a fault trips a switch instead of burning the house down. Repeated trips are a message about the wiring, not an argument for a bigger breaker.

saying these in an interview costs you the question

  • Relies on a single global timeout for every workload
  • Ignores the optimizer's estimate as an admission-time gate
  • Aborts queries where demotion would serve the user better
  • Assumes per-query limits also cap total spend
  • Raises the limits whenever a guardrail fires instead of fixing the query

context

resolving links…