I am working with a large dataset (~5GB) stored in a Google BigQuery database. My goal is to create a web app which works a little bit like an Excel spreadsheet where users start off by seeing the full dataset (or at least they think they do - the first 10,000 rows should suffice). They then would go on to apply a number of different filters to the dataset to reduce it to a manageable size (100-1000 rows) and continue by clicking on any one of those rows which opens a detailed view.

Here's the problem: Google BigQuery charges me based on data filtered, not on data returned. So the cost of querying the entire database with a really complex filter is equal to the cost of just querying a single row (which would be necessary for the detail view).

How should I go about minimising the number of queries?

So far I've come up with the following: Store all the data displayed in the web app on my application server (i.e. the 10,000 rows addressed above) and then service the detail view requests from that data.

Is there a better solution for this?

  • 2
    Have you considered NOT using BigQuery for processing such a small dataset? If you can pre-filter and index the data, you might be able to perform further queries outside of the original database.
    – amon
    Oct 20, 2021 at 18:11
  • @amon using BigQuery is a requirement I cannot change
    – Moritz_st
    Oct 20, 2021 at 18:12
  • if they start with the large dataset, why do you need to hit bigquery again to filter what you already have?
    – Ewan
    Oct 20, 2021 at 18:16
  • @Ewan You mean storing all the data in the application server? That would sort of defeat the purpose of having a database entirely
    – Moritz_st
    Oct 20, 2021 at 18:23
  • 6
    If BigQuery ($$$) is "a requirement that you cannot change," then I would not go out of my way to minimize costs if doing so would compromise the application. However, I would also freely discuss the issue with your manager and with others who represent the parties who are paying those invoices. There is always a balance between "desiring to save money, of course," and "the costs of doing business." Oct 20, 2021 at 20:09

2 Answers 2


Seems to me like for this kind of application, rather than pretending to the user that you are showing them all the data, you could instead show them a pre-selected random sample of the data to "assist with query design"

Google charge by "data scanned", so if you can reduce the size of this sample dataset to say 10Mb you can do 100,000 queries before you hit the 1TB free limit.

Once the user is happy with their query they can then click a button to run it against the real dataset. Maybe put in some UI annoyances to discourage users using that as standard.

  • 2
    Echoing my comment above: "Google's fees are a cost of doing business." Obviously we always want to reduce costs whenever we can, but the OP's situation might be one where (s)he needs to seek external guidance from financial stakeholders. How much cost are they willing to accept? How much do they care about what the costs are? They may not care at all ... and there's only one way to find out. "Never 'assume' ..." Oct 20, 2021 at 20:11
  • I guess this is an optimisation. The user can't tell the difference and your costs come down a hundred fold. It doesnt really matter whether you are passing on the costs or not. its either cheaper for your customer or more profits for you
    – Ewan
    Oct 21, 2021 at 16:29

First of all, you should look into sharding, clustering, or partitioning the data. All of these techniques can reduce query costs.

Sharding splits data across multiple tables. Typically, you split by some large grouping value and put that in the table name. So “sales” becomes “sales_midwest”, “sales_northeast”, etc. Then you either make your query smart enough to pick the right shard or use wildcard tables and the _table_suffix pseudo column.

Clustering is similar indices on a traditional relational database. You specify columns that are likely to be useful in queries.

Partitioning specifies either an integer or date column. Behind the scenes, BigQuery stores each partition in a different table. If the where clause references the partition column, then BigQuery only scans the relevant partitions. That means you only get billed for those partitions.

Depending on the nature of your data, one or more of these techniques could help.

Otherwise, BigQuery lets you save the results of one query into a destination table. You just set the destination table using whichever SDK or API you are using.

You can also set the destination table to automatically expire after a set amount of time. That requires a separate API call to BigQuery before you submit the actual query job. This step would add noticeable lag to an interactive system. Alternatively, you can add a background process to clean up the result tables.

All of this adds considerable complexity. Before implementing any of it, you should estimate how many queries you actually expect. Compute the cost of running the full table scan on every one of those. I’d expect the number will either be so low that you can confidently ignore the costs or high enough that you can have a meaningful conversation comparing development cost to BigQuery cost.

Another technique is to consider the columns you use. BigQuery charges based on columns used by the query. Do you need all columns all the time? You could potentially slash the cost considerably by limiting the columns. Obviously, you still must allow access to everything. If every user needs every column all the time then this won’t work. I can imagine at least a few users might prefer working with fewer columns until they have the final results. As with any other idea, the savings may or may not be worth the effort.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.