BigQuery

What is BigQuery?

BigQuery is Google Cloud’s serverless data warehouse — a system for running SQL analytics over enormous datasets (terabytes to petabytes) with no infrastructure to provision or manage. You load data (or query it in place), write SQL, and BigQuery automatically distributes the query across Google’s massive compute fabric and returns results, scaling transparently. It’s Google’s answer to analytical workloads — the “ask complex questions across all our data” tool — comparable to Amazon Redshift and Snowflake, but distinguished by its fully serverless model: there’s no cluster to size, start, or tune.

Why it’s fast, and what it’s for

BigQuery is built for analytics, not transactions, and its architecture reflects that. It’s columnar (stores data by column, so a query touching a few of many columns reads only those — huge I/O savings and strong compression) and runs queries with massively parallel processing across thousands of machines on demand, which is why it can scan enormous tables in seconds. It separates storage from compute (they scale independently, and you pay for them separately), and its serverless design means a query can marshal vast compute for a moment and release it — no idle cluster. This makes it excellent for data warehousing, business intelligence, ad-hoc analytical queries, and increasingly ML (BigQuery ML lets you train models in SQL) and as an analytics backend for large-scale data. But the same design that makes it great at analytics makes it terrible at transactional workloads: single-row lookups, frequent small updates, and high-concurrency point queries are exactly what a columnar MPP warehouse is bad at. Using BigQuery as an application database is a category error — for transactions you want Cloud SQL or Spanner; BigQuery is the warehouse you load into for analysis.

The cost model — the thing that surprises everyone

BigQuery’s pricing is its most important practical characteristic and its biggest foot-gun. The default on-demand model bills primarily by the amount of data scanned by each query, not by rows returned or time taken — so a poorly-written query that scans a huge table can cost real money, and SELECT * over a large table is the classic expensive mistake (it reads every column even if you only look at a few). This billing model rewards specific disciplines: select only the columns you need (columnar storage means unused columns aren’t scanned — the opposite of a row store), partition and cluster tables (so queries prune to relevant data instead of scanning everything — a query filtered on a partitioned date column scans only those partitions), avoid SELECT *, and preview/estimate query cost before running (BigQuery shows bytes-to-be-scanned). Teams that ignore this get startling bills from analysts running unbounded scans repeatedly. Google also offers capacity/flat-rate (editions) pricing — reserved compute slots for predictable heavy usage — which caps cost for high-volume shops but can waste money for spiky, light usage where on-demand is cheaper. The recurring mistakes: using BigQuery as a transactional database (wrong tool), SELECT * and unpartitioned tables driving up scan costs, and choosing the wrong pricing model for the usage pattern (on-demand for constant heavy load, or flat-rate for occasional queries).

What people get wrong

• Using it as an application database — columnar MPP is built for bulk analytics, not single-row transactions and frequent updates; use Cloud SQL/Spanner for those.
• SELECT * and unpartitioned tables: on-demand billing is by data scanned, so reading all columns and unpartitioned tables inflates cost — select needed columns and partition/cluster tables.
• Wrong pricing model — constant heavy usage may be cheaper on flat-rate/reserved slots, while spiky light usage suits on-demand; mismatching them overpays.

Primary source: BigQuery documentation