10.1 BigQuery SQL Tuning and Performance Best Practices

Google Cloud Professional Data Engineer Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

0.1 About the Google Cloud Professional Data Engineer Exam 0.2 Study Strategy & Cloud Data Engineering Methodology

2 Chapter 1: Designing for Security and Compliance

1.1 IAM Roles, Service Accounts, and Least Privilege for Data 1.2 Data Encryption, Key Management, and Privacy with Cloud DLP 1.3 Compliance, Data Sovereignty, and Geographic Placement 1.4 Multi-Environment Architecture: Development, Staging, and Production

3 Chapter 2: Designing for Reliability and Resilience

2.1 High Availability, Disaster Recovery, and Failover Patterns 2.2 Fault Tolerance and ACID Guarantees in Distributed Systems 2.3 Data Validation, Cleansing, and Quality Assurance with Dataform 2.4 AI-Assisted Data Preparation and LLM Query Generation

4 Chapter 3: Architecture Patterns and Flexibility

3.1 Modern Data Architectures: Data Lake, Warehouse, Lakehouse, and Mesh 3.2 Data Migration Strategies and Hybrid/Multi-Cloud Integration 3.3 Database Migration Service and Heterogeneous Database Migrations

5 Chapter 4: Ingestion Architectures: Pub/Sub and Streaming Sources

4.1 Cloud Pub/Sub Core Architecture and Ingestion 4.2 Pub/Sub Advanced Patterns: Dead-Lettering, Ordering, and Schemas 4.3 Change Data Capture (CDC) with Datastream and Batch Ingestion

6 Chapter 5: Stream and Batch Processing with Apache Beam and Cloud Dataflow

5.1 Apache Beam Core Concepts and Pipeline Design 5.2 Advanced Windowing, Watermarks, and Triggers 5.3 Dataflow Pipeline Optimization, Scaling, and Templates

7 Chapter 6: Data Transformation with Dataproc, Data Fusion, and Workflow Orchestration

6.1 Apache Spark and Hadoop with Cloud Dataproc 6.2 Visual Data Pipelines with Cloud Data Fusion 6.3 Workflow Orchestration with Cloud Composer 6.4 AI Data Enrichment inside Data Pipelines

8 Chapter 7: Storage Service Selection and Cloud Storage Architecture

7.1 Google Cloud Storage Service Selection Decision Matrix 7.2 Cloud Storage Architecture, Classes, and Lifecycle Management

9 Chapter 8: Deep Dive into Google BigQuery Storage

8.1 BigQuery Storage Architecture and Data Organization 8.2 Partitioning and Clustering for Performance and Cost 8.3 BigLake and Hybrid Querying across Clouds 8.4 Data Warehouse Modeling: Star Schemas, Denormalization, and Slowly Changing Dimensions

10 Chapter 9: Operational and NoSQL Databases: Bigtable and Cloud Spanner

9.1 Cloud Bigtable Architecture and Row Key Design 9.2 Cloud Bigtable Schema Management and Performance Tuning 9.3 Cloud Spanner: Globally Scalable Relational Database

11 Chapter 10: BigQuery Query Performance and Optimization

10.1 BigQuery SQL Tuning and Performance Best Practices 10.2 BigQuery BI Engine and Materialized Views 10.3 Data Sharing and Analytics Hub 10.4 Preparing Data for Visualization and BI Tool Connectivity

12 Chapter 11: Machine Learning Readiness and Data Governance

11.1 Machine Learning with BigQuery ML 11.2 Feature Engineering and Data Preparation for AI/ML 11.3 Data Governance, Cataloging, and Discovery with Dataplex 11.4 Unstructured Data, Embeddings, and Retrieval-Augmented Generation

13 Chapter 12: Site Reliability Engineering and Monitoring for Data Systems

12.1 SRE Principles for Data: SLIs, SLOs, and SLAs 12.2 Observability: Cloud Monitoring, Cloud Logging, and Alerting 12.3 Troubleshooting Data Pipelines and Workload Failures

14 Chapter 13: Workload Automation, CI/CD, and Cost Optimization

13.1 CI/CD and Version Control for Data Pipelines 13.2 BigQuery Cost Management and Capacity Planning 13.3 Holistic Cost Optimization across GCP Data Services 13.4 Query Workload Management: Interactive vs. Batch Jobs, Queues, and Quotas

10.1 BigQuery SQL Tuning and Performance Best Practices

Key Takeaways Explain This Topic AI

