BigQuery Architecture and Cost

Consider a hypothetical analytics team with a 3 TB events table. An analyst runs SELECT * FROM events LIMIT 10 and the bill shows that the whole table was read. A dashboard that aggregates one week of one column finishes in two seconds but occupies thousands of slots while it runs. A nightly transformation scans little data and still takes an hour. Three queries, three different explanations, and the knobs that fix them are different too.

BigQuery separates what a query reads from the compute that processes it, and each side has its own meter. Under on-demand pricing the meter is bytes processed. Under capacity pricing it is slot time. To predict a query’s cost, or to lower it, you need to know which meter applies and which properties of the query and the table move it. This article builds that understanding from the architecture up, then checks each cost rule against small models written for it, since a real BigQuery project cannot be run from a text editor.

How a query runs

Google describes BigQuery’s storage and compute as separate layers connected by a very fast network, so that each can be scaled without disturbing the other (Google, BigQuery overview). Table data lives in a columnar format on Google’s distributed file system. The execution engine, Dremel in the research literature (Melnik et al., 2010), turns a SQL statement into a tree of stages. In Google’s description of the architecture, leaf workers called slots read data from storage and compute on it, intermediate workers aggregate the partial results, and a shuffle tier moves data between them over the datacenter network (Google, BigQuery under the hood). Borg, Google’s cluster manager, allocates the machines. The practical reading is the one the query plan documentation gives: slots run the individual work units of a stage in parallel, and stages hand data to each other through shuffle.

Three consequences follow, and the rest of the article is mostly about them.

• Bytes read depend on columns. Because each column is stored separately, a query touches only the columns it names. This is column pruning and it is the first reason SELECT * is expensive. The file-level mechanics are the same as in the columnar file formats that lakehouse tables use.
• Rows can be skipped in groups. Rows are stored in blocks. If a block can be shown not to match a filter, it is not read. Partitioning and clustering exist to make that possible, and they change how many bytes a query reads without changing its result.
• Compute is allocated per query, in slots. No cluster is started or sized by the user. The service assigns slots to a query as its stages need them, and the question of how many slots a project may use is a purchasing decision, not a deployment decision. A virtual warehouse in a system like Snowflake is something you size and start. A BigQuery slot is a unit the scheduler hands out.

A slot is deliberately abstract. The documentation defines it as a representation of compute, memory, and I/O resources together, so it cannot be converted into CPU cores. Its useful property is that the work of a query can be measured in slot-milliseconds, the sum over all slots of the time each spent working on it. That number, reported as totalSlotMs in the query plan, is the compute analog of bytes processed.

Two meters

The two pricing models bill different quantities. Under on-demand pricing you pay for the bytes a query processes, and the project can use a default capacity of up to 2,000 concurrent slots (Google, Introduction to workload management). Under capacity pricing you buy slots: reservations hold slot capacity, with a 50-slot minimum per reservation, billed in slot-hours. A reservation can have a baseline of slots that are always allocated and always paid for, plus autoscaling capacity. Autoscaled slots are added only after the baseline, and idle slots from other reservations if sharing is enabled, are used up. They scale in multiples of 50 and are billed per second with a one-minute minimum by default; capacity added by the autoscaler is kept for at least a 60-second scale-down window (Google, Introduction to slots autoscaling). The two models can coexist, and a project can move between them.

What matters for cost engineering is what each meter responds to. A query that reads little data and computes a lot is cheap under one meter and expensive under the other.

Property of a query	On-demand meter (bytes)	Capacity meter (slot time)
Columns referenced	Directly proportional to the stored size of those columns	Fewer bytes read usually means less scan work, so slot time often falls too
Rows returned or LIMIT	No effect on a non-clustered table; may stop early on a clustered one	Less output work, but scan work is unchanged without early stopping
Partition or block pruning	Fewer blocks read, fewer bytes billed (clustered estimates are upper bounds)	Less data to scan, less slot time
Joins, aggregations, window functions, regular expressions	No effect beyond the bytes they read	Raise slot time, sometimes by orders of magnitude
Data skew in a join or group key	No effect	Raises elapsed time and can raise slot time
Result cache hit	No charge	No query work

