BigQuery SQL cheat sheet

Partitioning, clustering, arrays and structs, cost control, and the BigQuery-specific SQL that saves real money.

SQL & DatabasesIntermediate6 sections

Cost control first

select * from `project.dataset.t` -- DON'T: BigQuery bills by bytes scanned, and SELECT * scans every column. Always name columns on wide tables.
where _partitiontime >= timestamp('2026-08-01'): Filter on the partition column with a literal or parameter — that is what makes pruning kick in.
select count(*) from `p.d.t` where date(ts) = current_date() -- scans everything!: Wrapping the partition column in a function defeats pruning. Filter the raw column: ts >= timestamp(current_date()).
bq query --dry_run --use_legacy_sql=false 'select ...': Dry run returns bytes that would be scanned without charging you. Bake into CI for expensive models.
select * from region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT where creation_time > timestamp_sub(current_timestamp(), interval 1 day) order by total_bytes_billed desc limit 20;: Yesterday's most expensive queries — the first place to look when the bill spikes.

create table d.events partition by date(event_ts) cluster by user_id, event_name as select * from staging.events: Partition on the time column you filter by; cluster on high-cardinality columns you filter or join on. Up to 4 cluster keys.
partition by range_bucket(customer_id, generate_array(0, 1000000, 10000)): Integer range partitioning for non-temporal keys.
alter table d.events set options (partition_expiration_days = 90);: Automatic retention — partitions older than the window are dropped for free.
select * from d.INFORMATION_SCHEMA.PARTITIONS where table_name = 'events';: Row counts and sizes per partition; verify your loads are landing where you expect.

select event, u.item from t, unnest(items) as u: UNNEST flattens an array to rows. The comma is an implicit CROSS JOIN — rows with empty arrays disappear.
left join unnest(items) as u: Keeps rows whose array is empty or NULL. Use this when counting parents.
array_agg(item order by ts limit 5): Roll rows back up into an array — ordered and capped inline.
select s.field1, s.field2 from t cross join unnest([t.some_struct]) s: Structs are accessed with dot notation; struct(a, b) constructs one on the fly.
select array_length(items), items[safe_offset(0)] from t;: safe_offset returns NULL instead of erroring past the end. offset() is 0-based, ordinal() is 1-based.

select * except (loaded_at, _raw) from t: All columns minus the noisy ones — the sane version of SELECT *.
select * replace (lower(email) as email) from t: Keep every column but transform one in place.
qualify row_number() over (partition by id order by updated_at desc) = 1: Dedup without a CTE — QUALIFY filters on window results directly.
safe_divide(revenue, users): Returns NULL on divide-by-zero instead of failing the query. Use in every ratio metric.
declare run_date date default current_date(); -- scripting: BigQuery scripting supports variables, loops, and IF — handy for admin jobs without external orchestration.

load data into d.events from files (format = 'PARQUET', uris = ['gs://bucket/events/*.parquet']): SQL-native load — no bq CLI needed. Free (uses shared slot pool), same as bq load.
create external table d.raw_events with connection `us.my-conn` options (format = 'PARQUET', uris = ['gs://bucket/events/*']): Query files in GCS without loading. BigLake connection adds row/column security over external data.
create snapshot table d.events_backup clone d.events;: Zero-copy snapshot for backups before risky backfills.
select * from d.events for system_time as of timestamp_sub(current_timestamp(), interval 2 hour);: Time travel: query the table as it was up to 7 days ago. First aid for accidental deletes.

create materialized view d.mv_daily as select date(ts) d, count(*) c from d.events group by 1;: Auto-refreshed and used transparently by the optimizer for matching queries. Limited SQL surface — no full joins on all editions.
create search index on d.logs (all columns);: Point lookups in huge text/log tables via search(t, 'needle') without scanning everything.
select * from d.events tablesample system (1 percent);: Cheap sampling for profiling — scans roughly 1 percent of blocks.

From DataLane — tutorials at/blog, practice SQL live in theplayground.