BasicsBigQuery

BigQuery concepts

How BigQuery works, explained with examples you can run in the browser: 107 articles on cost, storage, SQL, writing data, scripting, performance, security and operations, checked against BigQuery.

These articles explain what is specific to BigQuery: how it bills a query, how partitioning and nested data work, which SQL behaves differently from other databases, and the limits you run into. They are based on Google's BigQuery documentation, rewritten and tested: every example on these pages was run on BigQuery, and the result you see is BigQuery's answer. On a desktop browser you can edit an example and run it on Querylab.io's BigQuery engine without an account.

New to BigQuery? Start with How BigQuery works and How a query is billed.

Basics

How BigQuery works BigQuery keeps storage and compute separate, stores each column on its own, and runs a query as parallel stages on slots.
How BigQuery SQL differs from other SQL BigQuery SQL (GoogleSQL) uses backticks for names, has no implicit string-to-number casts, only one integer type, and requires ALL or DISTINCT after UNION.
Projects, datasets and tables in BigQuery A BigQuery table is named project.dataset.table.
BigQuery locations and regions Every BigQuery dataset has one location, a region like europe-west1 or a multi-region like US or EU, and a query runs in the location of the data it reads.
BigQuery jobs Every query, load, export and copy in BigQuery runs as a job with an ID, a state and a project that is billed.

Cost and pricing

How BigQuery bills a query On-demand BigQuery charges for the bytes in the columns a query reads, not for rows returned or time spent.
BigQuery on-demand vs capacity pricing (editions) On-demand BigQuery charges per TiB of data a query reads.
BigQuery slots explained A BigQuery slot is a unit of compute that runs part of a query.
BigQuery reservations, commitments and assignments How BigQuery capacity pricing is set up.
BigQuery storage pricing: active, long-term, logical and physical BigQuery bills stored data per GiB-month at an active or long-term rate, measured in logical or physical bytes.
BigQuery query cache BigQuery keeps each query result for about 24 hours and returns it for free when the same query runs again.
Controlling BigQuery costs: dry runs, byte limits, quotas and partition filters The ways to cap what BigQuery queries can cost.
BigQuery free tier and sandbox BigQuery gives every account 1 TiB of query processing and 10 GiB of storage free each month, and the sandbox lets you use it without a credit card.

Tables and storage

BigQuery table types BigQuery has standard tables, external tables, views, materialized views, snapshots, clones and temporary tables.
BigQuery data types BigQuery has one integer type (INT64), exact decimals (NUMERIC, BIGNUMERIC) and an approximate FLOAT64.
Nested and repeated data in BigQuery BigQuery stores child rows inside the parent row as a STRUCT or an ARRAY of STRUCTs, so related data needs no join.
Partitioned tables in BigQuery A partitioned BigQuery table is split by a date, timestamp or integer column.
Clustered tables in BigQuery A clustered BigQuery table keeps rows with similar values in up to four columns stored together, so a filter on those columns reads fewer blocks.
Partitioning vs clustering in BigQuery Partition a BigQuery table when queries filter on a date and you need a firm cost estimate.
Wildcard tables and _TABLE_SUFFIX in BigQuery A wildcard table queries many similarly named BigQuery tables as one, and _TABLE_SUFFIX chooses which of them are read.
Primary and foreign keys in BigQuery BigQuery primary and foreign keys are not enforced: duplicates and orphans go in without an error.
Default values and identity columns in BigQuery How DEFAULT works in BigQuery tables (what you can use, when it applies, what it skips) and how identity columns generate unique integers, including the ALWAYS and BY DEFAULT modes and their limits.
Schema changes in BigQuery tables What ALTER TABLE can change in a BigQuery table: add columns at the end, relax REQUIRED to NULLABLE, widen a few types, rename and drop columns.
Time travel and fail-safe in BigQuery BigQuery keeps changed and deleted data for 2 to 7 days (7 by default) and lets you query it with FOR SYSTEM_TIME AS OF.
Table snapshots and table clones in BigQuery A BigQuery table snapshot is a read-only copy that you keep as long as you like.
Table and partition expiration in BigQuery BigQuery can delete tables and partitions on a schedule.
Dataset replication and disaster recovery in BigQuery A BigQuery multi-region keeps your data in one region and gives no protection from a regional outage.