Storage has a third meter that this article leaves aside except for one choice: BigQuery can bill storage by logical size, the uncompressed size, or by physical size, the compressed size on disk, while storing the data in the same compressed format either way (Google, Storage overview). Which is cheaper depends on how well your data compresses relative to the price difference.

Worked example: reading the bytes meter

The rules above are easy to state and easy to misremember, so the first model makes them executable. It is a toy of BigQuery-style billing, not BigQuery: columns are stored separately, rows are grouped into fixed-size blocks, partitions are skipped whole, and clustered blocks carry per-column minimum and maximum values that let a scan skip them. Real block sizes are adaptive and are not exposed to users, and real pruning has more machinery, so the numbers below show direction and relative magnitude only.

The data is synthetic: an orders table of 1.44 million rows, 4,000 per day for 360 days, with a skewed distribution over 30 countries. Column widths are assumptions chosen so that one wide text column dominates the row, which is the situation that makes column selection matter. Table.scan returns two numbers for a query: the estimate, which is what a pre-execution dry run could know (partition pruning only), and the actual bytes read after block pruning.

"""Toy model of BigQuery-style billing: columns are stored separately, partitions are
skipped whole, clustered blocks are skipped by min/max. All sizes are assumptions."""
import random, datetime as dt

random.seed(5)
DAYS, ROWS_PER_DAY, BLOCK_ROWS = 360, 4000, 20_000
COUNTRIES = [f"C{i:02d}" for i in range(30)]
WEIGHTS = [1 / (i + 1) for i in range(30)]          # skewed: a few big countries
STATUSES = ["cancelled", "new", "paid", "shipped"]
WIDTH = {"order_date": 8, "country": 4, "status": 9, "customer_id": 8, "amount": 8, "notes": 120}
COLS = list(WIDTH)
start = dt.date(2024, 1, 1)

rows = []                                            # arrival order = date order
for d in range(DAYS):
    day = start + dt.timedelta(days=d)
    for _ in range(ROWS_PER_DAY):
        rows.append((day, random.choices(COUNTRIES, WEIGHTS)[0], random.choice(STATUSES),
                     random.randint(1, 200_000), round(random.random() * 500, 2), "x"))
MB = 1_000_000

class Table:
    def __init__(self, rows, partition_by_month=False, cluster=()):
        self.cluster = cluster
        self.parts = {}                              # partition key -> list of blocks
        groups = {}
        for r in rows:
            key = (r[0].year, r[0].month) if partition_by_month else "all"
            groups.setdefault(key, []).append(r)
        for key, g in groups.items():
            if cluster:
                idx = [COLS.index(c) for c in cluster]
                g = sorted(g, key=lambda r: tuple(r[i] for i in idx))
            blocks = []
            for i in range(0, len(g), BLOCK_ROWS):
                b = g[i:i + BLOCK_ROWS]
                stats = {c: (min(r[COLS.index(c)] for r in b), max(r[COLS.index(c)] for r in b))
                         for c in cluster}
                blocks.append((len(b), stats))
            self.parts[key] = blocks

    def _bytes(self, nrows, cols):
        return nrows * sum(WIDTH for c in cols)

    def scan(self, cols, filters=None, limit=None):
        """filters: {col: (lo, hi)} inclusive. Returns (estimate_MB, actual_MB).
        estimate = only partition pruning; actual = partition + block pruning."""
        filters = filters or {}
        est = act = 0
        for key, blocks in self.parts.items():
            if "order_date" in filters and key != "all":      # partition pruning on the date column
                y, m = key
                lo, hi = filters["order_date"]
                pstart = dt.date(y, m, 1)
                pend = (dt.date(y + (m == 12), m % 12 + 1, 1) - dt.timedelta(days=1))
                if hi < pstart or lo > pend:
                    continue
            for n, stats in blocks:
                est += self._bytes(n, cols)
                if all(not (c in stats and (stats[1] < f[0] or stats[0] > f[1]))
                       for c, f in filters.items()):
                    act += self._bytes(n, cols)
        if limit and self.cluster:                   # scan stops once enough blocks are read
            act = min(act, self._bytes(BLOCK_ROWS, cols))
        return est / MB, act / MB

