Wildcard tables without a _TABLE_SUFFIX filter
A wildcard table pattern with no _TABLE_SUFFIX constraint reads every matching table, which for daily-sharded data means the entire history.
What it is
A wildcard reference like `events_*` unions every table matching the prefix. _TABLE_SUFFIX filtering with constants is the wildcard equivalent of partition pruning; without it, everything is read.
Why it costs money
- Daily-sharded tables are the classic case, and they accumulate a shard per day forever. Three years of shards is 1,095 tables in one scan.
- As with partitions, a non-constant _TABLE_SUFFIX filter does not prune.
- Wildcard queries also bypass the query cache, so repeated runs are repeatedly billed.
Worked example
Expensive
SELECT COUNT(*) FROM `analytics.events_*`Cheaper
SELECT COUNT(*)
FROM `analytics.events_*`
WHERE _TABLE_SUFFIX BETWEEN "20260701" AND "20260731"The arithmetic. Across 1,095 daily shards averaging 3 GiB, the unfiltered form bills ~3.2 TiB ($20). One month bills ~93 GiB ($0.57).
How to fix it
- 1Always constrain _TABLE_SUFFIX with literals or expressions of literals.
- 2Better: migrate sharded tables to a single partitioned table. Partitioning is faster, cheaper, and does not need this rule.
The analyzer detects this one. Paste your query into the BigQuery Query Cost Analyzer and it will point at the exact line.
Fixing one query is satisfying. Fixing the pattern is the win.
Finitizer finds every instance of this across your BigQuery job history, ranks them by what they actually cost, and keeps checking after you have fixed them.