BigQuery SQL: Tips, Tricks and Optimization

Good BigQuery SQL is mostly ordinary good SQL. But the engine has a few characteristics that reward particular habits: it reads whole columns, it distributes joins across many workers, and it charges by what it reads or computes. The twelve habits below follow from those facts, and each is tied to Google's own guidance (Google Cloud: optimize query computation). Three are demonstrated on real data.

Read less

1. Name the columns you need. BigQuery stores each column separately and reads only those a query mentions. SELECT * reads all of them. When you want nearly all, exclude the few you do not (Google Cloud: query syntax):

SELECT * EXCEPT (raw_payload, internal_notes)
FROM sales.orders;

SELECT * REPLACE (ROUND(amount, 2) AS amount)
FROM sales.orders;

2. Do not use LIMIT to save money. Google states it plainly: adding LIMIT to a SELECT * query does not affect the amount of data read, and on a table that is not clustered a LIMIT does not reduce compute cost. To look at a table, use the table preview, which Google documents as free of charge (Google Cloud: estimate and control costs).

3. Filter on the partition column, and on cheap types. A filter on the partitioning column lets whole partitions be skipped. Beyond that, Google notes that comparisons on BOOL, INT64, FLOAT64 and DATE columns are typically faster than on STRING or BYTES. Partitioning and clustering covers the details.

Join less

4. Aggregate before you join. A join moves rows between workers, so the fewer rows that reach it the better. If the question is about groups, reduce to groups first and join the small result. The example below does exactly this.

5. Put the largest table first. The optimizer chooses the join strategy, but Google's advice is to write the largest table first and smaller ones after it, which favours broadcasting the small table to the workers handling the large one.

6. Join on integers. BigQuery does not index keys, so a wide STRING key costs more to compare than an INT64.

7. Replace self-joins with window functions. A self-join can square the number of rows. "Compare each row with the previous one" is a job for LAG, as shown in BigQuery window functions.

8. Do not assume a CTE is computed once. This surprises people from other databases. A WITH clause is for readability. Google warns that it may be evaluated every time it is referenced. If an expensive subquery is used several times, store it:

CREATE TEMP TABLE daily AS
SELECT DATE(pickup_at) AS day, COUNT(*) AS trips
FROM analytics.trips
WHERE pickup_at >= '2024-01-01' AND pickup_at < '2024-02-01'
GROUP BY day;

SELECT * FROM daily WHERE trips > (SELECT AVG(trips) FROM daily);

Fail less

9. Compute several measures in one pass. COUNTIF(condition) counts the rows where a condition holds (Google Cloud: aggregate functions). Combined with SUM(IF(...)), it lets one scan answer what would otherwise take a query per measure.

10. Guard division and casting. In BigQuery a division by zero is an error, and one such row fails the whole query, however long it has been running. SAFE_DIVIDE returns NULL instead (Google Cloud: mathematical functions). SAFE_CAST does the same for conversions, and the SAFE. prefix for most other functions (Google Cloud: function calls):

SELECT SAFE_CAST(postcode AS INT64)      AS postcode_number,
       SAFE.PARSE_DATE('%Y-%m-%d', dob)  AS date_of_birth
FROM crm.customers;

Use these deliberately. A NULL that hides bad data is not an improvement on an error unless you count the NULL values afterwards.

11. Ask for approximate answers when exactness is not needed. APPROX_COUNT_DISTINCT(user_id) returns an estimate of COUNT(DISTINCT user_id) using less memory, and Google says it performs better on very large input (Google Cloud: approximate aggregate functions). A dashboard showing "about 4.2 million users" rarely needs the exact figure.

Three of these on real data

The script runs habits 9, 10 and 4 on New York yellow taxi trips for January 2024 (NYC TLC: trip record data). The queries are GoogleSQL, parsed with the BigQuery grammar of the sqlglot library, translated to DuckDB and run locally. It is not a run of BigQuery.

# Three GoogleSQL habits on real trip data. Each query is parsed with sqlglot's BigQuery grammar,
# translated to DuckDB and run locally. It is not a run of BigQuery, so no bytes billed are shown.
import duckdb
import sqlglot

con = duckdb.connect()
con.execute("CREATE VIEW trips AS SELECT * FROM read_parquet('../../dedata/yellow_tripdata_2024-01.parquet')")
con.execute("CREATE TABLE zones AS SELECT * FROM read_csv_auto('../../dedata/taxi_zone_lookup.csv')")


def bq(sql):
    return con.execute(sqlglot.transpile(sql, read="bigquery", write="duckdb")[0]).fetchall()


# 1. Several measures in one pass: COUNTIF and SAFE_DIVIDE instead of one query per measure.
print("payment_type     trips    tipped  pct_tipped  tip_pct_of_fare")
for row in bq("""
SELECT payment_type,
       COUNT(*) AS trips,
       COUNTIF(tip_amount > 0) AS tipped,
       ROUND(100 * SAFE_DIVIDE(COUNTIF(tip_amount > 0), COUNT(*)), 1) AS pct_tipped,
       ROUND(100 * SAFE_DIVIDE(SUM(tip_amount), SUM(fare_amount)), 1) AS tip_pct_of_fare
FROM trips
GROUP BY payment_type
ORDER BY payment_type"""):
    print(f"{row[0]:12} {row[1]:9,} {row[2]:9,} {row[3]:11} {row[4]:16}")

