BigQuery Optimization Techniques

This document covers ways to optimize BigQuery queries. Faster queries use fewer resources, cost less, and fail less often.

Key Drivers of Performance

To optimize BigQuery query performance, understand the key factors affecting speed. Query completion time depends on how much data is read from storage, how that data is organized, how many stages your query requires, how parallelizable those stages are, how much data is processed at each stage, and how computationally expensive each of the stages is.

For example, a query reading three columns takes 50% longer than one reading two columns, since it reads 50% more data.

Measuring this difference precisely is challenging because BigQuery is a service; network overhead and service load vary. Running the query multiple times may be necessary to estimate speed accurately.

Queries with a group-by tend to be slower than those without, as the group-by adds an extra query stage.

Controlling Cost

Query cost depends on your pricing plan. BigQuery offers two plans:

• On-demand pricing (per TiB): charged for the number of bytes processed by each query.
• Capacity pricing (per slot-hour). charged for compute capacity used to run queries, measured in slots (virtual CPUs) over time.

In an on-demand (per query) pricing plan, the cost of a query is proportional to the amount of data processed by the query. To reduce cost in an on-demand pricing model, your queries should process less data. In general, reducing the amount of data scanned will also improve query speed.

In an are Capacity plan, the net cost of your query is quite aligned with the time taken for the query to complete.

Finding the most expensive queries

When trying to control costs, it can be helpful to create a short list of queries to focus on. You can do this by querying the INFORMATION_SCHEMA associated with a project to find the most expensive queries:

SELECT job_id, query, user_email, total_bytes_processed, total_slot_ms
FROM `some-project`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE EXTRACT(YEAR FROM creation_time) = 2024
ORDER BY total_bytes_processed DESC
LIMIT 5

The preceding query lists the five most expensive queries in 2024 in some project based on total_bytes_processed. If you are on capacity pricing, you might choose to order the queries based on total_slot_ms instead.

Optimize query computation

When you run a query, you can view the query plan in the Google Cloud console. You can also request execution details by using the INFORMATION_SCHEMA.JOBS*

The query plan includes details about query stages and steps. These details can help you identify ways to improve query performance. For example, if you notice a stage that writes a lot more output than other stages, it might mean that you need to filter earlier in the query.

To learn more about the query plan and see examples of how the query plan information can help you to improve query performance, see Get query performance insights. After addressing the query performance insights, you can further optimize your query by performing the following tasks:

• Reduce data that is to be processed
• Optimize query operations
• Avoid anti-SQL patterns

Reduce data processed

Avoid SELECT *

• Control projection by querying only the columns that you need.
• If you are experimenting with or exploring your data, you can use table preview options to view data at no charge and without affecting quotas. (In the Google Cloud console, on the table details page, click the Preview tab to sample the data.)
• Applying a LIMIT clause to a SELECT * query does not affect the amount of data read.
• use SELECT * EXCEPT to exclude one or more columns from the results to reduce the amount of data that is read by a query.

SELECT * EXCEPT (col1, col2, col5)
FROM `my_project.my_dataset.my_table`

If you do require queries against every column in a table, but only against a subset of data, consider Materializing results in a destination table and querying that table instead

Avoid excessive wildcard tables

When querying wildcard tables, you must use the most granular prefix.

SELECT *
FROM `my_project.my_dataset.my_table_*`
WHERE _TABLE_SUFFIX BETWEEN '10' AND '20'

Reduce data before using a JOIN

For queries with GROUP BY and JOIN, perform aggregation earlier in the query to reduce the amount of data processed. For example, the following query performs a JOIN on two large tables without any filtering beforehand:

-- Query A
WITH
  users_posts AS (
  SELECT *
  FROM `bigquery-public-data`.stackoverflow.comments AS c
  JOIN `bigquery-public-data`.stackoverflow.users AS u
  ON c.user_id = u.id)
SELECT user_id, ANY_VALUE(display_name) AS display_name, ANY_VALUE(reputation) AS reputation, COUNT(text) AS comments_count
FROM users_posts
GROUP BY user_id
ORDER BY comments_count DESC
LIMIT 20;

This query pre-aggregates the comment counts which reduces the amount of data read for the JOIN:

-- Query B
WITH
  comments AS (
  SELECT user_id, COUNT(text) AS comments_count
  FROM `bigquery-public-data`.stackoverflow.comments
  WHERE user_id IS NOT NULL
  GROUP BY user_id
  ORDER BY comments_count DESC
  LIMIT 20)
SELECT user_id, display_name, reputation, comments_count
FROM comments c
JOIN `bigquery-public-data`.stackoverflow.users AS u
ON c.user_id = u.id
ORDER BY comments_count DESC;

