Memory hook: Partition prunes; cluster narrows; slots execute.
Must remember
BigQuery separates managed storage from query execution. Choose on-demand bytes-processed economics or capacity-based reservations/editions according to workload predictability and isolation needs. Reservations assign capacity; they do not remove the need to tune wasteful SQL. Separate interactive reporting from batch work when latency and priority differ.
Partition large tables on a useful date/time or supported range key. Filters must permit partition pruning. Clustering organizes blocks around selected columns and can reduce scanning within partitions. Avoid unnecessary SELECT *, repeated large joins and unbounded date ranges. Inspect the execution plan, scanned bytes, skew, shuffle and slot utilization before selecting a remedy. A LIMIT does not necessarily reduce bytes read.
Denormalized nested/repeated structures can reduce repeated joins for suitable analytical relationships; normalize where reuse and update correctness demand it. OLTP transaction design and analytical models optimize different access patterns. Materialized views precompute supported results with managed refresh; BI Engine accelerates suitable interactive analysis. Result caching depends on eligibility and unchanged inputs.
External/federated queries access data without fully loading it into native tables, but latency, supported operations, source load and network locality still matter. BigLake supports governed access across supported lake data. Use compatible formats, partition layout and regional placement.
For dashboards, Looker supplies governed business definitions and controlled access. Row policies restrict rows; column policy tags and masking address sensitive fields. An authorized view can expose a controlled projection without granting broad access to underlying datasets. Test as the consumer identity, not as an administrator.
Review details
A dry run estimates supported query processing without executing the query; a maximum-bytes-billed setting can reject an excessive on-demand query. Capacity/reservation management solves a different problem. Batch priority suits jobs that can tolerate scheduling delay; isolate business-critical interactive workloads appropriately.
SQL correctness before performance: check join multiplicity, null handling, aggregate grain and partition filters. WHERE acts before grouping; HAVING filters aggregates. A window function retains row detail while computing over its partition. A faster query with duplicated totals is still wrong.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Repeated daily time-range reporting | Partition on the relevant date and filter it. |
| Predictable isolated query capacity | Appropriate reservations and workload assignments. |
| Restricted consumer dataset | Authorized views and suitable row/column controls. |
Traps
- LIMIT is not a reliable query-cost cap.
- Clustering and partitioning help only when query patterns can exploit them.
Active recall
1. What does partition pruning avoid?
Reading irrelevant partitions.
2. What does clustering organize?
Storage blocks around selected column values.
3. Why inspect a query plan?
To find expensive scans, joins, shuffle, skew and execution bottlenecks.
4. When is federation useful?
When querying supported external data without full ingestion fits freshness, performance and governance constraints.
5. Why distinguish row policy and masking?
One restricts records; the other controls exposure of field values.