# 2. A denominator that is sometimes zero.
zero, null_result = bq("""
SELECT COUNTIF(trip_distance = 0),
       COUNTIF(SAFE_DIVIDE(fare_amount, trip_distance) IS NULL)
FROM trips""")[0]
print(f"\ntrips with zero distance: {zero:,}; fare per mile returned as NULL by SAFE_DIVIDE: {null_result:,}")

# 3. The same answer two ways: join then aggregate, or aggregate then join.
join_first = bq("""
SELECT z.Borough AS borough, COUNT(*) AS trips, ROUND(SUM(t.total_amount)) AS revenue
FROM trips AS t
JOIN zones AS z ON z.LocationID = t.PULocationID
GROUP BY borough
ORDER BY trips DESC""")
aggregate_first = bq("""
WITH by_zone AS (
  SELECT PULocationID, COUNT(*) AS trips, SUM(total_amount) AS revenue
  FROM trips
  GROUP BY PULocationID)
SELECT z.Borough AS borough, SUM(b.trips) AS trips, ROUND(SUM(b.revenue)) AS revenue
FROM by_zone AS b
JOIN zones AS z ON z.LocationID = b.PULocationID
GROUP BY borough
ORDER BY trips DESC""")
rows_joined = bq("SELECT COUNT(*) FROM trips")[0][0]
zones_joined = bq("SELECT COUNT(DISTINCT PULocationID) FROM trips")[0][0]
print(f"\nrows entering the join: {rows_joined:,} when joining first, {zones_joined} when aggregating first")
print(f"results identical: {join_first == aggregate_first}")
for borough, trips, revenue in aggregate_first[:3]:
    print(f"  {borough:10} {trips:9,} trips  {revenue:12,.0f} total amount")

payment_type     trips    tipped  pct_tipped  tip_pct_of_fare
           0   140,162    48,799        34.8              7.7
           1 2,319,046 2,204,834        95.1             22.5
           2   439,191       128         0.0              0.0
           3    19,597        99         0.5              0.2
           4    46,628       370         0.8              3.2

trips with zero distance: 60,371; fare per mile returned as NULL by SAFE_DIVIDE: 60,371

rows entering the join: 2,964,624 when joining first, 260 when aggregating first
results identical: True
  Manhattan  2,646,948 trips    59,247,529 total amount
  Queens       273,128 trips    18,639,793 total amount
  Brooklyn      25,258 trips       828,808 total amount

One pass, four measures. The first query scans the trips once and returns, for each payment type, the count, the number tipped, the share tipped and tips as a share of fares. Payment type 1 is credit card: 95.1% of those trips record a tip. Type 2 is cash, at 0.0%.

That second figure is a lesson in itself. The data dictionary states that the tip field is populated for card payments and that cash tips are not included (TLC: data dictionary). The query is correct, and the conclusion "cash passengers do not tip" would be wrong. No SQL habit substitutes for reading how a column is produced.

A zero denominator. 60,371 trips have a recorded distance of zero. Fare per mile for those rows is undefined, and SAFE_DIVIDE returns NULL for exactly those 60,371. On BigQuery the plain / operator would raise a division by zero error instead.

Aggregate first. Both versions of the third query give identical results. Joining first sends 2,964,624 trip rows through the join. Aggregating first sends 260, one per pickup zone. The answer is the same and the join is over eleven thousand times smaller.

Check your work

12. Read the execution details. After a query runs, the console shows its stages, with rows in and rows out of each. Google's guidance points to two things worth looking for:

• A stage whose output is far larger than its input. That is a join multiplying rows, often from a key that is not unique.
• A stage that writes much more than the others. It usually means a filter could be applied earlier.

And before a query runs, the validator in the editor reports how many bytes it will process. Make reading that number a reflex. BigQuery cost optimization explains what the number costs.

Habits to drop

• Ordering in the middle of a query. ORDER BY belongs in the outermost query, ideally with a LIMIT. Sorting a very large result with no limit can fail with a resources exceeded error.
• Updating one row at a time. BigQuery's DML is designed for bulk changes. Stage the changes in a table and apply them in one statement.
• Tables named by date. A table per day, queried with wildcards, performs worse than one partitioned table.
• One enormous statement. Splitting a complex query into steps with temporary tables is easier to debug and can be faster.

For tuning that applies to any SQL engine, see SQL optimization for data engineers.

SQL for analytics and pipelines is covered in the Data Engineering course.

Sources

• Optimize query computation (Google Cloud Documentation) — Google Cloud
• Estimate and control costs (Google Cloud Documentation) — Google Cloud
• Query syntax (Google Cloud Documentation) — Google Cloud
• Aggregate functions (Google Cloud Documentation) — Google Cloud
• Mathematical functions (Google Cloud Documentation) — Google Cloud
• Function calls (Google Cloud Documentation) — Google Cloud
• Approximate aggregate functions (Google Cloud Documentation) — Google Cloud
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission
• Data dictionary: yellow taxi trip records (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission