BigQuery Query Optimization: How to Cut Slot Time Without Touching Your Table Design

By Amit Yahalom
July 26, 2026 | 5 min read

Two analysts can run the exact same report against the exact same table and burn wildly different amounts of slot time. Same data, same warehouse, same reservation. The difference is almost never the infrastructure. It’s the SQL. 

Most BigQuery cost conversations jump straight to partitioning, clustering, or reservation sizing. Those matter, but they’re table- and account-level decisions. They don’t fix a query that self-joins a billion-row table, filters after a join instead of before it, or sorts intermediate results it never needed to support. 

Query optimization is a different discipline. It focuses on reducing costs and the amount of work the execution engine performs. 

This guide focuses specifically on that layer. Read on to learn: 

  • What exactly query optimization means
  • How to read a BigQuery execution plan 
  • Seven SQL techniques 
  • How BigQuery’s advanced runtime rewards clean queries 
  • How query-level optimizations scale

What Query Optimization Means in BigQuery (Not Table Design, Not Billing) 

BigQuery separates storage from compute, and every query you run consumes slots, the unit of computational capacity that executes your query’s stages. Query optimization means writing SQL that asks the execution engine to do less: scan fewer bytes, shuffle less data between stages, and avoid computational patterns that balloon output rows relative to input rows. 

This is distinct from schema design (how tables are partitioned and clustered) and distinct from billing strategy (on-demand versus reservations). 

A perfectly partitioned table can still get hammered by a badly written query – and a badly written query can still get somewhat cheaper on a well-tuned reservation. 

The query is the one variable every analyst and engineer touches daily, which makes it the highest-leverage place to start. 

How to Read a BigQuery Execution Plan Before Optimizing Anything

Before rewriting anything, look at what BigQuery actually did. Every completed query has an execution graph broken into stages, and each stage reports the numbers that tell you where time and money went: 

  • Slots consumed
  • Bytes shuffled
  • Wait time
  • Input rows versus output rows

You can pull this directly from INFORMATION_SCHEMA: 

SELECT
  job_id,
  query,
  total_slot_ms,
  total_bytes_processed,
  total_bytes_billed,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS elapsed_seconds
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND job_type = 'QUERY'
ORDER BY total_slot_ms DESC
LIMIT 25;

This query surfaces your heaviest jobs from the last 24 hours ranked by slot time, which is a better cost signal than elapsed time since slot time reflects actual compute consumed, not just wall-clock duration. 

From there, open the execution graph for any expensive job and check three things: 

  1. Wait time by stage. High wait time relative to other stages usually means slot contention, not a query problem. If it’s consistent across your heaviest queries, that’s a capacity conversation, not a SQL rewrite. 
  2. Output rows versus input rows per stage. A stage that produces dramatically more output rows than it consumes input is almost always a join generating a fan-out, often a self-join or a join on a low-selectivity key.  
  3. REPARTITION and COALESCE steps. These appear after a shuffle when BigQuery is rebalancing unevenly distributed data. Occasional appearances are normal. Frequent, expensive ones point to skewed join keys that deserve a second look. 

Reading the plan first means you’re optimizing the actual bottleneck instead of guessing what will help the most. 

7 SQL Techniques that Reduce BigQuery Slot Time

Once you know where the cost is coming from, these are the seven patterns most responsible for it, roughly in order of how often they show up in an execution plan review. 

1. Prune Columns and Rows Before Anything Else

SELECT * forces BigQuery to scan every column in the table, even the ones your query never uses. Because BigQuery is columnar, this is one of the most wasteful habits possible: you’re paying to read data you’ll immediately discard. 

-- Scans every column in the table
SELECT * FROM `project.dataset.events`;

-- Scans only what the query needs
SELECT event_id, event_timestamp, user_id
FROM `project.dataset.events`
WHERE event_date = '2026-06-01';

When you need columns but want to drop a few, SELECT * EXCEPT(column_name) is cleaner than listing every remaining column by hand, and it still limits the scan. 

2. Filter Before You Join, Not After

BigQuery can push some filters into a join automatically, but you shouldn’t rely on that as your primary strategy. Filter both sides of a join explicitly before the join executes rather than filtering on the joined result. 

-- Filters after the join processes the full table
SELECT o.order_id, o.revenue, c.region
FROM `project.dataset.orders` o
JOIN `project.dataset.customers` c ON o.customer_id = c.customer_id
WHERE o.order_date = '2026-06-01';

-- Filters before the join reduces what gets shuffled
WITH recent_orders AS (
  SELECT order_id, customer_id, revenue
  FROM `project.dataset.orders`
  WHERE order_date = '2026-06-01'
)
SELECT r.order_id, r.revenue, c.region
FROM recent_orders r
JOIN `project.dataset.customers` c ON r.customer_id = c.customer_id;

The second version shrinks the table before it even hits the join, which reduces the shuffle volume and the slot time spent on the join stage itself. 

3. Order Joins Largest to Smallest and Use Integer Keys

BigQuery’s optimizer handles a lot of join planning automatically, but a few best practices still make a measurable difference: 

  • Place the largest table first in the FROM clause, followed by progressive smaller tables
  • Join on INT64 keys instead of STRING keys wherever you have the choice, since integer comparisons are cheaper for the engine to evaluate
  • Avoid cross joins entirely unless you’ve pre-aggregated the data first, since a cross join’s worst case is the row count of the left table multiplied by the row count of the right table

4. Replace Self-Joins With Window Functions

Self-joins are commonly used to compute row-dependent relationships, like comparing a row to the previous for the same entity. The problem is that a self-join can multiply your output rows well beyond your input rows, which is expensive at any scale. A window function does the same job without the join. 