Note: WITH clauses with common table expressions (CTEs) are used for query readability, not performance. There is no guarantee that adding a WITH clause causes BigQuery to materialize temporary intermediate tables and reuse the temporary result for multiple references. The WITH clause might be evaluated multiple times within a query, depending on query optimizer decisions.

Use the WHERE clause

Use a WHERE clause to limit the amount of data a query returns. Whasden possible, use BOOL, INT64, FLOAT64, or DATE columns in the WHERE clause.

Operations on BOOL, INT64, FLOAT64, and DATE columns are typically faster than operations on STRING or BYTE columns

Use materialized views

Use materialized views to precompute the results of a query for increased performance and efficiency (A materialized view is a precomputed and incrementally updated result set based on a SQL query.)

CREATE MATERIALIZED VIEW `your_project.your_dataset.mv_java_questions_2024_12`
AS
SELECT id, title, body, owner_user_id, creation_date, tags, score
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date >= '2024-12-01' AND creation_date < '2025-01-01' 
  AND tags LIKE '%java%';

Use BI Engine

BigQuery BI Engine is a fast, in-memory analysis service that accelerates many SQL queries in BigQuery by intelligently caching the data you use most frequently. Consider adding a BI Engine reservation to the project where the queries are being computed. BigQuery BI Engine uses a vectorized query engine to accelerate the SELECT query performance.

BI Engine preferred tables let you limit BI Engine acceleration to a specified set of tables. Queries to all other tables use regular BigQuery slots.

Notes

• Use BI Engine for tables queried frequently.
• JSON type columns are unsupported and are not accelerated by BI Engine.
• Queries that access multiple tables are only accelerated if all tables are preferred tables.
• To use BI Engine, your organization must purchase BI Engine capacity by creating a BI Engine reservation with a supported edition

Optimize query operations

Avoid repeatedly transforming data

If you are using SQL to perform ETL operations, then avoid situations where you are repeatedly transforming the same data.

For example, if you are using SQL to trim strings or extract data by using regular expressions, it is more performant to materialize the transformed results in a destination table. Functions like regular expressions require additional computation. Querying the destination table without the added transformation overhead is much more efficient.

The provided example discusses the Stack Overflow public dataset, specifically the posts_questions table, which contains a column named tags. The main issue is that the data in the tags column is formatted as a single pipe-separated string, such as "python|pandas|dataframe". The objective is to enable your team to analyze trending tags by counting each individual tag effectively.

1. The “Bad” Approach (Repeated Transformation)

Every time an analyst wants to count tags, they force BigQuery to parse and split those strings on the fly for millions of rows.

-- ⚠️ EXPENSIVE: Repeatedly splitting strings and unnesting arrays
SELECT flat_tag, COUNT(*) as count
FROM `bigquery-public-data.stackoverflow.posts_questions`,
UNNEST(SPLIT(tags, '|')) as flat_tag -- <--- The Transformation Overhead
WHERE creation_date >= '2022-09-25'
GROUP BY 1
ORDER BY 2 DESC;

2. The “Best Practice” Approach (Materialize First)

You perform the heavy splitting operation once and save the clean, ready-to-use data in a new table.

-- Step 1: Materialize (The ETL Job)
-- Create a new, clean table where tags are already separated
CREATE OR REPLACE TABLE `my_project.my_dataset.clean_stackoverflow_tags` AS
SELECT id, title, creation_date, flat_tag -- This is now a simple string column
FROM `bigquery-public-data.stackoverflow.posts_questions`, 
UNNEST(SPLIT(tags, '|')) as flat_tag -- We do the heavy lifting HERE, only once.
WHERE creation_date >= '2022-09-25';

-- Step 2: Query the Optimized Table
-- ✅ EFFICIENT: No splitting, no parsing. Just counting.
SELECT flat_tag, COUNT(*) as count
FROM `my_project.my_dataset.clean_stackoverflow_tags`
GROUP BY 1
ORDER BY 2 DESC;

Avoid multiple evaluations of the same CTEs

When your query contains common table expressions (CTEs) that are used in multiple places in the query, they might end up being evaluated each time they are referenced. The query optimizer attempts to detect parts of the query that could be executed only once, but this might not always be possible. As a result, using a CTE might not help reduce internal query complexity and resource consumption.

You can store the result of a CTE in a scalar variable or a temporary table depending on the data that the CTE returns.

In this example, we define a CTE called python_2024. We then use it twice in the final SELECT statement (once in a subquery for views, once in a subquery for score).