The estimate/actual split mirrors a documented property. Google states that for a clustered table you do not get an accurate cost estimate before execution because the number of storage blocks to scan is not known, and that the estimate is an upper bound (Google, Introduction to clustered tables; Estimate and control costs). The model builds the same asymmetry into scan.

The experiments run six queries against this model. Each needs only the table class above.

n = len(rows)
print(f"rows={n:,}  blocks of {BLOCK_ROWS:,} rows  row width={sum(WIDTH.values())} B")
plain = Table(rows)
print("\n-- E1 which columns are read (no filter, unpartitioned, unclustered)")
for cols in (COLS, ["order_date", "country", "amount"], ["amount"]):
    print(f"{len(cols)} cols {','.join(cols)[:40]:40s} est={plain.scan(cols)[0]:8.1f} MB")

print("\n-- E2 LIMIT 10 on SELECT *")
clustered = Table(rows, cluster=("order_date", "country", "status"))
print(f"unclustered : actual={plain.scan(COLS, limit=10)[1]:8.1f} MB")
print(f"clustered   : actual={clustered.scan(COLS, limit=10)[1]:8.1f} MB")

print("\n-- E3 partition by month, one-week date filter, SELECT *")
pm = Table(rows, partition_by_month=True)
wk = {"order_date": (dt.date(2024, 5, 6), dt.date(2024, 5, 12))}
print(f"unpartitioned: est={plain.scan(COLS, wk)[0]:8.1f} MB actual={plain.scan(COLS, wk)[1]:8.1f} MB")
print(f"partitioned  : est={pm.scan(COLS, wk)[0]:8.1f} MB actual={pm.scan(COLS, wk)[1]:8.1f} MB")

print("\n-- E4 clustering column order, no partitioning, SELECT amount")
q_dc = {"order_date": (dt.date(2024, 5, 6), dt.date(2024, 5, 12)), "country": ("C00", "C00")}
q_cs = {"country": ("C03", "C03"), "status": ("paid", "paid")}
q_c = {"country": ("C03", "C03")}
layouts = {"unclustered": plain,
           "(order_date,country,status)": clustered,
           "(country,status,order_date)": Table(rows, cluster=("country", "status", "order_date"))}
print(f"{'layout':30s} {'date+country':>18s} {'country+status':>18s} {'country only':>18s}")
for name, t in layouts.items():
    out = []
    for q in (q_dc, q_cs, q_c):
        e, a = t.scan(["amount"], q); out.append(f"{e:5.1f}/{a:5.1f} MB")
    print(f"{name:30s} " + " ".join(f"{o:>18s}" for o in out))
print("(each cell: dry-run estimate / actual bytes read)")

print("\n-- E5 estimate versus enforcement, clustered table, SELECT * for one week")
e, a = clustered.scan(COLS, wk)
limit_mb = 100
print(f"estimate={e:.1f} MB actual={a:.1f} MB maximum_bytes_billed={limit_mb} MB")
print("rejected before running" if e > limit_mb else "runs", "| would have read", f"{a:.1f} MB")

print("\n-- E6 partition by month and cluster, same query")
both = Table(rows, partition_by_month=True, cluster=("order_date", "country", "status"))
e, a = both.scan(COLS, wk)
print(f"estimate={e:.1f} MB actual={a:.1f} MB maximum_bytes_billed={limit_mb} MB ->",
      "rejected" if e > limit_mb else "runs")

The first experiment reads the same table with three column lists.

-- E1 which columns are read (no filter, unpartitioned, unclustered)
6 cols order_date,country,status,customer_id,am est=   226.1 MB
3 cols order_date,country,amount                est=    28.8 MB
1 cols amount                                   est=    11.5 MB

