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. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
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 |