BigQuery doesn't charge you for having data. It charges you for touching it. On-demand pricing bills per byte scanned, which means your bill isn't a data-size problem or a traffic problem. It's a query-shape problem, and query shape is fixable in an afternoon per table.
A recent client was a textbook case. Dashboards running SELECT * over an unpartitioned events table, so every refresh scanned the full history back to 2019. Plus a pile of scheduled queries feeding tables nobody read anymore. Three weeks of work cut the bill 72%, about £2K a month, and the queries got faster. That last part surprises people. It shouldn't. Bytes scanned is both the cost metric and the latency driver. Same lever.
Week 1: find out where the money goes
Don't guess. BigQuery logs every job with its billed bytes:
SELECT
user_email,
query,
ROUND(total_bytes_billed / POW(1024, 4), 2) AS tib_billed,
COUNT(*) AS runs,
ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tib_total
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
AND job_type = 'QUERY'
GROUP BY user_email, query
ORDER BY tib_total DESC
LIMIT 25;
Almost every time, a handful of queries dominate. Here it was five queries producing most of the spend, and four of them were dashboard refreshes running on a schedule. Recurring queries are where the money is, because a bad shape times 48 runs a day compounds while you sleep.
Week 2: fix the shape
Partition by the column people actually filter on (usually a date), cluster by the next most common filters:
CREATE TABLE analytics.events_v2
PARTITION BY DATE(event_timestamp)
CLUSTER BY customer_id, event_type
OPTIONS (require_partition_filter = TRUE)
AS SELECT * FROM analytics.events;
Two details matter more than the DDL:
require_partition_filter = TRUE is the line that keeps the fix fixed. Without it, someone writes one exploratory full scan in month two and you're back where you started. With it, BigQuery rejects the query instead of billing you for it. Make waste an error, not a habit.
Clustering only pays if queries filter or join on the clustered columns. One team's ad-hoc analysis here filtered on a column we hadn't clustered, and their queries saw no improvement at all. Cluster for the workload you measured in week 1, not for the schema you imagine.
Then rewrite the top offenders. SELECT * on a columnar store bills you for every column, including the 40 you never render. Listing columns explicitly cut one dashboard query's scan by an order of magnitude before partitioning even entered the picture.
Week 3: kill the zombie jobs
This is the unglamorous part and it was worth a big slice of the 72%. Scheduled queries outlive their consumers. The dashboard gets deprecated, the Slack report gets muted, and the pipeline underneath keeps materializing tables daily, forever, at full scan cost.
Cross-reference every scheduled query against actual reads of its output table (INFORMATION_SCHEMA again, look for jobs referencing it). No reads in 60 days? Pause it. We paused first rather than deleting, waited two weeks for screams, heard none, deleted. Nobody has asked about any of them since.
What I'd tell you to skip
Slot reservations. Flat-rate pricing is a real tool, but it's the tool you reach for after fixing query shape, not instead of it. Buying capacity to run wasteful queries is paying rent on the waste. Fix the shape first, then check whether your steady-state scan volume still justifies reservations. After this cleanup, it didn't come close.
The whole engagement was three weeks because none of this is hard. It just requires someone to actually look at INFORMATION_SCHEMA instead of the billing page, which tells you what you spent but never why.
I do this kind of work at reducecost.cloud on pay-for-savings terms: nothing upfront, I take a share of the reduction I deliver, and if I save you nothing you pay nothing.
Top comments (0)