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.

Edit:

I forgot another technique. 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.

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.

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.

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.

Edit:

I forgot another technique. 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.