Google BigQuery

Overview

BigQuery is Google Cloud's fully managed, serverless data warehouse. You load or stream data into tables, write SQL, and Google runs the query on as much hardware as it needs. You do not choose servers, size clusters or tune disks. Storage and compute are separate, so a table can grow to petabytes while you pay for queries only when you run them, or for a reserved amount of compute capacity.

BigQuery is built for analytics: scanning large tables, joining them and aggregating results for reports, dashboards, data science and machine learning. Data is stored in a columnar format, so a query reads only the columns it references. Features such as partitioning and clustering let it skip blocks of data as well. Beyond plain SQL, BigQuery can train and run machine learning models with SQL, query files in object storage and serve as the back end for many business intelligence tools.

An everyday analogy is the difference between owning a car and using a taxi service. With a traditional warehouse you buy the car, choose its size, maintain it and pay for it when it sits in the garage. BigQuery is a taxi that appears when you need a journey, with as many seats as the trip requires, and you pay for the distance or for a fixed monthly allowance.

What it is

Where it came from

BigQuery grew out of Dremel, an internal Google system for interactive queries over very large data sets, described in a research paper in 2010. Google announced BigQuery at its I/O conference in May 2010, offered it to early users in 2011 and made it broadly available soon after.

Over the following years it added standard SQL as the default dialect, streaming inserts, partitioned and clustered tables and BigQuery ML, announced in 2018. Later additions included cross-cloud querying, BigLake for open-format data in object storage, and in 2023 BigQuery editions, which reorganised capacity pricing into Standard, Enterprise and Enterprise Plus tiers. More recently Google has added generative AI functions that call models from SQL. Details and names of these features change often, so check the current documentation.

How it works

Storage

When data enters BigQuery, it is converted to a proprietary columnar format and stored on Google's distributed file system, replicated for durability. Each column is compressed separately. BigQuery keeps metadata such as the minimum and maximum values for blocks of data, which lets the planner skip blocks. Tables are versioned: time travel lets you query or restore a table as it was within a recent window, seven days by default.

Compute

Queries run on a large shared pool of workers. The unit of capacity is a slot, roughly a share of CPU, memory and network. A query plan is split into stages. Each stage runs many workers in parallel, and intermediate results are exchanged through a fast in-memory shuffle layer. Because compute is separate from storage, BigQuery can assign hundreds or thousands of slots to a large query for a few seconds and then release them.

Running a query

1. You submit SQL through the console, a client library, the bq command-line tool or a connected BI tool.
2. BigQuery checks permissions and plans the query.
3. It prunes partitions and clustered blocks that filters exclude and reads only the referenced columns.
4. Stages run in parallel across slots. Joins and aggregations shuffle data between stages.
5. Results are written to a temporary table and returned. Identical repeated queries can be served from a results cache.

Paying for it

In the on-demand model you pay for the bytes each query processes, so reading fewer columns and partitions directly lowers cost. In the capacity model you reserve slots through an edition and queries share that capacity. Storage is billed separately, with a lower rate for data that has not been modified for 90 days.

CREATE TABLE shop.events (
  event_ts TIMESTAMP, user_id STRING, country STRING, amount NUMERIC)
PARTITION BY DATE(event_ts)
CLUSTER BY country, user_id;

Key concepts and terms

A worked example

This scenario is illustrative, and figures are approximate. A subscription app records 50 million events a day, about 25 GB per day, and keeps three years, around 27 TB.

1. Events stream in through the Storage Write API from a pipeline. The table is partitioned by event date and clustered by country and user_id.
2. A product analyst asks for daily active users in Germany over the last 30 days. Partition pruning limits the scan to 30 of about 1,100 partitions. Only two columns are needed, and clustering on country lets BigQuery skip most blocks within each partition. A dry run estimates a few gigabytes rather than terabytes.
3. A dashboard refreshes the same numbers every hour. The team creates a materialised view of daily counts per country, so dashboard queries read a small, precomputed table.
4. A data scientist trains a churn model with BigQuery ML using CREATE MODEL, evaluates it with ML.EVALUATE and writes predictions with ML.PREDICT, without exporting the data.
5. Finance sets a project-level quota on bytes processed per day and reviews the most expensive queries weekly from the INFORMATION_SCHEMA jobs views.