Reading all six columns costs 226 MB; reading three costs 28.8 MB; reading amount alone costs 11.5 MB. The wide notes column accounts for about 76 percent of the row, so removing it is worth more than any filter. The query returns the same number of rows in all three cases. What changed is the number of columns the storage layer had to open.

The second experiment adds LIMIT 10 to SELECT *.

-- E2 LIMIT 10 on SELECT *
unclustered : actual=   226.1 MB
clustered   : actual=     3.1 MB

On the unclustered table the limit does nothing to the bytes read: 226.1 MB, the whole table. The documentation says exactly this for non-clustered tables. LIMIT trims what is returned, not what is scanned, so it is not a cost control. On the clustered table the scan can stop after enough blocks have produced ten rows, here one 20,000-row block, 3.1 MB. This early stop only happens when the engine can read blocks in a useful order, which is why the documentation attaches the behavior to clustered tables.

The third experiment partitions the table by month and filters one week.

-- E3 partition by month, one-week date filter, SELECT *
unpartitioned: est=   226.1 MB actual=   226.1 MB
partitioned  : est=    19.5 MB actual=    19.5 MB

A filter on the partitioning column lets the engine skip every partition that cannot match (Google, Introduction to partitioned tables). Because the table is partitioned by month, the one-week filter still reads all of May, 19.5 MB, about 9 percent of the table. The estimate equals the actual value here because partition pruning is decided from metadata before execution, which is why partitioned tables can give an exact dry-run estimate. The granularity of the partition sets the floor: weekly questions on monthly partitions pay for a month. The related partition pruning entry covers when a filter can and cannot be used for pruning; the BigQuery documentation notes that subquery filters and non-constant values do not prune.

Clustering and the order of columns

The fourth experiment is the one that most often surprises engineers. It clusters the same table three ways and runs three filters, reading only amount so that the numbers isolate the pruning effect. The layouts are unclustered, clustered by (order_date, country, status), and clustered by (country, status, order_date). The queries filter on date and country, on country and status, and on country alone. This mirrors the example in Google’s documentation, where a table clustered by Order_Date, Country, Status is optimized for a filter on the first two but not for a filter on the last two.

-- E4 clustering column order, no partitioning, SELECT amount
layout                               date+country     country+status       country only
unclustered                         11.5/ 11.5 MB      11.5/ 11.5 MB      11.5/ 11.5 MB
(order_date,country,status)         11.5/  0.3 MB      11.5/ 11.5 MB      11.5/ 11.5 MB
(country,status,order_date)         11.5/  1.3 MB      11.5/  0.6 MB      11.5/  0.8 MB
(each cell: dry-run estimate / actual bytes read)

Read the table by columns of layouts. The unclustered row shows the baseline: every query reads the full 11.5 MB. The date-first layout makes the date-and-country query cheap, 0.3 MB, and does nothing for country and status: 11.5 MB again. The documentation’s rule appears in the model, and so does the reason. Clustering sorts rows by the first column and sorts by the second only within equal values of the first. Each block here holds 20,000 rows, five days, and within those five days every country appears, so every block’s country range spans nearly the whole domain and none can be skipped. The country-first layout reverses the picture: country alone drops to 0.8 MB, country and status to 0.6 MB, while date and country is a little worse than before, 1.3 MB.

Two decisions follow. Order the columns by how often and how selectively queries filter on them, with the column used in nearly every query first. And accept that one physical order serves some filters better than others. The documentation caps clustering at four columns and notes that automatic reclustering maintains the order in the background. The model also shows a limit of the documentation’s phrase “not optimized”: a trailing-column filter can still help when blocks are smaller than the groups of the leading column. In the model that would require blocks of a single date, and it is not something a table owner controls, so planning around the first column is the safe rule. The clustered table entry summarizes the same constraints; the table maintenance article covers the equivalent file-layout work for open table formats, where you perform compaction and sorting yourself instead of relying on automatic reclustering.

