Measure the top-1% share of your query log · Cost Mechanics

Simulate a modelNeeds Python with NumPy

Problem

Warehouse spend is rarely spread evenly across queries. Per-query cost (bytes scanned, credits) is heavy-tailed: most queries are cheap, and a few scan whole tables. So a small share of queries can carry most of the bill, which makes them the place to look first, and makes a blunt cap (block anything above the 99th percentile) a risky tool: it blocks 1% of queries by construction, including legitimate ones.

Run the SQL from the value of pruning against your warehouse's query history (most warehouses expose bytes scanned or credits per query). Report the share of total spend that the most expensive 1% of queries carries. Then decide what a cap at the 99th percentile would have blocked, and how many of those blocked queries were legitimate.

This lab has no warehouse history to read, so the script prints the query for you to run on yours and simulates histories instead: 100,000 queries whose cost is log-normal, for five tail weights (σ). The numbers below are from those simulated histories, not from a real warehouse.

The question. How much of the total spend do the most expensive 1% of queries carry, and how does that depend on how heavy the tail is?

Predict

With a mild tail, where the p99 query costs about 10× the median (σ = 1), what share of spend do the top 1% of queries carry?

Under 15%15 to 35%35 to 50%A majority

Run it

1Install uv once. It fetches Python and the lab's packages for you.
Terminal
```
curl -LsSf https://astral.sh/uv/install.sh | sh
```

2Get the script. It is the whole lab: read it, then download it.

measure-the-top-1-share-of.py50 linesDownload

View the code

measure-the-top-1-share-of.py

# /// script
# requires-python = ">=3.10"
# dependencies = ["numpy"]
# ///
# Copyright (c) 2026 Aayush Sharma · SPDX-License-Identifier: MIT
# https://thedataengineering.com/lab-code/LICENSE.txt
"""Measure the top-1% share of spend.

On your warehouse, run the query below against its query history. Without
one, this script simulates query histories whose per-query cost is
log-normal with a range of tail weights (sigma), and reports the share of
spend carried by the most expensive 1% of queries and what a cap at the 99th
percentile would block.
"""
import argparse, json
import numpy as np

SQL = """-- the share of spend carried by the most expensive 1% of queries
WITH q AS (
  SELECT query_id, bytes_scanned AS cost,
         NTILE(100) OVER (ORDER BY bytes_scanned DESC) AS pct
  FROM query_history
  WHERE start_time >= current_date - INTERVAL 30 DAY
)
SELECT SUM(CASE WHEN pct = 1 THEN cost END) / SUM(cost) AS top_1pct_share FROM q;"""

p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
p.add_argument("--queries", type=int, default=100_000, help="Queries in each simulated history.")
p.add_argument("--sigmas", type=lambda s: [float(v) for v in s.split(",")], default=[1.0, 1.5, 2.0, 2.5, 3.0],
               help="Log-normal sigma of per-query cost (tail weight), comma-separated.")
p.add_argument("--seed", type=int, default=0, help="Random seed.")
p.add_argument("--json", help="Also write the results to this JSON file.")
args = p.parse_args()
rng = np.random.default_rng(args.seed)
print(SQL, "\n")

rows = []
for s in args.sigmas:
    cost = np.sort(rng.lognormal(0, s, args.queries))[::-1]
    top = cost[: args.queries // 100].sum() / cost.sum()
    p99 = np.percentile(cost, 99)
    rows.append({"sigma": s, "top1_share_pct": top * 100, "p99_over_median": float(p99 / np.median(cost)),
                 "blocked_by_p99_cap": int((cost > p99).sum())})
    print(f"sigma {s:.1f}: top 1% of queries carry {top:6.1%} of spend; p99 is {rows[-1]['p99_over_median']:6.0f}x the median; "
          f"a p99 cap blocks {rows[-1]['blocked_by_p99_cap']:,} queries")

if args.json:
    with open(args.json, "w") as f:
        json.dump({"tables": {"sigmas": rows}}, f)

3Run it in the folder you saved it to, with the defaults first. Then change a knob from the table below and run it again.
Terminal
```
uv run measure-the-top-1-share-of.py
uv run measure-the-top-1-share-of.py --help     # every knob
```

Knobs
Knob · default	What it changes
--queries100,000	Queries in each simulated history.
--sigmas1.0, 1.5, 2.0, 2.5, 3.0	Log-normal sigma of per-query cost (tail weight), comma-separated.
--seed0	Random seed.

Our run

Share of spend in the top 1% of queries (simulated histories)Apple M2 · 8 cores · 8 GB

Data table

Share of spend in the top 1% of queries (simulated histories)
Tail weight (log-normal σ)	Share of total spend
1	9.38%
1.5	20.2%
2	36.5%
2.5	55.6%
3	75.2%

macOS 26.6.2 · Python 3.13.3 · numpy 2.5.4 · run 2026-10-20

Simulated histories of 100,000 queries

Simulated histories of 100,000 queries
σ	Top 1% share	p99 ÷ median	Blocked by a p99 cap
1	9.38%	10.3	1,000
1.5	20.2%	32.7	1,000
2	36.5%	107	1,000
2.5	55.6%	325	1,000
3	75.2%	1,130	1,000

Output · uv run measure-the-top-1-share-of.py

-- the share of spend carried by the most expensive 1% of queries
WITH q AS (
  SELECT query_id, bytes_scanned AS cost,
         NTILE(100) OVER (ORDER BY bytes_scanned DESC) AS pct
  FROM query_history
  WHERE start_time >= current_date - INTERVAL 30 DAY
)
SELECT SUM(CASE WHEN pct = 1 THEN cost END) / SUM(cost) AS top_1pct_share FROM q; 

sigma 1.0: top 1% of queries carry   9.4% of spend; p99 is     10x the median; a p99 cap blocks 1,000 queries
sigma 1.5: top 1% of queries carry  20.2% of spend; p99 is     33x the median; a p99 cap blocks 1,000 queries
sigma 2.0: top 1% of queries carry  36.5% of spend; p99 is    107x the median; a p99 cap blocks 1,000 queries
sigma 2.5: top 1% of queries carry  55.6% of spend; p99 is    325x the median; a p99 cap blocks 1,000 queries
sigma 3.0: top 1% of queries carry  75.2% of spend; p99 is   1131x the median; a p99 cap blocks 1,000 queries

What we found

The top-1% share depends entirely on the tail. At σ = 1, where the 99th-percentile query costs 10.3 times the median, the top 1% carried 9.38% of spend. The majority share the chapter expects appears from σ ≈ 2.5 (55.6%), where the p99 query costs 325 times the median, a spread typical of a warehouse where dashboards scan kilobytes and backfills scan terabytes. A p99 cap blocked 1,000 queries in every history: it always blocks 1%, whatever the tail.

Read it

Then decide what a cap at the 99th percentile would have blocked, and how many of those blocked queries were legitimate.

The ratio p99 ÷ median from your own history tells you which line you are on. If the most expensive 1% carry most of the bill, review those queries one by one: a missing partition filter, a SELECT *, an accidental cross join. A cap is a governor of last resort; a cost estimate before the query runs (the next lab) catches the expensive ones without blocking legitimate ones.