Strengths and limitations

Strengths:

• No infrastructure to manage; capacity scales automatically.
• Separate storage and compute, with very large parallel scans.
• Strong SQL support, including nested and repeated fields.
• Built-in ML and AI functions, geospatial analysis and BI acceleration.
• Integration with Google Cloud services and many third-party tools.
• Storage APIs let engines such as Spark read and write BigQuery tables efficiently.

Limitations:

• On-demand costs can surprise you if queries scan large tables carelessly.
• Not designed for high volumes of single-row updates or low-latency transactional work.
• Proprietary storage and SQL extensions increase lock-in to Google Cloud.
• Fine performance tuning is limited compared with systems where you control hardware.
• Quotas and limits apply to DML statements, streaming and concurrent queries.

How it compares with alternatives

Practical guidance and best practices

1. Partition large tables by date or timestamp and cluster by columns used in filters and joins.
2. Avoid SELECT *; list the columns you need, since cost and speed depend on columns read.
3. Use dry runs and the query plan to check bytes processed before running expensive queries.
4. Set custom quotas and budgets, and require partition filters on very large tables.
5. Use materialised views or summary tables for dashboards that run the same queries repeatedly.
6. Choose between on-demand and capacity pricing based on steady workload volume; review regularly.
7. Keep datasets in a location close to related data and services to avoid cross-region costs.
8. Manage access with IAM roles at dataset or table level, and use column-level and row-level security for sensitive data.

Common mistakes

• Running SELECT * on a wide, multi-terabyte table to look at a few rows; use table preview instead.
• Partitioning but then filtering on an expression that prevents partition pruning.
• Treating BigQuery as an operational database with frequent single-row updates.
• Leaving development projects without quotas, so one runaway query costs a lot.
• Creating thousands of small date-named tables instead of one partitioned table.
• Ignoring the data location and paying for cross-region transfers.

Frequently asked questions

Is BigQuery really serverless?

From the user's point of view, yes. You do not provision or manage servers or clusters. Google allocates compute slots for each query from shared or reserved capacity and handles storage, replication and upgrades.

How is BigQuery priced?

There are two main parts. Compute is either on-demand, charged by bytes processed, or capacity-based, using slots reserved through an edition. Storage is charged separately, with a lower rate for long-term data. Prices change, so check Google's current pricing page.

Can BigQuery query data outside its own storage?

Yes. External tables and BigLake tables can query files such as Parquet and ORC in object storage, and federated queries can reach some operational databases. Performance and features differ from native tables.

What is BigQuery ML?

It lets you create, train, evaluate and use machine learning models with SQL statements inside BigQuery. It supports several model types and can call externally hosted models, which keeps data in the warehouse.

Can I update and delete rows in BigQuery?

Yes, with standard DML statements such as UPDATE, DELETE and MERGE. They work well in batches but are not designed for thousands of tiny transactions per second.

Is BigQuery suitable for real-time analytics?

It can ingest streaming data and make it queryable within seconds, which suits many near-real-time dashboards. For very low-latency, high-concurrency serving, a specialised database may fit better.

Glossary

• Dremel: Google's internal interactive query system that BigQuery descends from.
• Edition: a capacity pricing tier, such as Standard, Enterprise or Enterprise Plus.
• IAM: Google Cloud's identity and access management.
• Nested and repeated fields: columns that hold structs and arrays.
• Results cache: stored results of recent identical queries.
• Shuffle: exchanging intermediate data between query stages.
• Storage Write API: high-throughput API for streaming and batch ingestion.
• BigLake: a way to manage and query open-format data in object storage with BigQuery governance.

Summary

BigQuery is a serverless warehouse that separates storage from compute and runs SQL across a large pool of workers, so teams can analyse terabytes or petabytes without managing infrastructure. Its columnar storage, partitioning and clustering decide how much data a query reads, which in turn drives both speed and cost. It adds ML, AI functions and access to open-format data, but it is not a transactional database and it ties you closely to Google Cloud. Comparing it with Amazon Redshift, Snowflake and a lakehouse approach helps you choose; see also Vertex AI for Google's wider ML platform.

Related

It belongs to Query engines and warehouses in my stack, under Big data. Others in the same group: