BigQuery cheat sheet

-- Table references need at minimum dataset.table; usually project.dataset.table.
SELECT * FROM `my-project.analytics.orders`;
SELECT * FROM `my-project.analytics`.orders;            -- dataset-qualified
SELECT * FROM analytics.orders;                          -- relies on default project

-- Datasets live inside a project; tables live inside a dataset.
-- "Dataset" in BigQuery = "schema" in Postgres.

-- Wildcard tables (event_20260401, event_20260402, ...).
SELECT * FROM `my-project.events.event_20260401`;
SELECT * FROM `my-project.events.event_*`
WHERE  _TABLE_SUFFIX BETWEEN '20260401' AND '20260430';

-- Types: INT64, FLOAT64, NUMERIC, BIGNUMERIC, BOOL, STRING, BYTES,
--        DATE, DATETIME, TIME, TIMESTAMP, GEOGRAPHY, JSON, ARRAY<T>, STRUCT.

-- STRUCT (record / nested object).
SELECT STRUCT(id, email, plan) AS user_record FROM users;
SELECT STRUCT('Ana' AS name, 30 AS age);                   -- inline literal

-- Access STRUCT fields with dot notation.
SELECT user_record.email FROM users_struct_table;

-- ARRAY of STRUCT (the BigQuery idiom for nested data).
SELECT
  user_id,
  ARRAY_AGG(STRUCT(order_id, total)) AS orders
FROM orders
GROUP BY user_id;

-- Single-column array.
SELECT id, tag
FROM   posts, UNNEST(tags) AS tag;

-- Array of structs (most common BigQuery pattern).
SELECT
  u.user_id,
  o.order_id,
  o.total
FROM   user_orders u, UNNEST(u.orders) AS o;

-- Keep parents with empty arrays via LEFT JOIN UNNEST.
SELECT u.user_id, o.order_id
FROM   user_orders u
LEFT JOIN UNNEST(u.orders) AS o ON TRUE;

-- WITH OFFSET to get array index.
SELECT day, ARRAY_LENGTH(events) AS n, e, idx
FROM   daily_events, UNNEST(events) AS e WITH OFFSET AS idx;

-- Drop columns from SELECT *.
SELECT * EXCEPT (password, secret_key) FROM users;

-- Modify columns in SELECT *.
SELECT * REPLACE (UPPER(email) AS email) FROM users;

-- Combine.
SELECT * EXCEPT (raw_payload) REPLACE (UPPER(email) AS email) FROM users;

CURRENT_DATE()                              -- today (note parens)
CURRENT_DATETIME()                          -- no timezone
CURRENT_TIMESTAMP()                         -- UTC

-- DATE_TRUNC: argument order is (date, part) — opposite of Postgres!
DATE_TRUNC(order_date, MONTH)               -- first of month
DATETIME_TRUNC(ts, DAY)
TIMESTAMP_TRUNC(ts, HOUR)

EXTRACT(YEAR FROM order_date)
DATE_DIFF(end, start, DAY)                  -- (end, start, unit)
DATE_ADD(d, INTERVAL 7 DAY)
DATE_SUB(d, INTERVAL 1 MONTH)

LAST_DAY(d, MONTH)                          -- with unit

-- Parse / format.
PARSE_DATE('%d/%m/%Y', '26/04/2026')
FORMAT_DATE('%Y-%m-%d', d)

-- Concatenation: CONCAT or the || operator (STRING, BYTES, ARRAY).
CONCAT('a', 'b', 'c')
'a' || 'b' || 'c'

-- Common functions.
SUBSTR(s, 1, 5)
LENGTH(s)                                   -- character count
SPLIT(s, ',')                               -- returns ARRAY<STRING>
UNNEST(SPLIT(s, ',')) AS part               -- explode to rows

REGEXP_CONTAINS(s, 'pattern')               -- match (RE2 syntax)
REGEXP_REPLACE(s, 'pattern', 'replacement')
REGEXP_EXTRACT(s, 'pattern')                -- first match
REGEXP_EXTRACT_ALL(s, 'pattern')            -- all matches as array

JSON_VALUE(payload, '$.user_id')                     -- scalar string
JSON_QUERY(payload, '$.user')                        -- nested JSON
JSON_EXTRACT_SCALAR(payload, '$.user_id')            -- legacy alias
UNNEST(JSON_EXTRACT_ARRAY(payload, '$.items')) AS i  -- array → rows
UNNEST(JSON_QUERY_ARRAY(payload, '$.items'))         -- new JSON-typed version

-- Build.
TO_JSON(some_struct)
JSON_OBJECT('id', id, 'email', email)

-- Create a partitioned table (filters by partition column become free).
CREATE TABLE `my-project.analytics.events`
PARTITION BY DATE(occurred_at)
CLUSTER BY user_id, event_type
AS SELECT * FROM source_events;

-- Query a single partition (cheap).
SELECT * FROM `my-project.analytics.events`
WHERE DATE(occurred_at) = DATE '2026-04-26';

-- Estimated bytes scanned (matters because BigQuery charges per byte):
-- use a dry run (bq query --dry_run, or the console's query validator).
-- BigQuery has no EXPLAIN statement.

-- BigQuery charges by bytes scanned. SELECT * scans every column.
-- Always prefer column lists.
SELECT user_id, total FROM orders;

-- Filter to a partition to limit scanned bytes.
WHERE _PARTITIONDATE = DATE '2026-04-26'

-- LIMIT does NOT reduce bytes scanned (whole table is still read).

-- Approximate functions for big-data scale.
APPROX_COUNT_DISTINCT(user_id)
APPROX_QUANTILES(total, 100)[OFFSET(95)] AS p95
APPROX_TOP_COUNT(category, 10)

-- Tables in a dataset.
SELECT table_name
FROM   `my-project.my_dataset.INFORMATION_SCHEMA.TABLES`;

-- Column metadata.
SELECT column_name, data_type, is_nullable
FROM   `my-project.my_dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE  table_name = 'orders';

-- Region-scoped (works across all datasets in a region).
SELECT * FROM `region-us.INFORMATION_SCHEMA.JOBS`
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 1 HOUR);