BigQuery cost anti-patterns
The patterns that make BigQuery queries cost more than they need to, each with the mechanism, worked arithmetic, and the fix. Eleven of these the analyzer can detect from your SQL; three are patterns no single query can reveal.
What SELECT * actually costs in BigQuery
highBigQuery bills the columns you read, not the rows you return, so SELECT * is the most expensive way to write any query.
LIMIT does not reduce BigQuery query cost
mediumAdding LIMIT 10 to a query changes what you see, not what you are billed. It is the most common cost misconception in BigQuery.
Querying a partitioned table without a partition filter
highA partitioned table with no filter on its partition column scans every partition — usually the single largest avoidable cost in a BigQuery estate.
A partition filter that looks like pruning but is not
highFiltering the partition column against a subquery or a joined column does not prune anything — BigQuery has to read every partition to evaluate it.
Wildcard tables without a _TABLE_SUFFIX filter
highA wildcard table pattern with no _TABLE_SUFFIX constraint reads every matching table, which for daily-sharded data means the entire history.
A CTE referenced more than once is evaluated more than once
mediumBigQuery does not materialize WITH clauses. A CTE used in three places runs three times, and bills three times.
Self-joins where a window function would do
mediumJoining a table to itself reads it twice. A window function reads it once and is usually faster as well.
CROSS JOIN and joins with no equality condition
highA join without an equality condition produces the Cartesian product. It rarely ends in a bill — it usually ends in a failed query after burning a lot of slot time.
The 10 MB minimum, multiplied by many small tables
lowBigQuery bills at least 10 MB per table referenced per query. Join forty lookup tables and you have bought 400 MB before reading any real data.
ORDER BY without LIMIT on a large result
mediumA top-level sort with no LIMIT forces the whole result through a single worker. It is the most common cause of "resources exceeded".
COUNT(DISTINCT) where an approximation would do
lowExact distinct counts require a full shuffle. APPROX_COUNT_DISTINCT is typically within 1% and dramatically cheaper in slot time.
Views defined with SELECT * impose their cost on every reader
mediumnot detectable from one queryA view that selects every column makes column pruning impossible for anyone querying it — even someone selecting a single field.
The same query, over and over, uncached
mediumnot detectable from one queryRepetition is where BigQuery bills add up. A $2 query on a 15-minute schedule is $5,760 a year.
Rewriting a table resets it to active storage pricing
lownot detectable from one queryData untouched for 90 days drops to roughly half price automatically. Any write to the table puts all of it back at full price.
Find these across every query you run, not one at a time.
Finitizer scans your whole BigQuery job history for these patterns, ranks them by what they actually cost you, and shows the rewritten query beside the original.