BigQuery separates storage from compute and charges by bytes processed rather than compute time. Its performance model is fundamentally different from traditional databases and other cloud warehouses — understanding how BigQuery plans and executes queries is the prerequisite for optimising them.
BigQuery separates compute and storage and charges for queries by bytes processed rather than compute time. This billing model means that query performance optimisation and cost reduction are the same problem: queries that process fewer bytes run faster and cost less. Understanding BigQuery's execution model is the prerequisite for optimising both.
How BigQuery Executes Queries
BigQuery uses a distributed query engine (Dremel) that dynamically allocates compute units (slots) to queries from a shared pool. On-demand pricing allocates slots automatically; reserved pricing provides a dedicated slot reservation that queries draw from.
The query execution steps:
**Table reads** — BigQuery reads only the columns referenced in the query from columnar storage. SELECT * forces a full column read; explicit column selection reduces bytes processed proportionally.
**Partition elimination** — BigQuery evaluates filter predicates against partition metadata before reading data. A query filtering on the partition column reads only matching partitions; all other partitions are skipped at zero cost. Partition elimination is the single highest-impact optimisation for queries that frequently filter on the same column.
**Column clustering** — within each partition, rows are sorted by the cluster columns. BigQuery uses the cluster column statistics to skip blocks that cannot contain matching values. Clustering is effective for high-cardinality columns queried with equality or range filters.
**Join execution** — BigQuery uses broadcast joins for small tables (under ~10 MB, broadcast to all workers) and hash joins for larger tables. The smaller table in a join should be on the right side of the join expression; BigQuery uses the right table for broadcast. If BigQuery cannot fit the right-side table in a broadcast, it shuffles both tables by join key — a shuffle is expensive for large tables.
Partitioning Strategy
BigQuery supports partitioning by date/datetime column, integer range, or ingestion time. Date partitioning is most common for time-series data.
**Partition column selection** — choose the column that appears most frequently in query filters. A table of customer orders partitioned by order_date means queries filtering by date period (last 30 days, current month) only read the relevant partitions. If the most common filter is customer_id rather than date, partitioning by customer_id (via integer range partitioning) is more appropriate.
**Partition granularity** — BigQuery supports day, month, and year partitioning. Finer granularity provides more precise partition elimination but creates more partitions to manage. Day partitioning is standard for most analytical use cases; month or year partitioning is appropriate for tables queried at coarser time ranges.
**Partition expiration** — BigQuery can automatically delete partitions older than a configured number of days. For data with a known retention requirement, partition expiration automates retention management without manual deletion.
**Require partition filters** — tables can be configured to reject queries that do not include a partition filter, preventing accidental full table scans. This is particularly valuable for large tables where a full scan is expensive.
Clustering for High-Cardinality Filters
Clustering sorts rows within each partition by the cluster columns. BigQuery stores statistics (min/max, distinct value counts) for cluster columns at the block level; queries filtering on cluster columns skip blocks that cannot contain matching values.
Clustering is beneficial when:
- Queries frequently filter or group on specific columns that are not the partition key
- The filter column has high cardinality (many distinct values) and queries typically filter to a small fraction of those values
- The column is not the partition key (a date column that is the partition key does not benefit from additional clustering on the same column)
A common effective configuration for a customer events table: partition by event_date, cluster by customer_id. Queries that filter by both date and customer_id benefit from both partition elimination (date) and clustering pruning (customer_id).
Slot Management and Workload Performance
BigQuery on-demand pricing allocates up to 2,000 slots per project but does not guarantee that number — during periods of high demand, queries may compete for slots with other projects in the regional pool. For predictable performance, reserved slots provide guaranteed compute capacity.
Slot consumption per query is visible in the INFORMATION_SCHEMA.JOBS view. The 'total_slot_ms' field records total slot-milliseconds consumed; dividing by job duration in milliseconds gives average slot consumption. Queries with high total_slot_ms and long duration are either resource-intensive (appropriate for their complexity) or poorly written (scanning more data than necessary).
The job_stages details in INFORMATION_SCHEMA.JOBS_BY_PROJECT provides per-stage slot consumption and records processed, enabling identification of which query stage is the bottleneck.
Cost Control
BigQuery costs are driven by bytes processed (on-demand) or slot consumption (reserved). The primary cost controls:
**Column selection** — the most impactful cost reduction. SELECT * on a 1 TB table processes 1 TB; SELECT column1, column2 on the same table processes only the two columns' storage. For wide tables, explicit column selection can reduce bytes processed by 90% or more.
**Partition and clustering** — as described above. Partition elimination is the primary structural cost control; clustering provides additional reduction within partitions.
**Materialised views** — for frequently run queries over large tables, materialised views pre-compute and cache the result. BigQuery automatically refreshes materialised views when source data changes (within about 5 minutes). Queries eligible to use the materialised view read from the cached result rather than the source table, reducing bytes processed to the size of the materialised view.
**BI Engine** — for dashboards with many concurrent users running the same queries, BI Engine provides an in-memory query accelerator that serves repeated dashboard queries from cache at zero bytes-processed cost. BI Engine is priced as a reservation of in-memory capacity; it is cost-effective when dashboard query volume is high.
**Cost attribution and monitoring** — use the INFORMATION_SCHEMA.JOBS view to attribute cost by user, dataset, and query pattern. Setting project-level budget alerts and dataset-level default billing project controls prevents unexpected cost spikes.
Our cloud engineering practice optimises BigQuery environments for organisations on Google Cloud — contact us to discuss BigQuery performance and cost management for your analytics environment.
A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.
Book a Call →