BigQuery is a leading data warehouse solution, and though its costs are relatively low compared to other solutions, you should estimate BigQuery costs beforehand. Otherwise, you can find yourself in need to turn it down or seek for BigQuery cost optimisation solutions.

Since many new clients ask me how much BigQuery will cost them, I dedicate this blog post to let you know how you can calculate the potential price of BigQuery on your own. 

The first thing you should be aware of is that the price of BigQuery includes two parts:

1. cost of storing your data on the cloud (in BigQuery);
2. and the cost of analysis (or computing), such as SQL statement execution.

BigQuery storage costs

First, let’s start with how much you will pay to store the data in BigQuery. At the beginning of this chapter, it’s worth mentioning that every user receives 10 GB of storage for free every month. Therefore, if the size of your data is small, you can jump to the next section of this article since the data storage costs will be zero.

If you are still reading it, your data is more than 10 GB, or you don’t know how the size of it. So let me tell you that BigQuery splits the storage price into two parts: active logical storage and long-term logical storage. 

BigQuery labels your data (or table) as a long-term logical storage if it’s not modified during the last 90 days. Even though you can pay 2x times less for long-term logical storage, I don’t recommend looking at it since it’s always better to have a higher estimation than a lower one. 

Since we don’t split storage into long and active for our rough estimation, we can use the equation below to estimate the costs of keeping our data on BigQuery servers.

The total amount of logical data GB x Price per GB

So, as you can see above, you pay for logical storage, not physical storage. It can be a bit hard to forecast it since physical storage doesn’t equal logical storage. You can have a look at my screenshot below to see the difference between the two of them. The logical storage is a bit less in BigQuery for the same data than on my MacBook.

So, if you only want to have a better estimation then I recommend you download the sample of data (one day, for instance) from your current analytics solution (GA4, Mixpanel, SQL server) and load it into BigQuery sandbox, which is free. After you load it into BigQuery, you will have an active logical storage summary for that sample, and you will be able to figure out the final cost for BigQuery storage. Otherwise, you can use the size of data from another source.

One of my clients wanted to transition from Mixpanel to BigQuery and wanted to have a better estimation of the cost that he will pay for storage, we made the following.

1. We used raw data export API to export the entire data of one day. 
2. We converted it into a tabular format and imported it into BigQuery Sandbox.
3. The physical storage equalled 73.23 MB, but the final active logical storage was 54.41 MB
4. Since it was the day with the highest amount of data (the most significant number of events), we decided to multiply it by 365 to know how much having of 1-year data will cost us monthly. 
5. Price per GB can be found in the official BigQuery documentation. It costs $0.02 per GB if you store the data on US servers.

Thus, we got to this equation:

54.41 Mb / 1000 x 365 (days) x $0.02 per GB  = $0.40 per month.

Since Google’s free tier offers 10 GB of free active storage every month, we understood that we would pay $0.20 monthly after having 1-year data in BigQuery. 

(54.41 Mb / 1000 x 365 (days) – 10) x $0.02 per GB  = $0.20 per month.

BigQuery analysis costs

Second, after we estimated storage costs, let’s predict the second part of BigQuery costs. It’s analysis or computing costs. Whenever someone uses BigQuery to research and get the data from there into Google Sheets, they run SQL queries, and BigQuery charges you for it.

Even though your BigQuery tables can be highly optimised to query fewer data and you can be under the 1Tb free tier, you still should estimate how much it will cost you.

The equation for analysis costs consists of a few parts:

1. Number of users who will run queries on BigQuery
2. The average number of queries a user will run daily
3. The average amount of data processed by these queries

Number of Users x  Number of Daily Queries Per User# x Average Amount Of Data Per Query per User x Days Per Month x Price per TB

You can determine the number of users by considering how many team members will use BigQuery. After that, you can ask how many daily queries they will run, and you can get the average. 

After knowing these two variables, you can look at the average amount of data processed. It’s the most difficult since you don’t know how much you will crawl daily. 

From my experience, I recommend looking at your (your team’s) daily activity and how many reports or analyses you do. For instance, if you use Mixpanel or Tableau, how many columns you use to retrieve data? What’s the time range for this analysis, whether it’s the last 90 days or the last 360 days?

That can give you a rough estimation of how much the average amount of data processed can be. Besides that, you can import the last 30 days of data into BigQuery Sandbox and play with them, trying to understand how much data, on average your query processes. 

In my client’s case, we imported the last 90 days of data and he gave me a summary of what queries he would run daily, weekly and months. After writing the queries and calculating the total amount of data they processed, I arrived at 130.29 mb per query.

So costs for the BigQuery analysis part where:

2 users x 32 queries x 0.00013029 TB x  30 x $5 per TB  = $1.25

My client doesn’t pay for analysis costs because the total processed data amount (0.25 TB) is lower than 1 TB.

BigQuery total costs

As you can see above, you should sum the costs of two BigQuery parts, and you will get the final estimation of how much BigQuery can cost you. 

In the case mentioned above, it was $0.20 per month for storage and $0.00 per month for analysis which gives $0.20 per month in total.

BigQuery calculator

Every case can require a bit different approach. Though I mentioned two critical parts of BigQuery above, you can run machine-learning models or use other Google Cloud solutions that Google can charge you accordingly. Therefore, I recommend you calculate the final costs of using the BigQuery calculator provided by Google, which is free. 

Conclusions

If you are considering using BigQuery and you need to estimate the final costs, you should be aware that BigQuery price depends on the two core elements:

1. how much data will you store in BigQuery (active logical storage)
2. and how much data you will use for running SQL queries

To calculate the first one, you can use the following equation:

The total amount of logical data GB x Price per GB

For the second part, you can use the equation below. 

Number of Users x  Number of Daily Queries Per User# x Average Amount Of Data Per Query per User x Days Per Month x Price per TB

Frequently Asked Questions