BigQuery decomposes SQL queries into multi-stage Directed Acyclic Graphs (DAGs) executed across Dremel tree tiers (root coordinator, intermediate mixers, and leaf worker slots) brokered by an independent distributed in-memory shuffle layer.

SQL cost and latency optimization relies on projection pruning (never using SELECT *) and early predicate pushdown to minimize columnar Capacitor bytes transferred across the petabit Jupiter bisection network.

Joins execute with maximum efficiency when the largest table is placed on the left (FROM) and the smaller table on the right (JOIN), enabling the query planner to broadcast the small table as an in-memory hash map across all worker slots.

Severe data skew caused by high-frequency join keys produces worker slot stragglers; mitigating hot keys via synthetic salting distributes saturated values evenly across parallel worker slots to prevent memory exhaustion.

Query execution graphs provide critical diagnostic indicators: massive disparities between maximum and average slot compute time flag skew, while 'Shuffle Spill to Disk' indicates slot RAM exhaustion that severely degrades performance.

Last updated: September 2026

10.1 BigQuery SQL Tuning and Performance Best Practices

Important

For the Google Cloud Professional Data Engineer exam, query performance optimization is a high-weight topic that directly influences both computational speed and operational costs. Optimization dictates whether queries complete in sub-seconds or minutes, and directly controls expenditure under BigQuery on-demand analysis billing ($6.25 per TB scanned in most regions) or capacity-based slot commitments (BigQuery Editions).

Google BigQuery is an enterprise-scale, distributed serverless cloud data warehouse designed to analyze multi-petabyte datasets with massive parallelism. While BigQuery automatically provisions compute resources, distributes workloads, and parallelizes execution, poorly structured SQL queries can rapidly exhaust slot capacity, trigger catastrophic memory spills, cause worker stragglers, and generate exorbitant on-demand query bills.

To build performant, cost-effective data pipelines and analytical dashboards, data engineers must understand the underlying physical execution architecture of BigQuery and systematically apply deterministic query optimization principles.

BigQuery Query Execution Architecture

When a client submits a SQL query to BigQuery, the system does not execute the statement as a monolithic process on a single virtual machine. Instead, BigQuery compiles the declarative SQL statement into a distributed Directed Acyclic Graph (DAG) composed of discrete execution stages. +-------------------------------------------------------------------------+

• | Dremel Master Query Coordinator |
• | [Query Parsing] -> [Logical Plan] -> [Optimized Physical DAG] |
• +-------------------------------------------------------------------------+
• |
• +----------------------------+----------------------------+
• | |
• v v
• +-------------------------------+ +-------------------------------+
• | Stage 00: Input Fact Scan | | Stage 01: Dimension Scan |
• | • Leaf Slots read Colossus | | • Leaf Slots read Colossus |
• | • Column Pruning & Filters | | • Broadcast Hash Table Gen |
• +-------------------------------+ +-------------------------------+
• | |
• +----------------------------+----------------------------+
• |
• v
• === Dynamic Distributed In-Memory Shuffle Architecture ===
• |
• v
• +-------------------------------------------------------------------------+
• | Stage 02: Join & Aggregation Worker Slots |
• | • Workers evaluate Broadcast Hash Probe & Compute Partial SUM/COUNT |
• +-------------------------------------------------------------------------+
• |
• v
• +-------------------------------------------------------------------------+
• | Stage 03: Output Serialization & Result Assembly |
• | • Root Slot gathers final partition chunks, applies ORDER BY LIMIT |
• +-------------------------------------------------------------------------+
• 1. The Dremel Multi-Tier Serving Tree

BigQuery's execution engine is built on Google's Dremel technology, which organizes compute resources into a hierarchical serving tree:

Root Server (Coordinator): The coordinator receives incoming SQL statements, verifies IAM permissions, validates table schemas, parses syntax, applies semantic checks, and invokes the cost-based query optimizer. The optimizer rewrites relational algebra into an optimized physical DAG, determining join algorithms, predicate pushdowns, and initial slot allocations.

Intermediate Mixers: Intermediate mixer servers act as distributed dispatchers and aggregators. They coordinate parallel task execution across execution stages, aggregate partial results from downstream worker nodes, and dynamically rebalance tasks when runtime data volumes deviate from static compiler estimates.

Leaf Workers (Slots): Leaf workers execute the physical compute tasks. They communicate across the petabit Jupiter network fabric to read columnar Capacitor storage blocks from Colossus, evaluate filter expressions, project requested columns, decompress dictionary encodings, and perform partial aggregations.

