BigQuery Optimization

Scope: project shop-prod, region us. You didn't give a region, so I assumed us. If shop lives elsewhere, use your region in any INFORMATION_SCHEMA queries (for example region-europe-west1). I haven't run anything against your project. This rewrite comes from the query text alone, and I'm assuming order_ts is a TIMESTAMP.

Optimized query

SELECT
  order_id,        -- replace with the columns you actually need
  customer_id,
  order_ts
FROM
  `shop-prod.shop.orders`
WHERE
  order_ts >= TIMESTAMP '2025-01-01 00:00:00'
  AND order_ts <  TIMESTAMP '2026-01-01 00:00:00'
  AND customer_id NOT IN (
    SELECT customer_id
    FROM `shop-prod.shop.blocked`
    WHERE customer_id IS NOT NULL
  )
ORDER BY
  order_ts
LIMIT 1000;  -- remove or adjust if you really need every row

Summary of optimizations

1. Replaced EXTRACT(YEAR FROM order_ts) = 2025 with a range filter. A function on the column prevents partition pruning and block pruning. A plain range on order_ts lets BigQuery prune statically.
2. Added WHERE customer_id IS NOT NULL to the NOT IN subquery. This lets BigQuery use an anti-hash join instead of the slower left outer hash join.
   • Behavior change: if shop.blocked contains a NULL customer_id, the original query returns zero rows, because NOT IN with a NULL evaluates to NULL. The rewrite returns the rows you almost certainly intended. If you somehow relied on the old behavior, tell me.
3. Replaced SELECT * with an explicit column list. This reads less data. I don't know your schema, so fill in the columns you need.
4. Added LIMIT to the ORDER BY. A full sort of a year of orders is expensive. If you need all rows, drop the LIMIT, or consider whether the sort is needed at all.

Table layout (biggest lever if the table is large)

The range filter only prunes partitions if shop.orders is partitioned on order_ts. To check, run:

bq show --format=prettyjson shop-prod:shop.orders

Look at timePartitioning, clustering and requirePartitionFilter in the output.

If the table is not partitioned, and each day holds roughly 10 GB or more (otherwise cluster only), you can create a partitioned copy:

CREATE TABLE `shop-prod.shop.orders_partitioned`
  PARTITION BY TIMESTAMP_TRUNC(order_ts, DAY)
  CLUSTER BY customer_id
  OPTIONS (require_partition_filter = TRUE)
AS
SELECT * FROM `shop-prod.shop.orders`;

You can't convert the existing table in place. Note that require_partition_filter = TRUE makes any query without an order_ts filter fail.

Validate before running

A dry run shows the bytes the query would process, and it costs nothing:

bq query --use_legacy_sql=false --dry_run "<the optimized query>"

Run it on the original query and on the rewrite, and compare totalBytesProcessed. The dry run won't show savings from clustering, since those happen at execution time.

These changes should improve cost-efficiency, but I can't promise a particular saving until you've compared the dry-run numbers.