The Risk: BigQuery often treats the CTE like a “saved snippet of text.” It pastes the code into the two subqueries and runs the scan twice.

-- ⚠️ POTENTIALLY INEFFICIENT
WITH python_2024 AS (
    -- This is a "Heavy" calculation (filtering millions of rows)
    SELECT id, view_count, score
    FROM `bigquery-public-data.stackoverflow.posts_questions`
    WHERE creation_date >= '2022-09-25'
      AND tags LIKE '%python%'
)

SELECT 
    (SELECT SUM(view_count) FROM python_2024) as total_views,
    (SELECT AVG(score) FROM python_2024) as avg_score;

What happens internally:

1. BigQuery executes python_2024 to find the sum.
2. BigQuery executes python_2024 again to find the average.
3. You pay for reading the data twice (and wait longer).

To force BigQuery to calculate the result exactly once, we explicitly save it to a Temporary Table.

-- Step 1: Create a temporary table (Lasts only for this session)
-- This runs the heavy scan EXACTLY ONE time.
CREATE TEMP TABLE temp_python_data AS
SELECT id, view_count, score
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date >= '2022-09-25'
  AND tags LIKE '%python%';

-- Step 2: Query the temp table multiple times
-- These queries are instant because they read the tiny temp table.
SELECT 
    (SELECT SUM(view_count) FROM temp_python_data) as total_views,
    (SELECT AVG(score) FROM temp_python_data) as avg_score;

Avoid repeated joins and subqueries

Instead of repeatedly joining the data, it might be more performant for you to use nested repeated data to represent the relationships. Nested repeated data saves you the performance impact of the communication bandwidth that a join requires. It also saves you the I/O costs that you incur by repeatedly reading and writing the same data.

Get Jawad Zaarour’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

You can see the performance difference in queries that use nested fields as compared to those that don’t by following the steps in this section.

1. Create a table based on the bigquery-public-data.stackoverflow.comments public dataset:

CREATE OR REPLACE TABLE `PROJECT.DATASET.stackoverflow`
AS (
SELECT user_id, post_id, creation_date
FROM `bigquery-public-data.stackoverflow.comments`
);

2. Using the stackoverflow table, run the following query to see the earliest comment for each user:

-- Query A
SELECT
  user_id,
  ARRAY_AGG(STRUCT(post_id, creation_date AS earliest_comment) ORDER BY creation_date ASC LIMIT 1)[OFFSET(0)].*
FROM `PROJECT.DATASET.stackoverflow`
GROUP BY user_id
ORDER BY user_id ASC;

3. Create a second table with identical data that creates a comments field using a STRUCT type to store the post_id and creation_date data, instead of two individual fields:

CREATE OR REPLACE TABLE `PROJECT.DATASET.stackoverflow_nested`
AS (
SELECT
  user_id,
  ARRAY_AGG(STRUCT(post_id, creation_date) ORDER BY creation_date ASC) AS comments
FROM `bigquery-public-data.stackoverflow.comments`
GROUP BY user_id);

4. Using the stackoverflow_nested table, run the following query to see the earliest comment for each user:

-- Query B
SELECT
  user_id,
  (SELECT AS STRUCT post_id, creation_date as earliest_comment FROM UNNEST(comments) ORDER BY creation_date ASC LIMIT 1).*
FROM `PROJECT.DATASET.stackoverflow_nested`
ORDER BY user_id ASC;

Similarly, repeating the same subqueries affects performance through repetitive query processing. If you are using the same subqueries in multiple queries, consider materializing the subquery results in a table. Then consume the materialized data in your queries.

Optimize your join patterns

When you create a query by using a JOIN clause, consider the order in which you are merging the data. As a best practice, place the table with the largest number of rows first, followed by the table with the fewest rows, and then place the remaining tables by decreasing size.

The Rule: Large on Left, Small on Right

FROM Table_A (Left)
JOIN Table_B (Right)

Optimize the ORDER BY clause

Use ORDER BY in the outermost query or within window clauses. Push complex operations to the end of the query. Placing an ORDER BY clause in the middle of a query greatly impacts performance unless it is being used in a window function.

If you are ordering a very large number of values, use a window function, and limit data before calling the window function. For example, the following query lists the ten oldest Stack Overflow users and their ranking, with the oldest account being ranked lowest:

-- Query A
SELECT id, reputation, creation_date, DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM bigquery-public-data.stackoverflow.users
ORDER BY user_rank ASC
LIMIT 10;

Instead, you should limit the dataset before computing the window function in order to improve performance:

-- Query B
WITH users AS (
  SELECT id, reputation, creation_date,
  FROM bigquery-public-data.stackoverflow.users
  ORDER BY creation_date ASC
  LIMIT 10)