2. Borg Slot Allocation and Dynamic Scheduling

A slot is BigQuery's virtual unit of computational capacity, comprising dedicated vCPU cores, working RAM, and network bandwidth provisioned inside Google Borg containers. Slot scheduling operates across two primary pricing models:

On-Demand Billing: Queries draw dynamically from a shared multi-tenant pool of up to 2,000 concurrent slots per project. BigQuery scales slot allocation up or down automatically based on query complexity and data volume.

Capacity-Based Commitments (BigQuery Editions): Organizations purchase dedicated slot reservations under Standard, Enterprise, or Enterprise Plus editions. Workloads run within configured baseline slot pools and can autoscale up to customer-defined maximum slot ceilings, preventing runaway compute costs while guaranteeing resource availability for mission-critical jobs.

3. Distributed Dynamic Shuffle

In traditional distributed architectures (such as Apache Spark, Hadoop MapReduce, or legacy MPP databases), intermediate data reshuffling between query stages is written to local worker disks or streamed directly between worker nodes. If a worker node encounters network congestion, runs out of disk space, or crashes, the entire query pipeline stalls or fails.

BigQuery decouples compute slots from intermediate data movement using Distributed Dynamic Shuffle :

Dynamic Shuffle is an independent, Google-managed in-memory storage cluster that brokers data transfer between execution stages.

Worker slots in Stage N N N serialize and write their intermediate output records directly into the shuffle layer in memory.

The query coordinator inspects the actual data volume and runtime statistics residing in the shuffle layer, dynamically resizing the number of worker slots required for Stage N + 1 N+1 N + 1 .

This architecture isolates worker failures, eliminates peer-to-peer slot communication bottlenecks, and allows BigQuery to adapt execution topologies dynamically mid-query.

SQL Cost and Speed Optimizations

Optimizing BigQuery SQL queries requires minimizing two primary physical metrics: bytes read from Colossus storage (which dictates on-demand billing and storage I/O wait) and slot CPU time (which dictates query latency and capacity reservation consumption).

1. Projection Pruning: Eliminating SELECT *

Because BigQuery stores tables in Capacitor columnar format on Colossus, queries are billed and throttled strictly based on the total byte volume of the columns read across the Jupiter network: -- ANTI-PATTERN: Scans every column across the entire table

• SELECT *
• FROM `project.dataset.telemetry_events`
• WHERE event_date = '2026-09-14';

• - RECOMMENDED: Scans strictly the required columns
• SELECT device_id, event_type, payload_value
• FROM `project.dataset.telemetry_events`
• WHERE event_date = '2026-09-14';

If telemetry_events contains 80 columns totaling 10 TB of storage, but device_id , event_type , and payload_value account for only 50 GB, selecting only the necessary columns slashes query costs and memory overhead by 99.5%.

Tip

The LIMIT Clause Trap: Applying LIMIT 10 to a query containing SELECT * does not reduce the number of bytes scanned or the cost of the query. BigQuery reads the complete columnar data blocks for all columns from Colossus before applying the LIMIT filter at the final output stage. The only mechanisms that reduce scanned bytes are column projection pruning, partition pruning, and cluster block skipping.

Semi-Structured Data: Structs vs Flat Columns

Capacitor natively handles nested and repeated data ( STRUCT and ARRAY types) by shredding fields into individual columnar paths. When querying a struct, referencing a specific nested attribute (e.g., SELECT user.address.zipcode ) scans only the data blocks for that nested field, preserving columnar pruning efficiency without requiring schema denormalization.

2. Early Predicate Pushdown and Filtering

BigQuery's query planner attempts to push WHERE filters as close to the Colossus storage layer as possible (predicate pushdown). However, certain SQL constructs prevent the optimizer from pruning records early:

Applying scalar functions or User-Defined Functions (UDFs) to columns in the WHERE clause (e.g., WHERE LOWER(status) = 'active' or WHERE CAST(order_id AS STRING) = '100' ) prevents BigQuery from leveraging block header metadata, forcing full columnar scans.

Filtering records after a JOIN in an outer query block can force millions of unnecessary records through the distributed shuffle layer. -- ANTI-PATTERN: Joins full tables, then filters post-join

• SELECT o.order_id, c.customer_name, o.total_amount
• FROM `project.dataset.orders` o
• JOIN `project.dataset.customers` c ON o.customer_id = c.customer_id
• WHERE o.order_status = 'COMPLETED'
• AND o.order_date >= '2026-09-01';