Querying

NULL in BigQuery BigQuery rejects x = NULL with an error instead of returning no rows, refuses NULL elements in arrays it writes, and has IS NOT DISTINCT FROM for NULL-safe equality.
Arrays in BigQuery How to build, index, aggregate and transform ARRAY values in BigQuery.
UNNEST in BigQuery UNNEST turns an array into rows.
STRUCT in BigQuery A STRUCT groups named fields into one value, like a row inside a column.
JSON in BigQuery BigQuery has a native JSON type with dot access, and JSON_VALUE / JSON_QUERY functions for JSON stored as text.
DATE, DATETIME, TIMESTAMP and time zones in BigQuery TIMESTAMP is an instant in UTC, DATETIME is a wall-clock time with no zone, DATE is a calendar day.
INTERVAL and RANGE types in BigQuery INTERVAL stores a duration as months, days and time separately, so 36 hours stays 36 hours until you justify it.
CAST and type conversion in BigQuery How CAST, SAFE_CAST and implicit coercion work in BigQuery.
SAFE_CAST, SAFE_DIVIDE and the SAFE. prefix in BigQuery One bad value stops a whole BigQuery query.
UNION, INTERSECT and EXCEPT in BigQuery In BigQuery, UNION must say ALL or DISTINCT, INTERSECT and EXCEPT only support DISTINCT, columns match by position unless you add CORRESPONDING or BY NAME, and NULLs count as equal.
Aggregate functions in BigQuery BigQuery's aggregates beyond SUM and COUNT, with the details that differ from other databases.
GROUP BY, ROLLUP, CUBE and GROUPING SETS in BigQuery BigQuery's GROUP BY accepts SELECT aliases, column numbers and GROUP BY ALL, and computes subtotals with ROLLUP, CUBE and GROUPING SETS.
Joins in BigQuery How JOIN behaves in BigQuery.
Subqueries in BigQuery Scalar, ARRAY, STRUCT, IN and EXISTS subqueries in BigQuery.
Hashing, deterministic sampling and A/B buckets in BigQuery FARM_FINGERPRINT gives a fast, stable INT64 hash for bucketing users, splitting A/B tests and finding duplicate rows.
Legacy SQL vs GoogleSQL in BigQuery How to recognize BigQuery legacy SQL and what changes when you move a query to GoogleSQL.
GEOGRAPHY and spatial functions in BigQuery BigQuery's GEOGRAPHY type is a set of points, lines and polygons on a sphere, with edges that follow great circles and distances in meters.
SEARCH function and search indexes in BigQuery SEARCH checks whether every term of a query appears as a token in your text or JSON.
Vector search in BigQuery VECTOR_SEARCH finds the nearest embeddings in a table by Euclidean, cosine or dot product distance.
Graph queries in BigQuery (GQL and CREATE PROPERTY GRAPH) BigQuery Graph lets you define a property graph over existing tables and query it with GQL or GRAPH_TABLE.
Window functions in BigQuery How BigQuery window functions pick their frame.
PIVOT and UNPIVOT in BigQuery BigQuery has PIVOT and UNPIVOT operators in the FROM clause.
Recursive CTEs in BigQuery WITH RECURSIVE in BigQuery needs UNION ALL, one self-reference, the same column types in both terms, and stops with an error after 500 iterations.
Pipe syntax in BigQuery BigQuery pipe syntax writes a query as a chain of |> steps in execution order, starting with FROM.
Strings, regular expressions and collation in BigQuery BigQuery compares strings byte for byte, so Apple and apple differ and Z sorts before a.
Approximate aggregation and HLL sketches in BigQuery APPROX_COUNT_DISTINCT, APPROX_QUANTILES and APPROX_TOP_COUNT trade a small error for speed on large data.
TABLESAMPLE in BigQuery TABLESAMPLE SYSTEM reads a random percentage of a table's storage blocks, and on-demand queries are billed only for the blocks read.
Query parameters in BigQuery BigQuery query parameters are named (@name) or positional (?), carry a type, and replace values only, never table or column names.
QUALIFY in BigQuery QUALIFY filters rows on the result of a window function, the way HAVING filters on an aggregate.
Reusable column aliases in BigQuery With reusable column aliases on, a SELECT alias can be used by later SELECT items and the WHERE of the same query.