SELECT id, reputation, creation_date, DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM users
ORDER BY user_rank;

Split complex queries into smaller ones

Complex queries, REGEX functions, and layered subqueries or joins can be slow and resource intensive to run. Trying to fit all computations in one huge SELECT statement, for example to make it a view, is sometimes an antipattern, and it can result in a slow, resource-intensive query. In extreme cases, the internal query plan becomes so complex that BigQuery is unable to execute it.

Splitting up a complex query allows for materializing intermediate results in variables or temporary tables. You can then use these intermediate results in other parts of the query. It is increasingly useful when these results are needed in more than one place of the query.

Use INT64 data types in joins

Use INT64 data types in joins instead of STRING data types to reduce cost and improve comparison performance.

What if my ID is a String (like a UUID)?

The Solution: Convert the String to a Number using FARM_FINGERPRINT().

FARM_FINGERPRINT: Computes the fingerprint of the STRING or BYTES input using the Fingerprint64 function from the open-source FarmHash library. The output of this function for a particular input will never change.

Return type: INT64

Using Approximate Aggregation Functions

BigQuery provides fast, low-memory approximations of aggregate functions. Instead of using COUNT(DISTINCT …), we can use APPROX_COUNT_DISTINCT on large data streams when a small statistical uncertainty in the result is tolerable.

For example, you can find the number of unique GitHub repositories by using:

SELECT
COUNT(DISTINCT repo_name) AS num_repos
FROM `bigquery-public-data`.github_repos.commits, UNNEST(repo_name) AS repo_name

using APPROX_COUNT_DISTINCT:

SELECT
APPROX_COUNT_DISTINCT(repo_name) AS num_repos
FROM `bigquery-public-data`.github_repos.commits, UNNEST(repo_name) AS repo_name

The approximate algorithm is much more efficient than the exact algorithm only on large datasets and is recommended in use cases for which errors of approximately 1% are tolerable. Before using the approximate function, always measure on your use case!

Other available approximate functions include APPROX_QUANTILES to compute percentiles, APPROX_TOP_COUNT to find the top elements, and APPROX_TOP_SUM to compute top elements based on the sum of an element.

Optimize string comparison

In BigQuery, you can use the REGEXP_CONTAINS function or the LIKE operator to compare strings. REGEXP_CONTAINS provides more functionality, but also has a slower execution time. Using LIKE instead of REGEXP_CONTAINS is faster, particularly if you don't need the full power of regular expressions that REGEXP_CONTAINS provides, for example wildcard matching.

Consider the following use of the REGEXP_CONTAINS function:

SELECT dim1
FROM `dataset.table1`
WHERE REGEXP_CONTAINS(dim1, '.*test.*');

You can optimize this query as follows:

SELECT dim1
FROM `dataset.table`
WHERE dim1 LIKE '%test%';

Avoid anti-SQL patterns

Avoid self joins

Self-joins happen when a table is joined with itself. Although BigQuery supports selfjoins, they can lead to performance degradation if the table being joined with itself is very large. In many cases, you can avoid the self-join by taking advantage of SQL features such as aggregation and window functions.

If you find yourself writing FROM Table A JOIN Table A, stop. Ask yourself: "Can I do this with LAG(), LEAD(), or RANK()?" The answer is almost always yes.

Self join example (Anti-Pattern):

-- Find the value of count from exactly 2 days ago for each row, based on the type.
WITH events AS (
  SELECT DATE('2019-06-08') AS day, 'a' AS type, 1 AS count
  UNION ALL SELECT '2019-06-09', 'a', 2
  UNION ALL SELECT '2019-06-10', 'a', 3
  UNION ALL SELECT '2019-06-07', 'b', 4
  UNION ALL SELECT '2019-06-09', 'b', 5
)
SELECT e1.type, e1.day, e1.count, COALESCE(e2.count, 0) AS prev_count
FROM events e1
LEFT JOIN events e2 ON e1.type = e2.type AND e1.day = DATE_ADD(e2.day, INTERVAL 2 DAY)
ORDER BY 1, 2

Using window function:

WITH events AS (
  SELECT DATE('2019-06-08') AS day, 'a' AS type, 1 AS count
  UNION ALL SELECT '2019-06-09', 'a', 2
  UNION ALL SELECT '2019-06-10', 'a', 3
  UNION ALL SELECT '2019-06-07', 'b', 4
  UNION ALL SELECT '2019-06-09', 'b', 5
)
SELECT e.type, e.day, e.count, IFNULL(FIRST_VALUE(e.count) OVER (win), 0) prev_count
FROM events e
WINDOW win AS (PARTITION BY type ORDER BY UNIX_DATE(e.day) RANGE BETWEEN 2 PRECEDING AND 2 PRECEDING)