• - RECOMMENDED: Filter inline or ensure partition/cluster predicates apply immediately
• SELECT o.order_id, c.customer_name, o.total_amount
• FROM (
• SELECT order_id, customer_id, total_amount
• FROM `project.dataset.orders`
• WHERE order_status = 'COMPLETED'
• AND order_date >= '2026-09-01'
• ) o
• JOIN `project.dataset.customers` c ON o.customer_id = c.customer_id;

Filtering early limits the intermediate record count pushed into the distributed shuffle, reducing slot memory pressure and avoiding shuffle network serialization.

Join Optimization Mechanics

Joins represent the most resource-intensive operations in distributed databases. BigQuery evaluates joins using two primary physical strategies:

1. Broadcast Hash Join

When a large table is joined with a small dimension table (typically under a few hundred megabytes to several gigabytes):

BigQuery reads the small table into memory, constructs an in-memory hash table, and broadcasts this hash table to every worker slot processing the large table.

Each worker slot reads its assigned slice of the large table and probes the local in-memory hash table in O ( 1 ) O(1) O ( 1 ) time.

Performance Impact: The large table is never shuffled across the network. Only the small table is broadcast, yielding near-linear scaling and minimal execution latency.

Join Ordering Best Practice: Place the larger table first (left side, after FROM ) and the smaller table second (right side, after JOIN ). While BigQuery's cost-based query optimizer can often reorder joins automatically, complex multi-table queries, subqueries, and non-trivial join conditions can inhibit optimizer reordering. Structuring SQL with FROM large_table JOIN small_table provides deterministic guidance to the planner.

2. Distributed Hash Join (Shuffle Join)

When two large tables are joined, neither table fits into worker memory:

BigQuery hashes the join keys of both tables and shuffles both datasets across the distributed shuffle layer, routing rows with identical join keys to the same worker slot.

Both tables consume substantial shuffle bandwidth and slot memory.

3. Avoiding Cartesian Products ( CROSS JOIN )

A CROSS JOIN combines every row of Table A with every row of Table B, producing M × N M \times N M × N output rows:

If Table A has 1,000,000 rows and Table B has 1,000,000 rows, the join emits 1 , 000 , 000 , 000 , 000 1,000,000,000,000 1 , 000 , 000 , 000 , 000 (1 trillion) records.

This causes catastrophic slot memory saturation, massive shuffle spill, and eventual query termination with Resources exceeded during query execution: The query could not be executed in the allotted memory .

Remediation: Always include explicit join conditions ( ON a.key = b.key ). If cross-joining is required to expand arrays, use CROSS JOIN UNNEST(array_column) or the comma syntax , UNNEST(array_column) , which limits the expansion to the nested elements within each single parent record.

Managing Data Skew and Hot Keys

Distributed joins rely on hash partitioning to assign rows to worker slots. When a dataset contains extreme key imbalance—such as a null value, placeholder ( default_user ), or a dominant corporate customer accounting for 50% of all transactions—a severe problem arises called data skew .

The Straggler Effect

When joining skewed data, 99% of worker slots process their evenly balanced keys in 5 seconds and go idle. Meanwhile, the single worker slot assigned to the dominant "hot key" receives billions of records. That slot's memory saturates, its CPU hits 100%, and the entire query stage hangs waiting for this single straggler to finish. Slot 01: [Key: 'cust_01'] ===> 5,000 rows ===> Finished in 3s [IDLE]

• Slot 02: [Key: 'cust_02'] ===> 6,200 rows ===> Finished in 4s [IDLE]
• Slot 03: [Key: 'cust_03'] ===> 4,800 rows ===> Finished in 3s [IDLE]
• Slot 04: [Key: 'cust_NULL'] ===> 500,000,000 rows ===> RUNNING (42 mins) [STRAGGLER!]
• Salting Techniques for Skew Mitigation

To eliminate join stragglers, data engineers apply salting —artificially distributing the skewed key across multiple synthetic sub-keys.

Suppose orders has a severe skew on customer_id = 0 (guest checkouts): -- Salting the large skewed table: appending a random integer between 0 and 9