Partitioning and clustering compose. Partition by a coarse key that queries nearly always constrain, usually a date, and cluster by the columns that narrow within it. The Google documentation guidance is that partitioning with very little data per partition, roughly under 10 GB, favors clustering alone, and a table is limited to 10,000 partitions, which makes hourly partitioning over several years a limit to compute in advance.

Estimates, dry runs, and enforcement

A dry run asks BigQuery to validate a query and report the bytes it would process without executing it or charging for it. It is the preview of the bytes meter. The estimate is an upper bound and can be higher than the bytes actually billed (Google, Estimate and control costs). A dry run informs a person; it does not stop anyone from running the query. Enforcement is the separate maximum-bytes-billed setting on the job: BigQuery estimates the bytes before execution, and if the estimate exceeds the limit the query fails without charge.

The two interact through exactly the asymmetry the model has. Experiment five sets a 100 MB limit and runs a one-week SELECT * against the clustered, unpartitioned table.

-- E5 estimate versus enforcement, clustered table, SELECT * for one week
estimate=226.1 MB actual=6.3 MB maximum_bytes_billed=100 MB
rejected before running | would have read 6.3 MB

The estimate is the whole table, 226.1 MB, because block pruning cannot be predicted. The job is rejected even though it would have read 6.3 MB. This is the cost of the upper bound: a conservative guard rejects some queries that would have been cheap. The sixth experiment adds monthly partitioning to the same clustered layout.

-- E6 partition by month and cluster, same query
estimate=19.5 MB actual=6.3 MB maximum_bytes_billed=100 MB -> runs

Now the estimate is 19.5 MB, the whole of May, which passes the guard, and the actual read is the same 6.3 MB. Partitioning contributes the part of the estimate that can be known in advance, and clustering contributes extra savings that only show up in the bill. That combination is why the pairing is standard practice for large fact tables, and why a hard byte limit works better on a partitioned table.

The following sketch shows how a dry run and a limit appear in the Python client. It is not run against a live service, and the table name is a placeholder.

from google.cloud import bigquery

client = bigquery.Client()
sql = "SELECT amount FROM `my_project.my_dataset.orders` WHERE order_date BETWEEN '2024-05-06' AND '2024-05-12'"

# Estimate only: validates the query and reports bytes, runs nothing, bills nothing.
dry = client.query(sql, job_config=bigquery.QueryJobConfig(dry_run=True, use_query_cache=False))
print(dry.total_bytes_processed)

# Enforcement: the job fails before running if the estimate exceeds the cap.
capped = bigquery.QueryJobConfig(maximum_bytes_billed=10 * 1024**3)
client.query(sql, job_config=capped).result()

Other controls act earlier or on a different level. A table option, require_partition_filter, makes queries without a filter that can eliminate partitions fail with an error (Google, Manage partitioned tables). Previewing a table in the console does not run a query and incurs no charge. Query results are cached for about 24 hours and a cache hit is not billed, but the text of the repeated query must match exactly and caching does not apply when a destination table is set, when non-deterministic functions such as CURRENT_TIMESTAMP() appear, for wildcard table queries, or when a referenced table has changed (Google, Using cached query results). A dashboard whose query embeds the current time in the SQL text defeats the cache on every refresh, a detail that costs money at scale.

Worked example: reading the slot meter

Bytes processed says how much data a query read. It says almost nothing about how much work the query did with that data. A join of two small tables can multiply rows and run for minutes. The query plan reports the work in slot-milliseconds, along with per-stage figures: records read, bytes written to shuffle, bytes of shuffle spilled to disk, and for the workers of a stage the average and maximum of the compute and wait times (Google, Query plan and timeline). The query execution plan entry explains what a plan is; this section uses one stage to show what the numbers mean.

The model below is a single stage with 2,000 similar work units of about 200 ms each, so about 400,000 slot-milliseconds, dispatched to a given number of slots: whenever a slot is free it takes the next unit, longest first. A second stage has the same total work, but one unit is far larger than the rest, the way a hot join key sends a large share of rows to one worker. The program prints the elapsed time of the stage for several slot counts.