Writing data

INSERT, UPDATE and DELETE in BigQuery BigQuery DML needs a WHERE clause on every UPDATE and DELETE, rejects an UPDATE that matches a row twice, bills a filtered UPDATE or DELETE for the whole table, and runs at most two mutating statements per table at once.
MERGE in BigQuery MERGE updates, inserts and deletes in one statement, which is how you upsert in BigQuery.
CREATE TABLE, ALTER and DROP in BigQuery BigQuery DDL runs as a query job.
Loading data into BigQuery Batch load jobs are free and accept CSV, JSON, Avro, Parquet and ORC.
Streaming data into BigQuery The Storage Write API streams rows into BigQuery so they can be queried within seconds.
Change data capture in BigQuery BigQuery CDC applies streamed UPSERT and DELETE rows to a table by primary key, in the background.
Exporting data from BigQuery Extract jobs copy a table to Cloud Storage for free; EXPORT DATA writes a query's result and is billed as a query.

Scripting and routines

Scripting and multi-statement queries in BigQuery A BigQuery script is several statements run as one job, with variables, IF, loops, error handling and dynamic SQL.
Temporary tables in BigQuery CREATE TEMP TABLE works only in a script or a session, takes an unqualified name, and keeps the table (and its storage bill) for up to 24 hours unless you drop it.
Transactions in BigQuery BEGIN TRANSACTION ...
Sessions in BigQuery A BigQuery session keeps temp tables, variables, temporary functions and settings like @@time_zone between separate queries, for up to 24 hours of inactivity.
User-defined functions in BigQuery (SQL, JavaScript and Python) BigQuery UDFs come in SQL, JavaScript and Python.
Table functions in BigQuery A table function is a parameterized view, a stored query you call in FROM with arguments.
Stored procedures in BigQuery A BigQuery stored procedure is a saved script you run with CALL.
Remote functions in BigQuery A remote function calls your own Cloud Run service from SQL, sending rows in batches as JSON.

Views

Views in BigQuery A BigQuery view is a saved query.
Materialized views in BigQuery A materialized view stores the result of an aggregation query and keeps it fresh.
Authorized views, datasets and routines in BigQuery An authorized view lets people query a slice of a table without any access to the table.

Performance

How a BigQuery query runs BigQuery turns a query into stages that exchange data through shuffle.
BigQuery query performance best practices Which BigQuery performance advice is worth following, measured.
Optimizing joins in BigQuery How BigQuery runs a join, measured on tables of up to 10 million rows.
BigQuery BI Engine BI Engine is an in-memory cache for BigQuery tables that you rent by the gibibyte.

Security and governance

BigQuery access control and IAM roles Who can read, change and bill queries in BigQuery.
Row-level security in BigQuery How BigQuery row access policies filter rows per user.
Column-level security and data masking in BigQuery How BigQuery policy tags restrict a column to some users, what a query on a restricted column does, how data masking rules return hashed, nulled or truncated values instead, and what we measured on tagged tables.
Encryption in BigQuery BigQuery encrypts all data at rest by default.
Differential privacy in BigQuery SELECT WITH DIFFERENTIAL_PRIVACY adds noise to aggregates and drops small groups, so a result can't reveal one person.