• WITH salted_orders AS (
• SELECT
• order_id,
• order_amount,
• customer_id,
• IF(customer_id = 0,
• CONCAT(CAST(customer_id AS STRING), '_', CAST(MOD(FARM_FINGERPRINT(order_id), 10) AS STRING)),
• CAST(customer_id AS STRING)) AS join_key
• FROM `project.dataset.orders`
• ),
• - Replicating the small lookup table across the 10 salt variations for the skewed key
• replicated_customers AS (
• SELECT
• customer_id,
• customer_name,
• IF(customer_id = 0,
• CONCAT(CAST(customer_id AS STRING), '_', CAST(salt AS STRING)),
• CAST(customer_id AS STRING)) AS join_key
• FROM `project.dataset.customers`
• CROSS JOIN UNNEST(GENERATE_ARRAY(0, 9)) AS salt
• WHERE customer_id = 0
• UNION ALL
• SELECT
• customer_id,
• customer_name,
• CAST(customer_id AS STRING) AS join_key
• FROM `project.dataset.customers`
• WHERE customer_id != 0
• )
• SELECT
• o.order_id,
• c.customer_name,
• o.order_amount
• FROM salted_orders o
• JOIN replicated_customers c ON o.join_key = c.join_key;

By salting customer_id = 0 with 10 variations, the guest checkout volume is distributed across 10 distinct worker slots rather than crushing a single slot, eliminating the straggler bottleneck.

Handling High-Volume NULL Keys

In an inner join, rows with NULL join keys never match because NULL = NULL evaluates to UNKNOWN . If a fact table contains millions of null keys, filtering them out prior to the join ( WHERE key IS NOT NULL ) prevents BigQuery from hashing and routing all null records to a single straggler slot. If unmatched null records are required in the output, isolate them, execute the join on non-nulls, and combine the sets using UNION ALL .

• Aggregations and Window Functions
• 1. COUNT(DISTINCT) vs APPROX_COUNT_DISTINCT

Evaluating exact distinct counts on high-cardinality columns (e.g., tracking distinct visitor cookies across billions of web hits) forces BigQuery to maintain exact hash tables of every unique value in slot memory, causing heavy memory pressure and shuffle serialization.

Google BigQuery provides APPROX_COUNT_DISTINCT() , which implements the HyperLogLog++ statistical algorithm:

HyperLogLog++ sketches large cardinalities using minimal memory (typically a few kilobytes per worker).

It provides a typical error rate of under 1% while completing orders of magnitude faster and consuming a fraction of the slot CPU time.

For executive reporting, daily active user (DAU) dashboards, and high-level trend analysis, APPROX_COUNT_DISTINCT is the recommended standard.

2. Window Functions vs Self-Joins for Deduplication

A classic pattern in event ingestion is deduplicating streaming records to retrieve the most recent state per entity. A naive implementation uses a self-join with an aggregation: -- ANTI-PATTERN: Heavy self-join with subquery aggregation

• SELECT a.*
• FROM `project.dataset.events` a
• JOIN (
• SELECT user_id, MAX(event_timestamp) AS max_time
• FROM `project.dataset.events`
• GROUP BY user_id
• ) b ON a.user_id = b.user_id AND a.event_timestamp = b.max_time;

This scans the table twice, performs an aggregation, and triggers a heavy shuffle join. The optimized BigQuery pattern utilizes the QUALIFY clause with an analytic window function: -- RECOMMENDED: Single table scan using QUALIFY

• SELECT user_id, event_type, event_payload, event_timestamp
• FROM `project.dataset.events`
• WHERE event_date >= '2026-09-01'
• QUALIFY ROW_NUMBER() OVER(
• PARTITION BY user_id
• ORDER BY event_timestamp DESC
• ) = 1;

The QUALIFY clause filters results directly on the output of window functions without requiring an extra subquery layer or self-join, executing in a single scan and aggregation stage.

3. Avoiding ORDER BY Without LIMIT on Massive Tables

In BigQuery's distributed architecture, sorting data requires collecting records onto a single worker node to establish a global sort order. If a query executes ORDER BY order_date across a table containing billions of records without a LIMIT clause:

Distributed worker slots cannot finalize the sort independently.

BigQuery attempts to route all output records to the root coordinator slot.

The root coordinator slot runs out of memory, terminating the query with Resources exceeded during query execution: ORDER BY clause produced too many results .

Best Practice: Always append a LIMIT N clause when ordering large datasets, or push presentation sorting to downstream BI dashboards and reporting applications.

Query Plan Inspection and Diagnostic Metrics

BigQuery provides complete transparency into query execution mechanics through the Google Cloud Console Execution Graph and the INFORMATION_SCHEMA.JOBS_BY_* metadata views.

Anatomy of Execution Stages

Each stage in the query execution graph reports performance metrics across four core categories:

Read: Time slots spent reading data blocks from Colossus storage or preceding stage shuffle buffers.

Compute: Time slots spent evaluating CPU instructions, arithmetic functions, regex expressions, and filtering predicates.