Avoid cross joins

Cross joins are queries where each row from the first table is joined to every row in the second table, with non-unique keys on both sides. The worst case output is the number of rows in the left table multiplied by the number of rows in the right table. In extreme cases, the query might not finish.

To avoid performance issues associated with joins that generate more outputs than inputs:

• Use a GROUP BY clause to pre-aggregate the data.
• Use a window function

Use alias names for similarly named columns

Aliases help to identify which columns and tables are referenced in addition to your initial reference of the column. Using aliases can help you understand and address problems in your SQL query, including finding the columns that are used in subqueries.

Optimize storage for query performance

Partition table data

A partitioned table in BigQuery physically divides a large table into partitions by specifying a partition column (timestamp or integer ranges). By dividing a large table into smaller partitions, you can improve query performance and control costs by reducing the number of bytes read by a query.

The examples below use the bigquery-public-data.samples.natality dataset. This dataset describes all United States births registered in the 50 States, the District of Columbia, and New York City from 1969 to 2008.

-- create unpartitioned table
CREATE OR REPLACE TABLE `project-id.dataset.natality_unpart`
AS
SELECT *
FROM `bigquery-public-data.samples.natality`

-- create partitioned table
CREATE OR REPLACE TABLE `project-id.dataset.natality_partitioned`
PARTITION BY RANGE_BUCKET(year, GENERATE_ARRAY(1969, 2008, 1))
AS
SELECT *
FROM `bigquery-public-data.samples.natality`

Integer range partitioning: You can partition a table based on ranges of values in a specific INTEGER column.

PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 100, 10))

The table is partitioned on the customer_id column into ranges of interval 10. The values 0 to 9 go into one partition, values 10 to 19 go into the next partition, etc., up to 99. Values outside this range go into a partition named __UNPARTITIONED__. Any rows where customer_id is NULL go into a partition named __NULL__.

Time-unit column partitioning: Also, you can partition a table on a DATE,TIMESTAMP, or DATETIME column in the table. When you write data to the table, BigQuery automatically puts the data into the correct partition, based on the values in the column.

For TIMESTAMP and DATETIME columns, the partitions can have either hourly, daily, monthly, or yearly granularity. For DATE columns, the partitions can have daily, monthly, or yearly granularity. Partitions boundaries are based on UTC time.

Clustering table data

Clustered tables in BigQuery improve query performance and reduce compute costs by sorting the data based on the contents of one or more columns (up to four). When you query a clustered table and apply a filter on the clustered columns, BigQuery uses the sorted blocks to skip reading data that doesn’t match the filter, a process known as block pruning.

While partitioning divides data into strict physical boundaries, clustering organizes the data within those boundaries (or across the entire table if it isn’t partitioned). Clustering is especially effective for columns with a high number of distinct values (high cardinality), such as user IDs, transaction IDs, or tags.

Key considerations for clustering:

• Column Order Matters: The order of the clustered columns dictates the sort order of the data. To maximize performance, your WHERE clauses should filter on the clustered columns starting from the first column in the list.
• High Cardinality: Use clustering for columns with many distinct values where partitioning would exceed BigQuery’s limits (partitioning is typically limited to 4,000 partitions per table).
• Combined with Partitioning: You can combine partitioning and clustering in the same table. BigQuery will first partition the data, and then cluster the data within each individual partition.

source: https://docs.cloud.google.com/bigquery/docs/clustered-tables

This example creates a table that is both partitioned by a date column and clustered by two high-cardinality string columns.

CREATE OR REPLACE TABLE `project-id.dataset.sales_data_clustered`
PARTITION BY DATE(transaction_timestamp)
CLUSTER BY store_id, customer_id
AS
SELECT *
FROM `project-id.dataset.raw_sales_data`

When a user queries this table filtering by both transaction_timestamp and store_id, BigQuery first prunes irrelevant date partitions. Then, it uses the store_id cluster to scan only the specific storage blocks containing that store’s data within the valid dates, drastically reducing the total bytes processed.

source: https://docs.cloud.google.com/bigquery/docs/partitioned-tables

References

• https://docs.cloud.google.com/bigquery/docs/best-practices-performance-compute
• https://cloud.google.com/bigquery/pricing?hl=en#on-demand-compute-pricing
• https://cloud.google.com/bigquery/pricing?hl=en#capacity-compute-pricing
• https://docs.cloud.google.com/bigquery/docs/best-practices-performance-functions
• Google BigQuery: The Definitive Guide