Skip to content
Tutorials Center
My learning

Topics

Lesson 1 of 37 ยท Getting Started

What Is Google BigQuery?

Learn what Google BigQuery is, how a serverless data warehouse differs from a normal database, what it is good at, and what it is not for, before writing any SQL.

Google BigQuery is a serverless data warehouse. You load large amounts of data into it, write SQL, and get answers back, without ever creating a server, choosing a disk size or tuning an index. Google runs thousands of machines behind the scenes, splits your query across them, and hands you one result table. A query over a few gigabytes finishes in seconds. A query over many terabytes often finishes in under a minute.

This course teaches BigQuery from first query to production pipelines. Before we start, you need a clear mental picture of what kind of tool it is, because most BigQuery mistakes (slow queries, surprising bills, wrong tool choice) come from treating it like the database behind a web shop.

Honest note about this site. BigQuery is a Google Cloud service, so it cannot run inside your browser. Every example on a runnable editor in this course uses a small SQLite database as a labelled stand-in: it runs the portable parts of the SQL (SELECT, joins, aggregates, window functions, JSON) so you can experiment right here. Syntax that only exists in BigQuery (for example UNNEST, STRUCT, QUALIFY, CREATE MODEL) is shown in read-only blocks, and I only show output when I am certain what BigQuery returns.

Warehouse versus application database

A web application stores one customer's order, updates a stock number, and looks up a single row by key. This is called OLTP (online transaction processing). Databases such as MySQL and PostgreSQL are built for it: they keep rows together on disk, use indexes, and handle thousands of tiny reads and writes per second.

A warehouse answers questions such as "what was revenue per country per month for the last three years?" This is OLAP (online analytical processing). Such a query touches millions of rows but only a few columns, and it rarely changes data. BigQuery stores data by column, compresses it heavily, and scans only the columns your query names.

Question OLTP database BigQuery
Typical query Fetch order 8841 Sum revenue by month over all orders
Rows touched A handful Millions to billions
Storage layout By row By column
Updates Frequent, one row at a time Rare, in bulk
Latency goal Milliseconds Seconds
You manage Servers, indexes, backups Nothing: it is serverless

What BigQuery gives you

Serverless No servers to size
Fast at scale Terabytes in seconds
Plain SQL GoogleSQL dialect
Pay per use Storage plus queries
1 / 2

What a query looks like

BigQuery uses GoogleSQL, a dialect that follows the SQL standard and adds its own features. If you know ordinary SQL you already know most of it. This is a typical BigQuery query. It is read-only here because the table lives in Google's public datasets:

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

Notice the backticks around the table name. A BigQuery table is addressed as project.dataset.table. You will learn what each part means in Projects, Datasets and Tables.

The same idea runs fine on our stand-in database. Here is "total stock value per category" on a small shop:

SQL Stand-in: an analytical question in SQL ยท database: shop

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

The shape is exactly what you will write in BigQuery: join, group, aggregate, sort. Only table naming and a few functions change.

Where BigQuery fits in a data stack

Most companies feed BigQuery from many places and read from it in many places.

flowchart LR
  A["Web app database"] --> W["BigQuery warehouse"]
  B["Logs and events"] --> W
  C["CSV files in Cloud Storage"] --> W
  D["SaaS exports (ads, CRM)"] --> W
  W --> E["Dashboards (Looker Studio)"]
  W --> F["Notebooks and Python"]
  W --> G["ML models (BigQuery ML)"]

Data flows in from operational systems; analysts, dashboards and models read from one place.

Strengths and limits

BigQuery shines when:

  • Tables are large (millions of rows and up) and questions span many of them.
  • You want to start now. There is nothing to install, and there is a free tier (a monthly allowance of storage and query processing, plus a sandbox that needs no billing card).
  • You join data from many sources into one place.
  • You want machine learning, geospatial functions or JSON handling next to your SQL.

It is a poor choice when:

  • Your app needs to read or write single rows thousands of times per second with millisecond latency.
  • Your data is tiny (a few thousand rows). Any database will do, and a spreadsheet may be enough.
  • You need strict row-level locking for concurrent writers.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does 'serverless' mean for BigQuery?

    Servers exist, but Google provisions and scales them. You only write SQL and choose where your data lives.
  2. 2. Which workload fits BigQuery best?

    BigQuery is an analytical (OLAP) warehouse. Big scans and aggregations are its strength; single-row transactions are not.
  3. 3. Why does column-oriented storage help analytical queries?

    If a table has 50 columns and you select 3, a columnar engine scans about 3/50 of the data.
  4. 4. Why do the editors in this course use SQLite?

    BigQuery is a cloud service. The stand-ins run the portable SQL parts; BigQuery-only syntax is shown read-only.

Summary

  • BigQuery is a serverless, columnar data warehouse queried with GoogleSQL.
  • It is built for analytics (OLAP): scans and aggregations over large tables.
  • It is not designed for high-rate single-row transactions (OLTP).
  • You pay for stored data and for the data your queries scan, and there is a free allowance.
  • On this site, runnable examples use a labelled SQLite stand-in; BigQuery-only syntax is read-only.

Next: The BigQuery Console Tour