Write: Time slots spent serializing and writing output records into dynamic shuffle buffers or the final destination table.

Wait: Time slots spent waiting for upstream execution stages to produce data, or waiting for available Borg compute slots.

Critical Diagnostic Indicators

Execution Metric / Symptom Physical Phenomenon Root Cause Engineering Remediation

High Wait Time (Slots Idle) Slot Starvation or Shuffle Bottleneck Query is waiting for Borg slots to be freed, or waiting on slow upstream I/O Switch to larger reservation or refactor preceding stages to prune data earlier

Max Compute Time >> Avg Compute Time Worker Stragglers (Data Skew) A handful of slots are processing an overwhelming share of rows due to hot join/group keys Apply key salting, split queries, or filter hot keys into separate treatment paths

Shuffle Output >> Input Records Intermediate Record Explosion Unintended Cartesian product ( CROSS JOIN ) or bad multi-table join condition Review join predicates; eliminate Cartesian joins; verify cardinality of join keys

Shuffle Spill to Disk Worker Slot Memory Exhaustion Intermediate data within a stage exceeded available slot RAM; data was paged to persistent disk Reduce batch sizes, apply clustering, avoid wide window functions across large partitions

Slot Contention (Slot Limit Flatline) Capacity Saturation Concurrent queries or complex DAGs exhaust reserved slot ceiling; queries queue Implement reservation priorities, assign workloads to separate reservations, or enable autoscaling

Shuffle Spill to Disk: The Defining Memory Metric

In the Execution Graph, the metric Shuffle Spill to Disk (reported as shuffleOutputBytesSpilled in job statistics) is the most critical indicator of memory distress:

Under optimal execution, BigQuery brokers shuffle data entirely within ultra-fast in-memory RAM buffers.

When a query evaluates massive groupings, un-coalesced window functions, or skewed joins that exceed slot RAM, BigQuery pages intermediate records to Colossus persistent disk.

Persistent disk I/O over the network is several orders of magnitude slower than in-memory RAM access.

Queries that spill gigabytes or terabytes to disk experience severe latency degradation. Remediation involves clustering tables to pre-sort data, salting skewed keys, and replacing exact distinct aggregations with approximate algorithms.

BigQuery SQL Anti-Patterns vs Recommended Optimizations

Anti-Pattern Root Cause / Performance Impact Recommended Optimization Exam Context & Practical Implementation

Using SELECT * Reads 100% of column blocks from Colossus over Jupiter, driving up on-demand billing and slot memory usage. Specify strictly the required column names ( SELECT id, total ). Mandatory rule for columnar storage; LIMIT does not reduce scanned bytes on SELECT * .

Joining on Non-Clustered / Skewed Keys Triggers massive distributed shuffle where both tables are hashed and transported across slots; hot keys cause worker OOM crashes. Cluster both tables on the common join key; apply key salting to balance high-cardinality skew. Clustering co-locates rows in storage blocks, transforming full shuffle joins into localized block reads.

ORDER BY Without LIMIT on Massive Tables Forces all distributed worker results to be serialized onto a single root coordinator slot to establish global ordering, causing coordinator OOM. Apply ORDER BY with LIMIT N , or push ordering to reporting presentation layers. If top-N rows are not specified, sorting billions of rows on a single coordinator node will fail execution.

Multiple Repeated Subqueries Re-executes identical table scans and aggregations multiple times across different branches of a query. Consolidate common logic using Common Table Expressions ( WITH cte AS (...) ) or temporary tables. BigQuery optimizer inlines CTEs; for expensive multi-branch reuse, materialize intermediate data into a temporary table.

Using COUNT(DISTINCT) on Massive Cardinality Requires tracking every exact unique value in slot memory, consuming gigabytes of shuffle RAM and slowing down aggregations. Replace with APPROX_COUNT_DISTINCT() , which utilizes HyperLogLog++ to deliver 99%+ accuracy at 1% of the compute cost. Highly recommended for high-cardinality dashboard metrics (e.g., daily unique visitors across billions of events).

Filtering Post-Join ( HAVING or outer WHERE ) Transports millions of unneeded rows through the join and shuffle layers before filtering them out. Push filter predicates into the WHERE clause of inner subqueries or verify join condition filters. Predicate pushdown minimizes intermediate row counts before distributed shuffle processing occurs.

Loading diagram... BigQuery Query Execution: Broadcast Hash Join, Slot Allocation, and Dynamic Shuffle Spill Architecture