Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 45 ยท Data and Analytics

BigQuery Basics

Learn BigQuery on Google Cloud: datasets, tables, standard SQL, on-demand and capacity pricing, loading data and public datasets, with SQL you can run.

BigQuery is Google Cloud's data warehouse. You load tables of data, ask questions in SQL, and get answers over millions or billions of rows in seconds, without creating a server, tuning an index or sizing a cluster. Storage and computation are separate, and Google runs the machinery for you. That makes it a favorite for analytics: sales reports, website logs, product events and the data behind dashboards.

If you already know SQL (see the SQL tutorial), you can be productive quickly: BigQuery speaks GoogleSQL, a standard-compliant dialect with a few handy extras. What is different is the cost model: you usually pay for the amount of data a query reads, so the way you write a query affects your bill. The next lesson, BigQuery Partitioning, Clustering and Cost Control, builds on that.

The building blocks

BigQuery organizes data in three levels inside a project:

  • A project is where billing and permissions live.
  • A dataset is a folder of tables in one location (a region or multi-region, see Regions, Zones and Latency). Access can be granted per dataset.
  • A table holds rows with typed columns. Views and materialized views look like tables but are defined by a query.

A full table name has three parts joined by dots, and you wrap it in backticks:

SQL
SELECT name, SUM(number) AS total
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE state = 'TX'
GROUP BY name
ORDER BY total DESC
LIMIT 5;

That query (read-only here: it needs a Google Cloud project) reads a table from bigquery-public-data, a project where Google publishes free sample datasets such as US baby names, weather and open-source code statistics. Anyone with a project can query them, and you pay only for the bytes your query reads (see the pricing section). This is the best way to practice at real scale.

From files to answers

Load Bring CSV, JSON, Avro or Parquet into a table.
Organize Tables live in datasets inside a project.
Query Ask questions in GoogleSQL.
Share Feed dashboards and other tools.
1 / 2

Writing queries

Most of what you know from other databases works as-is: SELECT, WHERE, GROUP BY, JOIN, window functions, common table expressions. A few details are specific to BigQuery:

  • Table names use backticks when they contain dashes or dots.
  • SELECT * EXCEPT (column) and SELECT * REPLACE (...) modify a star selection.
  • Columns can be nested and repeated: a STRUCT is a record inside a row, an ARRAY is a list inside a row, and UNNEST turns an array into rows. This lets you keep an order and its line items in one table without a join.
  • Standard functions include DATE_TRUNC, SAFE_DIVIDE (returns NULL instead of an error on division by zero) and APPROX_COUNT_DISTINCT (fast and slightly inexact).
SQL
SELECT
  order_id,
  item.sku,
  item.quantity
FROM `my-project.shop.orders`,
  UNNEST(items) AS item
WHERE order_date >= '2025-01-01';

Here items is an array of structs stored inside each order row, so there is no join table to maintain. This too is read-only, because it needs a table that you would have to create.

A stand-in you can run

BigQuery itself cannot run on this site, and the table names above exist only in the cloud. The editors below run the same kind of queries, grouping, joining and filtering, on a small bookstore database using SQLite compiled to your browser. The stand-in differs from the real service in important ways: there is one tiny database in memory, no backticks or project.dataset names, no storage or compute billing, and some function names differ. The shape of the SQL, however, is exactly what you would write in BigQuery.

SQL Stand-in: group and aggregate ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The query asks for one row per genre, the number of books and their average price. In BigQuery you would put the table name in backticks, for example `my-project.bookstore.books`, and the rest would stay the same.

SQL Stand-in: join three tables ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Joins work as usual. In a warehouse you would often avoid some of them by denormalizing: storing the author name inside the books table, since storage is cheap and joins over huge tables are not.

Loading data

You can get data into BigQuery in several ways:

  • Batch load a file from Cloud Storage (CSV, JSON lines, Avro, Parquet, ORC). Avro and Parquet carry their own schema, so they are the least error-prone. Batch loading is free of charge for the load itself, and the data appears when the job finishes.
  • Streaming rows through the Storage Write API for data that must appear in seconds. This costs per volume.
  • Queries that write results into tables (CREATE TABLE ... AS SELECT).
  • Scheduled transfers from other sources such as SaaS tools, and external tables that read files in place from Cloud Storage without copying them.

A load from the command line looks like this (read-only):

Terminal
bq load --source_format=CSV --skip_leading_rows=1 --autodetect \
  shop.sales gs://my-bucket/sales-2025.csv

--autodetect guesses the column types, which is fine for an experiment. For a table that matters, give an explicit schema so one odd value does not turn a number column into text.

Pricing in plain words

BigQuery has two main prices, and prices change, so always check the current price list before you plan a budget.

Storage is charged per gigabyte per month. A table, or a partition of a table, that nobody has modified for 90 days automatically moves to a cheaper "long-term" rate with no loss of speed.

