BigQuery is Google Cloud's fully-managed, serverless data warehouse designed for fast, scalable SQL analytics on petabyte-scale datasets. Built on Google's Dremel columnar storage architecture, it separates storage from compute, enabling elastic resource allocation and cost-effective querying. Beyond SQL analytics, BigQuery now functions as an autonomous data-to-AI platform with built-in vector search, native LLM inference via Gemini, and real-time continuous queries—all accessible through standard SQL. A critical mental model: cost is primarily driven by data scanned—optimizing queries with partitioning, clustering, and column selection directly reduces both latency and billing.
What This Cheat Sheet Covers
This topic spans 22 focused tables and 221 indexed concepts, 124 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
A jump-to index of every table row in this cheat sheet.
An interactive map of every table and concept in this topic.
Table 1: Data Types
Picking the right column type in BigQuery is partly about correctness and partly about cost and performance down the line. Note the deliberately small set of numeric types — INT64 is the only integer type, and NUMERIC versus FLOAT64 is the classic choice between exact decimal arithmetic for money and fast floating point for measurements. The nested types (ARRAY, STRUCT, JSON) are what make BigQuery's denormalized, columnar model so powerful.
| Type | Example | Description | |
|---|---|---|---|
SELECT 42 AS count | • Only integer type in BigQuery — signed 64-bit integer ranging from -2⁶³ to 2⁶³-1 • use for IDs, counts, and join keys | ||
SELECT 3.14159 AS pi | • Double-precision floating point — IEEE 754 standard • use for measurements and calculations where precision loss is acceptable | ||
SELECT NUMERIC '99.99' AS price | Exact decimal with 38 digits of precision and 9 decimal places — ideal for financial calculations requiring exact arithmetic | ||
SELECT BIGNUMERIC '1e100' AS large | Extended precision decimal with 76 digits of precision and 38 decimal places — for extremely large or precise calculations | ||
SELECT 'Hello' AS greeting | • Variable-length UTF-8 text — no length limit • use for text, URLs, and categorical data | ||
SELECT TRUE AS flag | • Boolean — TRUE, FALSE, or NULL • use in WHERE clauses and conditional logic | ||
SELECT TIMESTAMP '2026-03-04 10:30:00 UTC' | • Absolute point in time — always in UTC internally • automatically converts timezones • most common for event data | ||
SELECT DATE '2026-03-04' AS today | • Calendar date — format YYYY-MM-DD, range 0001-01-01 to 9999-12-31 • use for day-level partitioning | ||
SELECT DATETIME '2026-03-04 10:30:00' | • Date and time without timezone — format YYYY-MM-DD HH:MI:SS • represents a point in local time | ||
SELECT TIME '10:30:00' AS morning | • Time of day independent of date — format HH:MI:SS • use for schedules and recurring time patterns | ||
SELECT [1, 2, 3] AS numbers | • Ordered list of values of the same type — zero-indexed • flatten with UNNEST or aggregate with ARRAY_AGG | ||
SELECT STRUCT(1 AS id, 'Alice' AS name) | • Container of named fields with potentially different types — access with dot notation • use for nested/denormalized data | ||
SELECT JSON '{"key": "value"}' | • Structured JSON document — stored in parsed columnar format • query with JSON_VALUE and JSON_QUERY | ||
SELECT ST_GEOGPOINT(-122.4, 37.8) | • Geospatial data — points, lines, polygons in WGS84 coordinate system • use with GIS functions for spatial analysis | ||
SELECT RANGE<DATE> '[2026-01-01, 2026-12-31)' AS q1 | • Half-open interval of DATE, DATETIME, or TIMESTAMP values — [start, end) notation• use for time-range comparisons with OVERLAPS or IN operators | ||
SELECT b'\x00\xFF' AS binary | Raw binary data — for images, encrypted data, or binary protocols |