# BigQuery Performance Optimization: 12 Techniques for Faster Queries and Lower Costs

> Boost BigQuery performance with 12 proven techniques. Optimize queries, reduce costs, and leverage partitioning, clustering, materialized views, and efficient SQL for faster results.

- Repository: [Google/skills](https://github.com/google/skills)
- Tags: performance
- Published: 2026-08-13

---

**Optimize BigQuery performance by combining table partitioning and clustering, leveraging materialized views and query cache, writing efficient SQL that avoids SELECT *, and managing slot allocation to match workload demands.**

BigQuery delivers serverless, petabyte-scale analytics, but maximizing speed while controlling costs requires deliberate architectural choices. This guide examines BigQuery performance optimization techniques extracted from the `google/skills` repository, including specific implementations found in the Google Cloud Solution Architecture and Workload Manager skill modules. Whether you are tuning query latency or reducing bytes processed, these patterns align with the Well-Architected Framework’s performance pillar.

## Partition and Cluster Tables

**Partitioning** reduces query cost by restricting scans to specific date or timestamp ranges rather than entire tables. According to the guidance in [`skills/cloud/google-cloud-solution-architecture/references/best-practices-guides.md`](https://github.com/google/skills/blob/main/skills/cloud/google-cloud-solution-architecture/references/best-practices-guides.md), you should implement **ingestion-time partitioning** or **column-based partitioning** on high-cardinality timestamp fields to minimize I/O.

**Clustering** further organizes data within partitions based on the values of designated columns. When you define clustering keys that match frequent filter or join predicates, BigQuery can skip irrelevant blocks of data during query execution.

For optimal results, combine both strategies. Partition by date to isolate time ranges, then cluster by user or event type to accelerate precise lookups.

## Leverage Materialized Views and Query Cache

**Materialized views** pre-compute expensive aggregations and automatically refresh, allowing repetitive queries to return instantly without reprocessing base tables. This technique is particularly effective for dashboard queries and routine reports that filter or aggregate large datasets.

The **query cache** stores results of identical queries for approximately 24 hours. As noted in [`skills/cloud/google-cloud-waf-performance-optimization/SKILL.md`](https://github.com/google/skills/blob/main/skills/cloud/google-cloud-waf-performance-optimization/SKILL.md), re-executing the same query text (including whitespace) retrieves results from cache at no additional cost. Avoid changing query strings unnecessarily or disabling the cache in job configurations if you rely on this optimization.

## Optimize SQL Query Patterns

Efficient SQL writing directly impacts BigQuery performance optimization. The repository emphasizes several specific patterns:

- **Project only necessary columns.** Explicitly list columns in `SELECT` statements; avoid `SELECT *` to reduce bytes processed.
- **Use approximate functions.** Replace `COUNT(DISTINCT ...)` with `APPROX_COUNT_DISTINCT` when exact precision is not required, trading negligible error for significant speed gains.
- **Apply `TABLESAMPLE` for exploration.** Use `TABLESAMPLE SYSTEM (X PERCENT)` to analyze representative data subsets without scanning full tables during development.
- **Prefer `JOIN` over correlated subqueries.** Restructure complex nested queries to minimize data shuffling between execution stages.
- **Materialize common `WITH` clauses.** If you reference a common table expression (CTE) multiple times, convert it to a temporary table or materialized view to prevent redundant execution.

## Manage Compute Resources and Data Locality

BigQuery uses **slots** to represent virtual CPU capacity. The [`skills/cloud/workload-manager-basics/references/general-best-practices.md`](https://github.com/google/skills/blob/main/skills/cloud/workload-manager-basics/references/general-best-practices.md) file recommends using **flex slots** for burst workloads and **reservations** for predictable demand to balance latency against cost. Over-provisioning wastes budget, while under-provisioning queues queries during peak times.

Avoid **cross-region data movement**, which introduces network latency and egress charges. Keep source tables, destination datasets, and export operations within the same regional location. The Workload Manager guidance specifically highlights using regional datasets for custom rule exports to maintain data locality.

## Implementation Examples

The following Python and SQL examples demonstrate combining partitioning, clustering, and slot management.

Create a table with daily partitioning on `event_timestamp` and clustering on frequently filtered columns:

```python
from google.cloud import bigquery

client = bigquery.Client()
table_id = "my_project.my_dataset.partitioned_clustered"

schema = [
    bigquery.SchemaField("event_timestamp", "TIMESTAMP"),
    bigquery.SchemaField("user_id", "STRING"),
    bigquery.SchemaField("event_type", "STRING"),
    bigquery.SchemaField("value", "FLOAT"),
]

table = bigquery.Table(table_id, schema=schema)
table.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field="event_timestamp",
)
table.clustering_fields = ["user_id", "event_type"]
client.create_table(table)

```

Use approximate distinct counts and benefit from automatic query caching:

```sql
SELECT
  APPROX_COUNT_DISTINCT(user_id) AS approx_users,
  COUNT(*) AS total_events
FROM `my_project.my_dataset.partitioned_clustered`
WHERE event_timestamp BETWEEN '2024-01-01' AND '2024-01-31';

```

Execute queries with controlled slot allocation and cost limits:

```python
from google.cloud import bigquery

client = bigquery.Client()
job_config = bigquery.QueryJobConfig(
    use_query_cache=True,
    priority="INTERACTIVE",
    maximum_bytes_billed=10 * (1024**3),  # 10 GB limit

)

query = """
SELECT
  user_id,
  SUM(value) AS total_spent
FROM `my_project.my_dataset.partitioned_clustered`
WHERE event_type = 'purchase'
GROUP BY user_id
ORDER BY total_spent DESC
LIMIT 100
"""
query_job = client.query(query, job_config=job_config)
for row in query_job:
    print(f"{row.user_id}: ${row.total_spent:.2f}")

```

## Summary

- **Partition tables** on timestamp fields and **cluster** on filter predicates to minimize scanned bytes, as documented in [`skills/cloud/google-cloud-solution-architecture/references/best-practices-guides.md`](https://github.com/google/skills/blob/main/skills/cloud/google-cloud-solution-architecture/references/best-practices-guides.md).
- **Implement materialized views** for repetitive aggregations and rely on the **query cache** to serve identical requests instantly.
- **Optimize SQL** by projecting specific columns, using approximate functions, and applying `TABLESAMPLE` for large-scale exploration.
- **Control slot allocation** through flex slots or reservations to match compute capacity with workload patterns.
- **Maintain data locality** within single regions to avoid latency and egress costs, following the guidance in [`skills/cloud/workload-manager-basics/references/general-best-practices.md`](https://github.com/google/skills/blob/main/skills/cloud/workload-manager-basics/references/general-best-practices.md).

## Frequently Asked Questions

### What is the difference between partitioning and clustering in BigQuery?

Partitioning divides a table into segments based on a time-unit column or ingestion time, allowing queries to scan only relevant date ranges. Clustering organizes data within each partition based on the values of up to four columns, sorting the data so filters on those columns can skip entire blocks. According to the `google/skills` repository, using both together provides maximal pruning capabilities for time-series workloads.

### How does BigQuery slot allocation affect performance?

Slots represent virtual CPU capacity used to execute queries. When demand exceeds your allocation, queries queue until resources become available. The repository recommends **flex slots** for handling unpredictable bursts and **reservations** for steady workloads to ensure consistent latency without over-provisioning.

### When should I use approximate aggregate functions?

Use approximate functions like `APPROX_COUNT_DISTINCT` when analyzing large datasets where exact precision is not critical. These functions trade a tiny margin of error for significantly faster execution and lower resource consumption, making them ideal for exploratory data analysis and real-time dashboards.

### How can I verify that my query is using the cache?

Check the query job metadata via the BigQuery console or API. If `cacheHit` returns `true`, BigQuery served the result from cache rather than reprocessing the data. To maximize cache hits, avoid altering query text, including comments or whitespace changes, and ensure you are querying within the same location as the original execution.