Query Cost Ceiling
also called Scan Limit, Per-Query Byte Cap
A pre-execution limit on the bytes or credits a single query may consume - converting an unbounded bill into a failed query the author can see and fix.
An analyst joins two large tables without a partition filter on a Friday afternoon, the engine scans 180 TB, and the invoice arrives three weeks later. Nothing in that sequence is a monitoring failure: the query was admitted, it ran correctly, and the only signal was money, delivered late to someone who could not act on it.
A query cost ceiling moves the control before execution. The engine estimates what the query will read, compares it to a limit for that user or role, and refuses to start if it exceeds it. The analyst gets an error in two seconds instead of a bill in three weeks.
Why it matters
Analytical cost is dominated by a few extreme queries, not by the median. A platform where p50 scans 200 MB and p99 scans 400 GB can still have a monthly top query at 100 TB, and that distribution makes after-the-fact controls structurally useless - averages, budgets and monthly reviews all react after the spend.
A rejection also names the cause at the moment of authorship, when the author still has the query in front of them and remembers which filter they forgot.
Implementation patterns
- Enforce on the planner's estimate, before execution - a dry-run or explain step returning estimated bytes, which most consumption-priced engines expose. Enforcing on actuals mid-flight wastes the work already done, though a mid-query abort is a useful second layer.
- Set the ceiling from your own distribution: roughly 3 to 5 times the p99 of legitimate queries, measured over a fortnight, so real work is never blocked and the accident is. Keep it per role - interactive users get a low one, a reviewed pipeline runs without one, because code review bounds its cost instead.
- A named escape hatch - a role or labelled session that lifts the ceiling for a declared reason - because a ceiling with no exception path is removed entirely after the first legitimate rejection.
- An error message that teaches: the estimate, the limit and the probable cause (no partition filter,
SELECT *, a cross join), since "quota exceeded" produces a support ticket rather than a fix. Pair it with a statement timeout.
Industry example
Consumption-priced analytical services have shipped this control for years because customers asked for it after an invoice surprise: per-query maximum-bytes-billed settings, per-user daily quotas and statement timeouts are standard in the major cloud warehouses and query engines as of 2025. It is the admission-control idea that protects operational systems from overload, applied to cost rather than latency.
Failure scenarios
- Evasion by splitting: the rejected query is rerun as twelve monthly queries costing the same. The ceiling needs a per-user daily quota beside it.
- Dashboards failing silently when a scheduled tile exceeds the ceiling after the table grows and the BI tool renders an empty panel.
- Estimates wrong in the wrong direction: engines under-estimate some join shapes, so a ceiling can admit the query it exists to block.
- Blocked legitimate work moving off-platform - someone extracts to a laptop - which is worse for cost and security than the query was.
- A ceiling on pipelines, so a nightly model fails at 02:00 because a partition grew.
Trade-offs
| Choose | Gains | Pays |
|---|---|---|
| Low ceiling for everyone | Cost is bounded | Frequent rejections and a workaround culture |
| Interactive roles only | Accidents blocked where they happen | Pipelines still need review to stay bounded |
| No ceiling plus reporting | Nothing is blocked | Accidents are paid in full and found late |
When not to use it
When pricing is not per byte. On reserved or committed capacity a large scan costs zero pounds at the margin and nonzero queueing, so the control is priority and admission by workload class, not a byte limit. It is also wrong as a first control on a small platform: if the monthly bill is a few hundred pounds, a statement timeout plus a weekly look at the most expensive queries is enough, and a ceiling adds friction for six analysts who are not the problem.
Interview question
Q: "Leadership wants a policy after one query cost £4,000. You can ship one control this week. Which one, what number do you set it to, and what will people do to get around it?"
What a strong answer covers: a pre-execution ceiling on interactive roles only, with the number derived from the observed p99 rather than invented; the escape hatch and why a ceiling without one does not survive; the splitting evasion and the daily quota that answers it; testing scheduled queries against the ceiling before enabling it; and the admission that the real saving comes from layout and retention, with the ceiling as the stop against accidents.
Quick check
Quiz: Why enforce the ceiling on the planner's estimate rather than on bytes actually read? The estimate exists before any work is done, so the query is refused for free - enforcing on actuals means the money is already spent when the limit trips.
Flashcard: Where does a per-query cost ceiling belong and where not? — On interactive and ad-hoc roles at roughly 3 to 5 times the p99 legitimate scan; not on reviewed pipelines, where a rejection breaks a freshness SLA to save a trivial amount.