BigQuery Explained: Google's Serverless Data Warehouse

BigQuery is Google Cloud’s data warehouse. You load data, write SQL, and get results, without choosing machine sizes, installing anything or managing a cluster. Google describes it as fully managed and serverless (Google Cloud: BigQuery overview). That simplicity has one consequence every new user should understand before running queries on real data: with on-demand pricing, each query is billed by the amount of data it reads. This guide explains how BigQuery is built, how it charges, and the habits that keep a bill predictable.

How BigQuery is built

Two design choices explain most of BigQuery’s behaviour. First, storage and compute are separate layers that scale independently (Google Cloud: overview). Your tables sit in managed storage whether or not anything is querying them, and a query borrows compute for as long as it runs. Second, storage is columnar: each column of a table is stored separately (Google Cloud: storage overview).

Columnar storage is why BigQuery is fast at analytics: a query that sums one column of a billion-row table reads that column, not the whole rows. It is also why the columns you name in SELECT decide how much data a query reads.

How BigQuery charges

The bill has two main parts: storage (what you keep) and compute (running queries). For compute, you choose between two models (Google Cloud: BigQuery pricing):

Prices vary by region and change over time, so check the pricing page rather than any figure quoted in a tutorial. Storage has its own saving: tables and partitions that are not modified for 90 consecutive days move to long-term storage pricing, and modifying them moves them back to active storage (Google Cloud: estimate and control costs).

What a query reads (and what it does not)

Under on-demand pricing, three facts surprise almost everyone at first:

1. SELECT * reads every column. Google’s guidance is to query only the columns you need, and use SELECT * EXCEPT (…) when you want nearly all of them (Google Cloud: optimize query computation).
2. LIMIT does not reduce the data read on a non-clustered table. SELECT * … LIMIT 10 scans the same bytes as SELECT * (Google Cloud: estimate and control costs).
3. Exploring data with queries costs money; previewing does not. Use the table’s Preview tab in the console, or bq head, to look at rows for free.

Before running a query on a large table, check what it will read. A dry run validates the query and reports the bytes it would process, without running it. The maximum bytes billed setting makes a query fail instead of running if it would read more than you allow:

# Report the bytes this query would process, without running it
bq query --use_legacy_sql=false --dry_run \
  'SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = "hamlet"'

# Refuse to run if the query would bill more than ~1 GB
bq query --use_legacy_sql=false --maximum_bytes_billed=1000000000 \
  'SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = "hamlet"'

The console shows the same estimate in the query editor before you press Run. Get into the habit of reading it.

Design tables so queries read less

Choosing columns only helps so much. Beyond that, the table itself has to let BigQuery skip data. Two features do that, and they work well together.

Partitioning

A partitioned table is divided into segments by a DATE, TIMESTAMP or DATETIME column (daily, hourly, monthly or yearly), by ingestion time, or by ranges of an INTEGER column (Google Cloud: partitioned tables). A query that filters on the partitioning column reads only the matching partitions. Partitioning also gives an exact cost estimate before the query runs.

CREATE TABLE analytics.orders
(
  order_id      STRING,
  customer_id   STRING,
  country       STRING,
  status        STRING,
  amount        NUMERIC,
  order_date    DATE
)
PARTITION BY order_date
CLUSTER BY country, status
OPTIONS (
  require_partition_filter = TRUE      -- queries without a filter on order_date are rejected
);

require_partition_filter is worth setting on any large table. It turns an accidental full scan into an error message (Google Cloud: create partitioned tables). Choose a granularity your data can sustain: tables have a maximum number of partitions, and Google advises a coarser granularity (monthly instead of daily, say) if a finer one would reach it.

Clustering

Clustering sorts a table’s data by up to four columns and stores it in blocks. A filter on those columns lets BigQuery skip blocks that cannot match, which Google calls block pruning (Google Cloud: clustered tables). Column order matters: put the column you filter on most first. Unlike partitioning, the saving is not known exactly before the query runs, so the dry-run estimate for a clustered table can be higher than what you are actually billed.

-- Reads one month of partitions, then only the blocks for India and "shipped"
SELECT order_id, amount
FROM analytics.orders
WHERE order_date BETWEEN '2026-08-01' AND '2026-08-31'
  AND country = 'IN'
  AND status = 'shipped';

Choosing partition granularity, cluster columns and partition expiry for real tables is covered in partitioning and clustering in BigQuery.

A first-week checklist

• Use table Preview, not SELECT *, to look at data.
• Name the columns you need in every query.
• Read the bytes estimate before running anything on a large table; set maximum bytes billed on scheduled and ad-hoc queries.
• Partition large fact tables by date and set require_partition_filter; cluster by the columns you filter on most.
• Keep raw files in Cloud Storage and load or query them deliberately; see building an ingestion layer from Cloud Storage to BigQuery.

Slots, editions, reservations and cost monitoring go further in BigQuery cost optimisation. If you are choosing a warehouse rather than learning one, see BigQuery vs Snowflake vs Redshift.

Where to go next

BigQuery, Cloud Storage, Dataflow and Pub/Sub are part of the Google Cloud module of our Data Engineering course.

Sources

• BigQuery overview — Google Cloud documentation
• Overview of BigQuery storage — Google Cloud documentation
• BigQuery pricing — Google Cloud
• Introduction to partitioned tables — Google Cloud documentation
• Create partitioned tables — Google Cloud documentation
• Introduction to clustered tables — Google Cloud documentation
• Estimate and control costs — Google Cloud documentation
• Optimize query computation — Google Cloud documentation