Google BigQuery Tutorial
Learn Google BigQuery step by step: 37 lessons in 9 chapters with examples, quizzes, diagrams and projects.
Start learning →
of lessons completed
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.
- The BigQuery Console Tour Take a guided tour of the BigQuery web console: the Explorer panel, query editor, validator, results tabs, job history, and the free sandbox you can use without a billing card.
- Your First BigQuery Queries Write your first BigQuery queries: SELECT, WHERE, ORDER BY, LIMIT and aliases, with the BigQuery habits that matter early: naming tables, avoiding SELECT * and reading the byte estimate.
- Projects, Datasets and Tables in BigQuery Understand how BigQuery organizes data into projects, datasets, tables, views and routines, how to name them, what a location means, and how to create a dataset and table with SQL.
Architecture and Setup
- BigQuery Architecture: Dremel, Colossus and Slots See how BigQuery separates storage from compute, how Dremel splits a query into stages across slots, what Capacitor columnar storage is, and how this explains cost and speed.
- Setting Up BigQuery: Projects, IAM and the bq CLI Set up BigQuery for real work: create a Google Cloud project, enable the API, understand IAM roles for data and jobs, install gcloud, and run queries from the bq command line tool.
- BigQuery Data Types A complete guide to GoogleSQL data types in BigQuery: INT64, FLOAT64, NUMERIC, BOOL, STRING, BYTES, DATE, DATETIME, TIMESTAMP, ARRAY, STRUCT, JSON and GEOGRAPHY, with casting rules.
- Loading and Exporting Data in BigQuery Learn every way to get data into BigQuery (load jobs, bq load, LOAD DATA, streaming, external tables) and out again (EXPORT DATA), file formats compared, schema autodetect and common load errors.
Querying and Functions
- SELECT EXCEPT, REPLACE and Friends Master the BigQuery SELECT extras: SELECT * EXCEPT and REPLACE, IF, IFNULL, CASE, SAFE_DIVIDE, GENERATE_ARRAY, TABLESAMPLE and trailing commas, with runnable stand-ins for the portable parts.
- Aggregates and GROUP BY in BigQuery Learn BigQuery aggregation: COUNT, SUM, AVG, APPROX_COUNT_DISTINCT, ANY_VALUE, STRING_AGG, ARRAY_AGG, GROUP BY ROLLUP, HAVING, and aggregate filters, with runnable stand-ins.
- Dates, Times and Time Zones in BigQuery Work with dates and timestamps in BigQuery: CURRENT_DATE, DATE_TRUNC, DATE_ADD, DATE_DIFF, EXTRACT, FORMAT_TIMESTAMP, PARSE_DATE and time zone conversion, with SQLite stand-ins.
- Strings, Regex and Text Functions in BigQuery Clean and parse text in BigQuery: CONCAT, SUBSTR, SPLIT, TRIM, REPLACE, REGEXP_CONTAINS, REGEXP_EXTRACT, REGEXP_REPLACE, FORMAT, collation and common pitfalls, with stand-ins.
- Window Functions and QUALIFY in BigQuery Learn BigQuery window (analytic) functions: OVER, PARTITION BY, ORDER BY, frames, ROW_NUMBER, RANK, LAG, LEAD, running totals, moving averages, and the QUALIFY clause for filtering them.
- Joins, CTEs and Scripting in BigQuery Combine tables in BigQuery with INNER, LEFT, FULL and CROSS joins, use CTEs and subqueries, set operations, DECLARE variables, IF and LOOP scripts, temp tables and stored procedures.
Nested and Repeated Data
- Arrays and UNNEST in BigQuery Learn BigQuery arrays: creating ARRAY values, indexing with OFFSET and ORDINAL, ARRAY_LENGTH, UNNEST with CROSS JOIN, WITH OFFSET, ARRAY_AGG, ARRAY_CONCAT and the NULL rules, with JSON stand-ins.
- STRUCT: Records Inside Rows in BigQuery Understand BigQuery STRUCT: create structs, access fields with dot notation, SELECT AS STRUCT, arrays of structs, struct comparison, and how structs relate to nested JSON, with stand-ins.
- Nested and Repeated Schemas in Practice Design BigQuery tables with nested and repeated fields: when to denormalize, a JSON schema for orders with items, loading JSON and Avro, querying patterns, trade-offs and common mistakes.
- JSON and Array Aggregation Patterns in BigQuery Query semi-structured data in BigQuery with the JSON type: JSON_VALUE, JSON_QUERY, JSON_EXTRACT_ARRAY, TO_JSON_STRING, JSON_OBJECT, plus array patterns for dedup, pivots and sessions, with stand-ins.
Partitioning and Clustering
- Partitioned Tables Learn how BigQuery partitioned tables split data into segments, how partition pruning lowers the bytes a query scans, and how to create and filter them.
- Partition Types and Expiration Compare BigQuery's three partition types, choose daily, monthly or hourly granularity, and set partition expiration to drop old data automatically.
- Clustered Tables Learn how BigQuery clustering sorts storage blocks by up to four columns, how it differs from partitioning, and when it speeds up filters and aggregations.
- Choosing Partition and Cluster Keys A practical method for picking BigQuery partition and clustering columns from your real query workload, with a worked example and common design mistakes.
BigQuery ML
- Introduction to BigQuery ML Meet BigQuery ML: train, evaluate and use machine learning models with plain SQL using CREATE MODEL, ML.EVALUATE and ML.PREDICT, without moving data out.
- Regression and Classification Models Train linear, logistic and boosted-tree models in BigQuery ML: label columns, data splits, the TRANSFORM clause, regularization and feature preparation in SQL.
- Evaluating and Predicting with Models Read ML.EVALUATE metrics, run ML.PREDICT and ML.EXPLAIN_PREDICT, and compute precision, recall, accuracy and error from a confusion matrix in SQL.
- Clustering, Forecasting and Remote Models Go beyond prediction in BigQuery ML: segment customers with k-means, forecast time series with ARIMA_PLUS, and call Vertex AI or imported models from SQL.
Security and Cost
- IAM Roles and Dataset Access Understand BigQuery IAM: predefined roles, who needs jobUser versus dataViewer, dataset-level grants, authorized views, and least-privilege patterns.
- Row-Level and Column-Level Security Restrict BigQuery data by row with row access policies, by column with policy tags, and mask sensitive values with data masking, plus encryption basics.
- Pricing: On-Demand and Editions Understand how BigQuery charges for queries and storage: on-demand bytes scanned, capacity editions with slots, logical vs physical storage, and break-even maths.
- Controlling Query Cost Practical ways to cut and cap BigQuery spending: dry runs, maximum bytes billed, column selection, partition filters, caching, materialized views and monitoring.
Pipelines and Integrations
- Loading Data into BigQuery Batch-load CSV, JSON, Avro and Parquet into BigQuery with bq load, LOAD DATA and the console, choose schemas and write modes, and query external tables.
- Scheduled Queries and Routines Automate BigQuery with scheduled queries and parameters, then package logic into SQL and JavaScript UDFs, stored procedures and table functions.
- The Python Client Library Use google-cloud-bigquery from Python: authenticate, run parameterized queries, do dry runs, load DataFrames and stream rows, with a runnable stand-in client.
- Streaming, Dataform and Connected Tools Stream events into BigQuery with the Storage Write API and Pub/Sub, build SQL pipelines with Dataform, and connect Looker Studio, Sheets, dbt and Airflow.
Projects and Practice
- Project: E-commerce Analytics Build an e-commerce analytics warehouse: design partitioned and clustered tables, then write revenue, customer, cohort and basket queries you can run here.
- Project: Event Log Pipeline Build a clickstream pipeline: land raw events, de-duplicate, sessionize with window functions, compute funnels and retention, and check data quality in SQL.
- Practice: BigQuery SQL Exercises Twelve BigQuery-style SQL exercises with runnable solutions, a GoogleSQL translation table, and a closing review of partitioning, cost and security decisions.