"""One query stage as work units dispatched to S slots (greedy: next unit goes to the first free slot)."""
import heapq, random

def run_stage(units_ms, slots):
    free = [0.0] * slots                     # time at which each slot becomes free
    heapq.heapify(free)
    for u in sorted(units_ms, reverse=True): # longest first, a common heuristic
        t = heapq.heappop(free)
        heapq.heappush(free, t + u)
    return max(free)                          # elapsed ms of the stage

random.seed(3)
even = [random.uniform(150, 250) for _ in range(2000)]        # 2000 similar units, ~200 ms each
total_even = sum(even)
hot = even[:-1] + [60_000.0]                                  # one unit holds a hot key
# keep total work equal so only the shape differs
scale = total_even / sum(hot)
hot = [u * scale for u in hot[:-1]] + [60_000.0 * scale]
print(f"total slot-ms even={sum(even):,.0f}  skewed={sum(hot):,.0f}")
print(f"{'slots':>6s} {'even elapsed s':>15s} {'skewed elapsed s':>17s}")
for s in (10, 50, 200, 1000, 2000):
    print(f"{s:6d} {run_stage(even, s)/1000:15.2f} {run_stage(hot, s)/1000:17.2f}")
avg = sum(hot) / len(hot); mx = max(hot)
print(f"skewed stage: compute avg per unit={avg:.0f} ms, max={mx:,.0f} ms, ratio={mx/avg:.0f}x")

total slot-ms even=399,619  skewed=399,619
 slots  even elapsed s  skewed elapsed s
    10           39.96             52.19
    50            7.99             52.19
   200            2.00             52.19
  1000            0.40             52.19
  2000            0.25             52.19
skewed stage: compute avg per unit=200 ms, max=52,187 ms, ratio=261x

Both stages cost the same total slot-milliseconds, 399,619, which is what a capacity meter would charge. They differ in elapsed time. The even stage speeds up almost in proportion to slots: about 40 seconds on 10 slots, 8 seconds on 50, a quarter of a second on 2,000. Adding slots buys time and costs nothing extra, because the work is conserved. The skewed stage stops improving at 52 seconds, the length of its largest unit, whatever capacity is added. In the plan this shows up as the compute maximum far above the compute average, here 261 times: the documentation describes that signature as a stage spending a disproportionate amount of time on a few slices of data and suggests filtering earlier or splitting the query. More slots do not fix it. A change to the data distribution or the join does. The Spark skew article describes the same failure with different names.

Combining the two meters gives a diagnostic reading that the slot and clustered-table entries rely on:

Observed in the job	Likely meaning	First direction to try
High bytes processed, modest slot time per byte	A layout or column-selection problem: the query reads far more than it needs	Select fewer columns, add a partition or clustering filter, check the dry run
Low bytes processed, high slot time	Expensive computation: join fan-out, aggregation, window functions, regular expressions	Pre-aggregate before joining, avoid self and cross joins, materialize repeated work
Stage compute maximum far above average	Skew on a key or a partition	Filter earlier, change the key, split the query
High shuffle bytes spilled	A stage transmits more data than memory holds	Reduce rows before the shuffle; consider clustering on the join or group key
Long wait time per worker but modest compute	Waiting for slots, not slow work	Look at reservation capacity and competing jobs

Google’s performance guidance for compute names the common causes: self-joins that square the output, cross joins that produce more rows than they read, too many table shards, and repeated transformations that could be materialized, along with ordering joins so that the large table comes first and aggregating before joining (Google, Optimize query computation). For queries you cannot change, a materialized view can precompute a repeated aggregation, and BigQuery reads only the changes to the base table where it can.

These figures are also queryable across a whole project. The following SQL is illustrative and was not run, because it needs a live project. It ranks the previous week’s queries by bytes billed and slot time using the jobs view of the information schema; check the current column list in the documentation before adopting it.

