Don't Break the Bank on SQL Queries: BigQuery On-Demand vs Flat-Rate prices. Which Saves You More? 💰😎

BigQuery is a powerful database with numerous benefits, but like any tool, it has its concerns. First of all, it is important to be mindful of the amount of data you are going to process to avoid unexpected costs at the end of the month.

While this may not be a concern if your data is under 1 TB, you may break the bank if you have much more than this. Stay ahead of the game by familiarizing yourself with the various pricing models available in BigQuery, and unlock the full potential of it as our team did.

---

Discover the differences between Default and Flat-Rate pricing models 🧐

BigQuery offers two pricing models for running SQL queries: on-demand and flat-rate. You may read a little more about it here - link. By default, you will be on the on-demand pricing model when you start working with the database.

But what happens when you are dealing with massive amounts of data, say, 20 TB of it? And what if your SQL query is particularly complex?

This is where the on-demand pricing model can get pricey, as you will pay for the amount of data you process while running your query. The cost for processing 1 TB of data ranges from $5 to $9 depending on the location of data processing. To process 20 TB of data in "us" or "eu", you will have to pay $100 for just one query. However, with up to 2,000 concurrent slots available, your complex query will be finished very quickly.

But BigQuery also offers a Flat-rate pricing model where you pay for the time you have slots booked for your tasks, rather than the amount of data processed.

You can book anywhere from 100 to 10,100 slots, with prices ranging from $4 to $6 for booking 100 slots for 1 hour. The best part is that you pay for the minutes. That means that if you cancel the booking in a couple of minutes after creating it, you will only be charged $0.12 to $0.20.

So, let's go back to our earlier example of processing 20 TB of data. If you can complete your data query in less than an hour with 100 slots reserved, you will only end up paying less than $5 for the entire process! That is a whopping $95 in savings per SQL query, making the Flat-rate pricing model an attractive option for those looking to optimize their budget.

---

Unveiling the secret to our massive savings: Switching to a hybrid approach! 🤩

When we discovered the potential savings with the flat-rate pricing model, we explored our options. There are three subscription types to choose from, each with its own unique benefits:

1. Monthly flat-rate commitments starting at $2,000 per 100 slots per month
2. Annual flat-rate commitments starting at $1,700 per 100 slots per month
3. Flex slots, which offer short-term commitments starting at just $4 per 100 slots per minute.

After careful consideration, we opted for flex slots.

But when we need to process small-sized data sets quickly we still use on-demand. It is perfect when you do not want to wait.

And when we know we are going to be processing more than a few TB of data, we simply buy fixed slots, run the query, and cancel the reservation. Typically, I buy 200 slots for less than 30 minutes, and that is plenty of time to get the job done.

Overall, our hybrid approach has been a game-changer, helping us save money and optimize our data processing in a way that works best for us.

---

Unlock the power of Flex slots: Step-by-Step Guide 👩‍💻

Let me share with you the incredible cost savings our team has experienced by using both the on-demand and flat-rate pricing models for our data processing needs.

Using the on-demand model is simple - just log in, write your SQL query, and you are off to the races. But when it comes to the flat-rate pricing model, there are a few more steps involved.

First, you will need to choose the project you want to purchase slots for. It is important to make sure you have the proper permissions for this project, as you can buy slots with Project A and use them to process data from Project B.

Step 1.

In your Google Cloud panel go to BigQuery - Capacity management.

Step 2.

Then choose "Create commitment" and fill in the form.

While choosing the “Commitment duration” we choose “Flex” as we decided earlier. We do not need an Annual or Monthly reservation this time.

When it comes to buying slots for your SQL queries, it is important to consider the complexity of your query. If you are dealing with a lot of window functions, group by statements, and other complex operations, it may be worth investing in 300 or more slots. By purchasing more slots, you will be able to process your data faster.

Click 'NEXT', type ‘CONFIRM’ and click ‘Purchase’.

Step 3.

Congratulations on purchasing the capacity you need! Before diving into running your queries, it is important to reserve those slots for the specific project you will be working on. To do this, simply navigate to the "Create reservation" option and follow the prompts.

Fill in the fields.

Click on “Create assignment”.

Choose the project where you will write a query and click "Save".

Now you can run your query!

Step 4.

Great job on completing your request. Now it is time to clean up after yourself and delete everything you created, step by step in reverse order.

First, delete the assignment you created for the specific project. Then, cancel the reservation you made for the flat-rate capacity. Finally, head back to the "Slot commitments" page and delete your purchase altogether.

By taking the time to clean up after yourself, you will ensure that you are not wasting any unnecessary resources or incurring additional costs. Plus, it is always good to keep things tidy and organized, right?

The final view of your "Slot commitments" view should be empty.

---

My congratulations to you! Once you get the hang of it, using a hybrid approach for your data processing needs will become a breeze. And the best part? You will be saving a ton of money for your team!

Before you call it a day, there are two important things to keep in mind:

1. First, be sure to check that your "Slot commitments" page is empty after completing your request (if you use flex slots like me). Otherwise, your reservation will continue to take up space even when you do not need it. And you will have to pay for it.

2. Secondly, when you create an assignment for a specific project, all queries for that project will use the purchased flat-rate capacity. If your colleagues are also running queries for that project, they will be placed in a queue and you may experience some wait time.

---

At our team, Data Analysts, Data Engineers, and the QA team have all learned how to use this pricing model to optimize their spending and achieve maximum efficiency. So why not give it a try and see how much you can save?