-- Self-join to get the previous order's revenue per customer
SELECT a.customer_id, a.order_date, a.revenue, b.revenue AS prev_revenue
FROM `project.dataset.orders` a
JOIN `project.dataset.orders` b
  ON a.customer_id = b.customer_id AND a.order_date > b.order_date;

-- Window function accomplishes the same result without the join
SELECT
  customer_id,
  order_date,
  revenue,
  LAG(revenue) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_revenue
FROM `project.dataset.orders`;

Anywhere you see a self-join, there’s a strong chance a window function with LAG, LEAD, or a ranking function replaces it directly. 

QUALIFY can often eliminate an additional subquery when filtering window function results. 

5. Materialize CTEs You Reference More Than Once

BigQuery doesn’t guarantee that a CTE will be materialized only once. If an expensive intermediate result is referenced repeatedly, consider materializing it into a temporary or permanent table instead of relying on repeated evaluation. 

If you reference the same CTE three times in a query, BigQuery computes it three times. If a subquery or a CTE is expensive and reused across multiple queries, materialize it into a table once and query the materialized version instead of recomputing it repeatedly. 

6. Move ORDER BY to the Outermost Query

Sorting is expensive, and BigQuery has to hold and compare data across the full result set to do it. Only use ORDER BY in your outermost query or inside a window function’s OVER clause. An ORDER BY buried in the middle of a query, before further filtering or aggregation happens downstream, forces BigQuery to sort data it hasn’t finished narrowing down yet. 

NOTE: ORDER BY inside window functions is an exception because it’s required to define the window frame. 

7. Use Approximate Functions When Precision Isn’t Required

Exact aggregation functions like COUNT(DISTINCT…) are expensive at scale, particularly on skewed data. If a dashboard metric or exploratory query doesn’t need exact precision, APPROX_COUNT_DISTINCT and related approximate functions scan meaningfully less data for a small, usually acceptable accuracy tradeoff. 

SELECT APPROX_COUNT_DISTINCT(user_id) AS approx_unique_users
FROM `project.dataset.events`
WHERE event_date = '2026-06-01';

Save exact counts for the cases where the last decimal point actually matters. 

How BigQuery’s Advanced Runtime Rewards Clean Queries

Google rolled out short query optimization as part of BigQuery’s advanced runtime, and it’s a genuinely different mechanism from the query-writing habits above. For eligible queries, typically shorter analytical queries with joins and aggregations, BigQuery skips its usual multi-stage distributed execution and processes the entire query in a single stage instead. That avoids the shuffle layer entirely, which is where a large share of slot time and wait time normally gets spent. 

BigQuery decides eligibility automatically based on: 

  • Data scan size
  • Filter selectivity
  • Data layout
  • Query structure

It also uses history-based optimization to learn from a query’s past executions over time. This runs by default now, with no code changes required on your end. 

The catch? Eligibility isn’t guaranteed. And it isn’t something you directly control from the SQL you write. What you can control is everything upstream of it: a query that already filters early, joins efficiently, and avoids unnecessary row inflation is far more likely to qualify for single-stage execution than a sprawling, multi-CTE query with late filtering. Advanced runtime rewards clean queries – but it doesn’t replace the need to write them. 

Query-Level Optimization Doesn’t Scale

Everything above works when a human is looking at a specific query. The harder problem is that most of the expensive queries hitting BigQuery today aren’t written by a human sitting down to optimize them. 

A dbt project might generate hundreds of models, each compiling into SQL that no one hand-tunes line by line. Airflow and Matillion pipelines schedule recurring jobs that were reasonable when the underlying tables were smaller and have quietly become expensive as data volume grew. AI agents and copilots are now generating ad hoc queries directly against BigQuery, often without any awareness of partition columns, join order, or slot cost at all. No data engineering team, however disciplined, can manually review every query plan across that volume of workload, especially when the queries themselves change on a weekly basis. 

That’s the real ceiling on query-level optimization: it scales with engineering time, and engineering time doesn’t scale with query volume. 

How Yuki Extends Query Optimization Across Every BigQuery Workload

Even well-written SQL still has to execute on the most cost-efficient pricing model. They don’t do anything for the ones you don’t, and in most BigQuery environments, that’s most of them. Yuki works underneath that layer, routing every BigQuery job, whether it’s hand-written analyst SQL, a dbt model, an Airflow task, or an AI-generated query, to the most cost-efficient compute path in real time. No query rewrites, no code changes, and no manual review queue required. 

That matters most exactly where hand-tuning breaks down: dbt pipelines with hundreds of models, AI-driven query spikes that show up without warning, and reservation capacity that’s either overcommitted or sitting idle while on-demand spend climbs elsewhere. 

Yuki’s BigQuery platform handles that routing continuously, with governance controls in place so platform teams keep visibility and control even as the optimization runs autonomously. 

If you want to see where your own BigQuery spend is being lost to query inefficiency, reservation waste, or both, Yuki’s free BigQuery cost analysis will show you exactly where before you commit to anything.

By Amit Yahalom
Product Manager passionate about turning complex challenges into products people truly love. I combine technical depth with strategic vision, always focused on creating lasting impact. At Yuki, I’m driven to shape tools that define the future of data.

Table of Contents

Free cost analysis

Take 5 minutes to learn how much money you can save on your Snowflake account.

By clicking Submit you’re confirming that you agree with our Terms and Conditions.

Follow us on LinkedIn

Related posts

Free cost analysis

Take 5 minutes to learn how much money you can save on your Snowflake account.

By clicking Submit you’re confirming that you agree with our Terms and Conditions.

Skip to content