SELECT
  user_email,
  COUNT(*) AS queries,
  SUM(total_bytes_billed) / POW(1024, 4) AS tib_billed,
  SUM(total_slot_ms) / (1000 * 3600) AS slot_hours
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
GROUP BY user_email
ORDER BY tib_billed DESC
LIMIT 20;

The ratio of the two columns across jobs is the quantity the next section needs.

Choosing between on-demand and capacity

With two meters, the choice between pricing models depends on the workload’s ratio of bytes to slot time. Let \(B\) be the TiB a workload scans in a period and \(H\) the slot-hours it uses. With a price \(P\) per TiB and a price \(s\) per slot-hour, on-demand costs \(P \cdot B\) and capacity costs about \(s \cdot H’\), where \(H’\) is the slot-hours you pay for, which is at least \(H\) because capacity is bought in steps and baseline is paid when idle. The two are equal when

\[ r^{*} = \frac{B}{H} = \frac{s \cdot H’}{P \cdot H} \]

where \(r^{*}\) is the break-even number of TiB scanned per slot-hour. Workloads that scan more than \(r^{*}\) TiB per slot-hour, such as large scans with light computation, cost less under capacity. Workloads that scan less, such as small tables with heavy joins, cost less on-demand. The formula is a definition, and real prices vary by edition and region, so the numbers that follow use abstract units.

The model simulates one day of demand in slots per minute for a hypothetical workload: a small constant load, dashboards bursting between 09:00 and 17:00, and one hour of nightly transformation. Prices are hypothetical: 6 units per TiB and 0.05 units per slot-hour. Capacity is modeled as a baseline plus autoscaling in multiples of 50 slots, billed each minute for the capacity needed. That simplifies the documented one-minute minimum and 60-second scale-down window, which would add a little cost, and it assumes baseline and autoscaled slots cost the same, so a baseline buys guaranteed availability here and no discount.

"""Hypothetical cost comparison in abstract cost units (not real prices).
Demand: slots needed each minute of a day. On-demand bills bytes; capacity bills slot-time."""
import random, math
random.seed(1)
minutes = range(1440)
def demand(m):                                   # slots the workload would use at minute m
    h = m / 60
    base = 20
    office = 300 if 9 <= h < 17 else 0           # dashboards: bursty in office hours
    etl = 600 if 2 <= h < 3 else 0               # nightly transform hour
    return base + office * random.uniform(0.3, 1.0) + etl

D = [demand(m) for m in minutes]
slot_hours = sum(D) / 60
P_TIB, P_SLOT_HOUR = 6.0, 0.05                   # hypothetical units: per TiB scanned, per slot-hour

def capacity_cost(baseline):
    cost = 0.0
    for d in D:
        extra = max(0.0, d - baseline)
        auto = math.ceil(extra / 50) * 50        # autoscale in multiples of 50 above the baseline
        cost += (baseline + auto) / 60 * P_SLOT_HOUR   # one minute of capacity
    return cost

print(f"slot-hours used per day: {slot_hours:,.0f}  peak demand: {max(D):,.0f} slots")
for b in (0, 100, 400):
    print(f"baseline {b:4d} + autoscale: {capacity_cost(b):7.2f} units/day")
print()
print("TiB scanned per slot-hour -> on-demand cost vs best capacity cost (units/day)")
best = min(capacity_cost(b) for b in range(0, 701, 50))
for tib_per_slot_hour in (0.0005, 0.002, 0.01, 0.05):
    od = slot_hours * tib_per_slot_hour * P_TIB
    print(f"{tib_per_slot_hour:8.4f} TiB/slot-h  on-demand={od:8.2f}  capacity={best:6.2f}  cheaper={'on-demand' if od < best else 'capacity'}")
print(f"\nbreak-even: {best / (slot_hours * P_TIB):.4f} TiB scanned per slot-hour")

slot-hours used per day: 2,684  peak demand: 620 slots
baseline    0 + autoscale:  168.08 units/day
baseline  100 + autoscale:  205.58 units/day
baseline  400 + autoscale:  492.50 units/day

