BigQuery for people who have never used a data warehouse

Most developers arrive at BigQuery with a mental model shaped by relational databases: a server, some indexes, a query planner, and a rough intuition that narrowing your query makes it faster. That intuition is not merely incomplete here. In several places it is actively wrong, and acting on it is how people end up with a surprising bill.

BigQuery is a columnar, distributed analytics engine with no servers to manage. It is genuinely excellent at what it does. It is also a different shape of tool, and the differences are worth learning before you point it at anything large.

Columns, not rows

A conventional database stores a row’s fields next to each other on disk. Reading one row is cheap; reading one column across millions of rows means touching every row.

BigQuery inverts this. Each column is stored separately. Reading one column across a billion rows touches only that column’s data and ignores everything else entirely.

This single fact explains most of BigQuery’s behaviour:

• SELECT * is the most expensive thing you can write. It reads every column. On a wide table this can be twenty times the cost of selecting the four columns you actually need.
• Adding more columns to the output increases cost. Adding more WHERE conditions usually does not reduce it, because the filter still has to read the column it filters on.
• Row counts matter less than you expect. A query over a billion rows and two columns can be cheaper than one over a million rows and every column.

If you take one habit away, make it this: never write SELECT * against a large table. Name your columns.

What you are actually billed for

There are two pricing models and they behave completely differently.

On-demand bills by bytes processed. Not bytes returned, not rows scanned: bytes read from the columns your query touches. A query returning a single number can process a terabyte if it aggregates over one.

Capacity-based pricing buys a quantity of compute available to your queries. Cost becomes predictable and disconnected from how much data each query reads.

On-demand suits exploratory and intermittent use. Capacity suits steady, heavy workloads and teams where one careless query should not be able to generate an unusual bill. Most organisations start on demand and move once usage becomes predictable.

The console shows an estimate of bytes processed before you run a query. Reading that estimate before hitting run is the single highest-value habit for anyone new to the tool.

Partitioning and clustering

These are the two mechanisms that genuinely reduce cost, and they must be set up on the table rather than applied at query time.

Partitioning splits a table by a column, usually a date. A query filtered to a single day’s partition reads only that partition. Without partitioning, the same filter reads the whole column and then discards most of it. The cost difference on a large table is enormous.

Clustering sorts data within a partition by up to four columns. A query filtering on a clustered column can skip blocks that cannot contain matches. The benefit is less dramatic than partitioning but requires no extra thought once configured.

A practical default for time-series data is to partition by ingestion date or event date, and cluster by the identifier you filter on most often, such as a customer or account ID.

Setting these up on a table that already holds a lot of data means recreating it. It is much easier to decide at creation time, which is a good argument for spending five minutes on the schema before loading anything substantial.

Streaming, batching and the cost of getting data in

Loading data in bulk from object storage is free or near-free. Streaming rows in individually is billed. For most analytics workloads, batching into files and loading periodically is dramatically cheaper than streaming, and the freshness difference rarely matters.

Reach for streaming when you genuinely need rows queryable within seconds. For a dashboard that refreshes hourly, batching is the correct answer and it costs almost nothing.

Where BigQuery is the wrong tool

It is worth being explicit, because BigQuery is often reached for by default on Google Cloud.

It is not a transactional database. Point lookups by primary key are slow and expensive relative to a conventional database, and there is no meaningful concept of a row-level index. It is not designed for high-frequency small updates. It is not the right backing store for an application’s user records.

Use it for analytics: aggregations over large volumes, reporting, ad-hoc exploration, joining datasets that would be unwieldy elsewhere. Keep transactional data in a transactional database and load it into BigQuery for analysis.

Habits that keep costs sane

A short list that covers most of the risk:

1. Name columns explicitly. Never SELECT * on anything large.
2. Read the byte estimate before running an unfamiliar query.
3. Partition every large table on the column you filter by most.
4. Preview data with the table preview, not with a SELECT and a LIMIT. A LIMIT does not reduce bytes processed; the preview reads nothing.
5. Set a maximum bytes billed on queries where you want a hard ceiling. The query fails rather than running away.
6. Use custom quotas at the project or user level so one person’s mistake cannot consume a month’s budget.
7. Materialise expensive intermediate results rather than recomputing them in every downstream query.

Point four surprises almost everyone. LIMIT restricts what comes back, not what is read, so it does not reduce on-demand cost at all.

Getting started sensibly

Create a project you can throw away. Load a modest sample rather than the full dataset, partition it, and run the queries you expect to run in production. Compare the byte estimates against your intuition and adjust the schema before scaling up.

That loop takes an afternoon and it is the difference between BigQuery being a very fast tool and being a very fast tool that costs more than you planned.

For the vendor’s own reference on the services involved here, see the Google Cloud documentation.

Questions people ask