BigQuery sharing (Analytics Hub) explained BigQuery sharing, formerly Analytics Hub, lets you share a dataset with other projects and organizations without copying it.
BigQuery data clean rooms and analysis rules A BigQuery data clean room lets several parties query each other's data without seeing the raw rows.

Metadata and operations

INFORMATION_SCHEMA in BigQuery INFORMATION_SCHEMA views describe tables, columns, partitions, jobs, storage and more, queried with SQL.
Monitoring BigQuery jobs and costs with INFORMATION_SCHEMA.JOBS Ready-to-run queries on INFORMATION_SCHEMA.JOBS for cost per user, the most expensive queries, repeated queries, cache hit rate, failures, the tables that cost the most, and slot usage over time, plus the traps that double-count script jobs.
Labels, billing export and cost attribution in BigQuery How to find out which team, pipeline or dashboard spends your BigQuery money.
BigQuery audit logs explained BigQuery writes Admin Activity and Data Access audit logs to Cloud Logging, with Data Access logs on by default.
BigQuery quotas and limits that matter The BigQuery limits people actually run into, grouped by what you are doing.
Common BigQuery errors and how to fix them What the most common BigQuery error messages mean and how to fix each one, from "Unrecognized name" and "neither grouped nor aggregated" to "Cannot access field on a value with type ARRAY", "Resources exceeded" and "Query exceeded limit for bytes billed".
Scheduled queries in BigQuery Scheduled queries run SQL on a schedule through the Data Transfer Service, with @run_time and @run_date parameters for incremental loads.
Continuous queries in BigQuery A continuous query processes rows as they land in a table and writes results to another table, Pub/Sub, Bigtable or Spanner.

External data

External tables in BigQuery An external table queries files in Cloud Storage, Drive or Bigtable without loading them.
BigLake and Apache Iceberg tables in BigQuery BigLake tables read files in Cloud Storage or other clouds through a connection, so users need access to the table only and row- and column-level security work.
Federated queries in BigQuery (Cloud SQL, Spanner, AlloyDB) EXTERNAL_QUERY sends SQL to Cloud SQL, AlloyDB or Spanner and returns the result to BigQuery.
Object tables in BigQuery An object table lists the files in a Cloud Storage bucket as rows, with their metadata, so you can filter them in SQL and send their contents to AI and ML functions.

AI and ML

BigQuery ML in SQL BigQuery ML trains and runs models with CREATE MODEL and ML.PREDICT.
AI functions in BigQuery (AI.GENERATE, AI.IF, AI.CLASSIFY) BigQuery calls Gemini from SQL with AI.GENERATE, AI.IF, AI.CLASSIFY, AI.SCORE and others.
Time-series forecasting in BigQuery BigQuery forecasts time series two ways.

Tools and APIs

The BigQuery Storage Read API The Storage Read API streams table data out of BigQuery in parallel, in Arrow or Avro, with column and row filters, for Spark, pandas and other engines.
BigQuery DataFrames (bigframes) explained BigQuery DataFrames is a pandas-style Python library that turns your code into SQL and runs it in BigQuery.
BigQuery and BI tools - Connected Sheets, Data Studio, Looker, Tableau Every BI tool sends real query jobs to BigQuery, and each refresh of a dashboard can bill.
BigQuery Data Transfer Service explained The BigQuery Data Transfer Service loads data into BigQuery on a schedule from SaaS apps, Google ad and analytics products, other clouds and other warehouses.
Migrating to BigQuery from Snowflake, Redshift, Teradata and Oracle The BigQuery Migration Service assesses a warehouse, translates SQL from about twenty dialects into GoogleSQL, and moves data.
Gemini in BigQuery - what each feature does and what it costs Gemini in BigQuery covers SQL and Python assistance, data insights, data canvas, data preparation, conversational analytics with data agents, and a pipeline agent.