TiB scanned per slot-hour -> on-demand cost vs best capacity cost (units/day)
  0.0005 TiB/slot-h  on-demand=    8.05  capacity=168.08  cheaper=on-demand
  0.0020 TiB/slot-h  on-demand=   32.21  capacity=168.08  cheaper=on-demand
  0.0100 TiB/slot-h  on-demand=  161.03  capacity=168.08  cheaper=on-demand
  0.0500 TiB/slot-h  on-demand=  805.14  capacity=168.08  cheaper=capacity

break-even: 0.0104 TiB scanned per slot-hour

The workload uses 2,684 slot-hours a day with a peak of 620 slots. Autoscaling alone costs 168.08 units. A 100-slot baseline costs 205.58 and a 400-slot baseline 492.50, because baseline slots are paid for during the idle hours between bursts. That is the central trade-off of baselines: they remove autoscaling delay and give a predictable capacity floor, and they are billed whether used or not. Reservations can share idle slots with each other when configured, which recovers some of that waste, but the warehouse workload isolation article explains why a workload that must meet a deadline should not depend on borrowed capacity.

The second block of output is the choice itself. If the workload scans 0.0005, 0.002, or 0.01 TiB per slot-hour, on-demand is cheaper; at 0.05 TiB per slot-hour capacity is cheaper. The break-even is 0.0104. The answer is not a property of BigQuery but of the workload’s bytes per slot-hour, which is why the jobs-view query above reports both columns. Measure your own ratio before comparing published prices. The break-even also leaves out predictability: on-demand spending moves with every query, while a reservation has a ceiling. A finance team that needs a fixed monthly number may accept a higher expected cost for it.

Failure modes that show up on the bill

Most surprising bills trace to a short list of mechanisms.

• Wide tables read through SELECT *. A dashboard that selects every column from a wide table pays for the widest columns on every refresh. Name columns, or create a narrow table or view for the dashboard’s needs.
• Partition filters that do not prune. A filter must be on the partitioning column in a form the engine can evaluate before execution. A filter against a subquery result or a transformed column may not prune. Use require_partition_filter so omissions fail loudly, and check the dry-run estimate against the expected partition size.
• Too many small partitions or shards. Hourly partitions across years approach the 10,000 partition limit, and thousands of date-named tables slow planning. Google’s performance guidance warns against over-sharding.
• Clustering columns in the wrong order. The leading column determines everything, as the fourth experiment showed. Choose it from the filters in the job history, not from the order the columns happen to appear in the schema.
• Hard limits that reject cheap queries. A maximum_bytes_billed value set against a clustered, unpartitioned table rejects on the upper-bound estimate.
• Cache defeats. Queries that embed non-deterministic functions, or tools that add changing comments to SQL text, bypass the cache.
• Row multiplication in joins. Joins on non-unique keys and cross joins inflate slot time without touching bytes processed. The slot meter is where it appears.
• Unwatched concurrency. On a reservation, a heavy job can make other jobs wait. The symptom looks like a slow query but shows as wait time rather than compute time in the plan.

The toy models leave out features of the real service that matter in production: actual block sizes and statistics are not observable, background reclustering changes layout over time, a query’s slots vary during execution, and the price lists differ by region and edition. Treat the models as a way to learn which quantity responds to which change, then confirm every decision with a dry run on your own table and the job history of your own project. In a data warehouse shared by many teams, the largest savings usually come from fixing the few queries at the top of the jobs-view ranking.

References

• Google Cloud, BigQuery overview
• Google Cloud blog, BigQuery under the hood
• Melnik et al., Dremel: Interactive Analysis of Web-Scale Datasets (VLDB 2010)
• Google Cloud, Introduction to workload management
• Google Cloud, Introduction to slots autoscaling
• Google Cloud, Query plan and timeline
• Google Cloud, Estimate and control costs
• Google Cloud, Introduction to clustered tables
• Google Cloud, Introduction to partitioned tables
• Google Cloud, Manage partitioned tables
• Google Cloud, Using cached query results
• Google Cloud, Optimize query computation