BigQuery optimization is mostly about not being clever

At a previous job I made our BigQuery workloads roughly 45% faster, and I keep a private list of what actually moved the needle versus what I merely enjoyed doing. The honest version of that list is deflating, so naturally it’s the useful one.

Partition pruning did more than everything else combined. Our biggest tables were partitioned by date, and half our expensive queries scanned all of history anyway, because a filter like date(event_ts) >= '2023-01-01' wraps the partition column in a function and quietly disables pruning. Rewriting filters so the partition column stands naked in the predicate turned full scans into slivers. Ten-minute fixes, massive wins, zero glamour.

Materializing three intermediate results beat optimizing thirty queries. The executive reporting queries all recomputed the same expensive customer-day spine from raw events. One scheduled intermediate table, thirty queries turned trivial. Finding it required reading query plans for an afternoon, which nobody does because reading query plans feels like homework. It is homework. Do the homework.

Honest data types, boring wins. IDs stored as strings that were joined against IDs stored as integers, casts sprinkled everywhere, statistics rendered useless. Standardizing types across the layer was tedious, moved several joins measurably, and required no intelligence whatsoever. My kind of optimization.

Now the confession section. The query I rewrote with an elaborate window-function scheme to avoid a self-join? Benchmarked identical to the original, because the optimizer had been handling it fine all along. The clustering I added to a mid-sized table on a hunch? Unmeasurable. I’d skipped the profiling step because I was confident, and confidence is free while compute is not.

The meta-lesson sits above any specific warehouse. Modern query engines are extremely good, and they are best when your requests are plain: prunable filters, honest types, work that isn’t repeated. Most “optimization” is removing the obstacles between a straightforward query and an optimizer that already knew what to do.

Be boring first. Measure. Be clever only where the measurements insist, which is almost nowhere.