Google Cloud BigQuery costs can be reduced by limiting unnecessary data scans, avoiding repeated calculations and setting query quotas to control consumption. At Investing.com, introducing quotas reduced monthly spending from approximately $3,000 to $600, an 80% reduction. Subsequent architecture changes supported roughly five times the data volume within the original $3,000 monthly budget.
Watch on YouTube (opens in a new tab)
How We Reduced BigQuery Costs at Investing.com
When I started working on the BigQuery project at Investing.com, the monthly bill was about $3,000. Within a month, it was down to roughly $600. The first change was to introduce query quotas.
Over the following year, we also changed the architecture. The volume of data we processed grew from approximately 20 TB to 100 TB, while monthly spending was back at around $3,000. The initial 80% drop and the later growth within the original budget were separate results, achieved at different stages.
The work involved some familiar engineering decisions: how much data to scan, which transformations to run in advance, and how often reports really needed to refresh. This article draws on the experience I shared in my Big Data Demystified session on BigQuery cost reduction.
Put a limit on expensive mistakes
I started with quotas because they gave us a way to control consumption immediately. They also made the cost of running queries harder to ignore.
A quota limits how much work can run. It does not make the same query cheaper, and once the limit is reached, further queries may be blocked. That distinction matters when interpreting the initial 80% reduction: it was a spending reduction after applying limits, rather than a benchmark showing the same workload running for one-fifth of the cost.
For teams using on-demand pricing, daily project and user query quotas can help limit consumption. At the individual query level, maximum bytes billed rejects a query before execution if its estimated billable bytes exceed the configured threshold. Google’s cost control documentation (opens in a new tab) explains these controls.
Leave room for essential work. In the setup I described, we kept the user limit below the project limit so that one person’s mistake would not consume the entire allowance. The right limits depend on who shares the project and which jobs must keep running.
Check what the query actually reads
A good place to look is SELECT *. When an analyst needs two columns from a table with a hundred, reading all hundred creates work the analysis never uses.
Adding LIMIT can make the result look small without making the scan small. For non-clustered tables, it does not reduce the data read. Use Preview to inspect sample rows, select the columns you need, and filter the relevant partitions. See Google’s query optimization guidance (opens in a new tab).
Clustering is another option when queries regularly filter on particular columns. It can help BigQuery skip irrelevant blocks, but the choice and order of columns should reflect the queries people actually run. Test those queries before and after the change. See querying clustered tables (opens in a new tab).
A view can save maintenance and still cost money to query
We found that roughly 60% of our workload could use views instead of separate ETL processes. That meant fewer pipelines to build and maintain.
The cost moves, though. A logical view stores SQL logic; querying it runs the underlying query. You save the work of maintaining a separate transformation pipeline, while paying for processing when someone uses the view. See logical views in BigQuery (opens in a new tab).
For occasional analysis, that can work well. A heavy query that runs every five minutes is a different decision. In that case, I would look at calculating the result ahead of time and letting users query a smaller summary.
Take a dashboard showing daily totals. It may be able to read a daily summary table instead of aggregating raw events on every refresh. The question is whether maintaining that table costs less than repeating the calculation. A suitable materialized view is another option for recurring workloads. See Google’s comparison of logical and materialized views (opens in a new tab).
Count the refreshes
In another reporting example I shared, a query that seemed inexpensive became costly through repeated refreshes. Looking at the price of one execution had hidden the cost of using the report throughout the day.
Check what happens when someone opens a dashboard, changes a filter or refreshes a chart. Apply the same scrutiny to pipelines: if only yesterday’s data changed, is it necessary to rebuild the whole reporting window? Late-arriving events and corrections may justify reprocessing history, but they should inform how far back you go.
Caching can help when results are reusable. BigQuery’s result cache avoids query charges on eligible cache hits, subject to conditions including identical query text and unchanged underlying data. Check cache eligibility (opens in a new tab) before assuming a repeated query will be free.
Decide how fresh the data needs to be
At Investing.com, the analysis I described focused on days, weeks and months. We did not need streaming ingestion for those workloads, because querying the latest hour was not a requirement.
That gave us a practical design constraint. We could choose processing schedules around the analysis people needed. A daily management report and an operational alert may need different approaches, even when they use the same source data.
I separated ingestion, transformation, analytical modeling and BI consumption into distinct layers. That made it possible to choose a technology for each stage and replace an expensive implementation without rebuilding everything around it.
I also weighed the maintenance burden. In the tests I described, an alternative processing engine could reduce costs for some stages, but tuning and operating it required more effort. That engineering time belongs in the comparison.
Look beyond the monthly bill
The longer-term result at Investing.com was handling approximately five times the data within the original monthly budget. That is why I look at the bill alongside the amount of work the platform supports.
A useful comparison includes data volume, query frequency, report availability and maintenance effort. If spending falls because people can no longer run the reports they need, there is still a problem to solve.
The original session used the pricing and product features available at the time. For a current assessment, check whether the workload uses on-demand or capacity-based pricing, because the billing model changes how processing improvements translate into savings. See current BigQuery pricing (opens in a new tab).
I would start with one recurring workload: inspect what it reads, count how often it runs, and check whether the result needs to be recalculated each time. That gives you a concrete change to measure before taking on a larger redesign.