Compute has two models:

  • On-demand: you pay per TiB of data your queries read. There is a monthly free amount per billing account. The number of rows returned and the time the query takes do not matter; the bytes read from the columns you named do. That is why SELECT * on a wide table is costly, and why LIMIT 10 does not make a query cheaper.
  • Capacity (slots, sold as "editions"): you reserve a number of slots, units of computing power, and pay for them by the hour, with autoscaling options. Costs are predictable and queries do not bill by bytes. It suits heavy, steady workloads.

The service also offers a free sandbox that needs no billing account, with small limits, which is a good place to learn. Always set a maximum bytes billed on queries or a project-wide quota so a mistake cannot cost thousands. Also set a budget alert.

Stand-in: estimating an on-demand bill

Because on-demand billing depends on bytes read, a tiny calculator helps build intuition. The price below is a made-up number for the exercise, not a real current rate.

JS Stand-in: what does a query cost?

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The last two lines cost the same: the engine still reads the whole amount column before it can return ten rows. Reading fewer columns is the first and easiest saving.

Everyday BigQuery: sandbox, saved queries, views, UDFs and charts

The sandbox, strengths and limits

The BigQuery sandbox lets you use BigQuery with a Google account and no billing account. It has small free limits (about 10 GiB of storage and 1 TiB of query processing per month at the time of writing), and tables expire automatically after 60 days. It is the best place to practice. Compared with Snowflake, Amazon Redshift and Databricks, BigQuery is serverless and strongest for interactive SQL analytics over very large tables, and it includes ML (BigQuery ML) and streaming ingestion. Its limits: it is not a transactional database (use Cloud SQL or Spanner for that), per-row updates are slow and costly, and a poorly written query can scan a lot of data, so cost control matters (BigQuery Performance and Cost). Some teams also find it less customizable than a cluster they run themselves.

Query history, saved queries and shared queries

  • Personal query history lists everything you ran, for a limited time, so you can reopen a query from last week.
  • A saved query is one you keep with a name. It is private to you until you share it, and it lives in your project.
  • A shared query (a saved query with permissions granted to other people or groups) lets a team reuse and improve the same SQL. For a long-lived team asset, move the SQL into a repository or a view instead of relying on the console.

Views, authorized views and access

Access is managed with IAM on projects and datasets: roles/bigquery.dataViewer reads data, dataEditor changes it, jobUser lets someone run queries (and pays for them), and admin does everything (IAM). A view is a saved query that looks like a table. An authorized view goes further: you give the view permission to read a source dataset, and then people can query the view without any access to the source tables. That is how you expose only certain columns or rows (for example hiding emails) to analysts.

Terminal
# read-only steps: a view in a separate dataset, then authorize it on the source dataset
bq mk --use_legacy_sql=false --view \
  'SELECT order_id, country, total FROM `my-project.private.orders`' \
  my-project:shared.orders_public
# then in the source dataset's sharing settings, choose "Authorize views" and add shared.orders_public

The stand-in uses plain SQLite views to show the idea of exposing only some columns, although SQLite itself has no authorization.

SQL A view that hides columns

Press Run (or Ctrl + Enter) to see the result here.

Functions, external data and loading

  • User-defined functions (UDFs) package reusable logic as SQL or JavaScript functions, for example CREATE TEMP FUNCTION for a query or a persistent function in a dataset.
  • External tables and federated queries read data where it lives (Cloud Storage files, Bigtable, or another database) without loading it first. They are handy for exploring, but slower than native tables.
  • Loading best practices: prefer columnar formats such as Parquet or Avro, load from Cloud Storage in large files rather than many tiny ones, use batch loads (free) unless you need streaming, and partition the destination table.

Charts and sharing results

BigQuery has no chart engine of its own. Looker Studio (formerly Data Studio) connects to a table or query and builds dashboards, and Connected Sheets lets spreadsheet users pivot and chart billions of rows from BigQuery inside Google Sheets without writing SQL. Both run queries on your behalf, so point them at aggregated tables or views to keep costs low.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is a dataset in BigQuery?

    Datasets group tables and views inside a project and set the data location and access control.
  2. 2. With on-demand pricing, what mainly determines the cost of a query?

    On-demand bills for data processed, which depends on the columns read, not on the result size.
  3. 3. Does adding LIMIT 10 to a query reduce the bytes billed on a normal table?

    LIMIT trims the result after the work is done, so it does not usually reduce the bytes scanned.
  4. 4. Which file format carries its own schema, making loads less error-prone?

    Avro and Parquet describe their columns and types, so BigQuery does not have to guess them as it does with CSV.

Summary

  • BigQuery is a serverless data warehouse that you query with GoogleSQL.
  • Data is organized as project, dataset and table; names use backticks.
  • Public datasets in bigquery-public-data are free to practice on, apart from query costs.
  • On-demand pricing charges for bytes read; capacity pricing reserves slots.
  • Select only the columns you need, and set limits on bytes billed and budgets.

Next: BigQuery Partitioning, Clustering and Cost Control