Google has recently introduced a new pricing model for BigQuery under the name of “BigQuery flex slots”. What is this new pricing approach and when should you choose for it? This article will focus on this new pricing and on how to programmatically get slots and assign them to your project. The code available here will give you two Cloud functions that can be parameterized with number of slots desired, reservation name and assignment project.
Price is what you pay. Value is what you get.” – Warren Buffett
First things first. I will start with introducing two important concepts before deep diving into the code implementation of BigQuery Flex Slots. These two concepts are BigQuery slots and BigQuery pricing models.
BigQuery Slots
What is it?
What is a BigQuery slot? A BigQuery slot is a unit of computational capacity required to execute SQL queries. You can see it as a mix of CPU, memory and network. BigQuery automatically calculates how many slots are required by each query, depending on query size and complexity.
Maximum amount of slots
For the “on-demand” BigQuery pricing model (I’ll explain more about the different pricing models later), you have a quota of maximum 2,000 slots per project. This means that anytime, across all your queries, you will have a maximum power of 2,000 slots. This might be insufficient depending on your queries workload. In that case, you can either switch to flat-rate pricing or the new flex slot pricing. We will come back to what pricing structure to choose in the next point.
When BigQuery executes a query job, it converts the declarative SQL statement into a graph of execution, broken up into a series of query stages. These themselves are composed of more granular sets of execution steps. BigQuery leverages a heavily distributed parallel architecture to run these queries and each stage is executed by one or many workers. These workers then communicate between each other using a fast distributed shuffle architecture. The “Execution details” of a query looks like this:
This can be visualised as a Directed Acyclic Graph (DAG) where each stage is allocated some slots based on BigQuery optimisation factors (leveraging speculative execution and dynamic work rebalancing of a stage).
If a query requests more slots than what is currently available, some units of works are queued until more slots become available.
BigQuery Pricing models
BigQuery comes in three flavours as far as pricing is concerned: on-demand, flat-rate and the new flex slots pricing models. Let’s dive into each of these options so you can decide which one is best for you.
On-demand pricing
This is the BigQuery pricing model you get by default, and it works as a “pay-as-you-go” model. You have a limit of 2000 BigQuery slots that can be allocated to your queries. These slots get scheduled automatically as your queries execute.
You are billed on the amount of bytes scanned by your queries (5 USD/TB at the time of writing).
Flat-rate pricing
Once you start having many workloads running and that your “on-demand” costs start rising to the sky, it’s time to consider moving to a more enterprise-grade pricing model. Flat-rate pricing offers you the possibility to commit to a certain amount of slots (starting at 500) at 10,000 USD per 500 slots.
It’s ideal in a situation where all the projects within your organisation have a constant workload nearing a certain amount of slots. The flat-rate pricing model then lets you leverage a fixed amount of slots, to be shared by all your projects under your organisation, at a fixed price.
It is recommended to first monitor your workloads with an on-demand pricing model to get a good grasp on your slot utilisation. By exporting your BigQuery logs, you can for instance build a Data Studio dashboard that will give you the insights you need (default charts showing slot usage exist in Stackdriver Monitoring).
The example below shows the average slot consumption with the total number of queries. In this case there is clearly no reason to move away from the “on-demand” pricing model.
Flexible slots or ‘flex slots’ pricing
Between these two pricing models, there now is a new one that will fit many use cases: flexible slots or ‘flex slots’.
What is flex slots pricing?
This newly-introduced flex slots BigQuery pricing model is ideal for cyclical workloads or ad-hoc queries that require some extra power for a certain predictable amount of time.
BigQuery flex slots give you the capability to commit to a certain amount of slots, through a reservation, for a duration as short as 60 seconds (billed at $0.04 per slot per hour, at the time of writing).
Use cases where flex slots is optimal
An ideal scenario to use flex slots would be if you run daily queries that refresh some data marts every morning, and these queries take 20 minutes to run. You can then buy 2000 slots for that short amount of time every day. This will greatly decrease your costs, as flex slots are cheaper compared to on-demand pricing: see graph below.
A valuable benefit of flex slots is that you can share them between projects and folders. Imagine you have various departments and that you want to allocate a certain amount of queries to each department based on their activity.
As you can see, you buy commitments that you then allocate to reservations. After that, you finally assign reservations to either an organisation, folders or specific projects. While slots are split between these departments, for example the marketing department that only has a reservation of 200 slots could use up to 1000 slots, in case the other two departments are not processing any queries and that their 800 slots are thus sitting idle.
Cost Savings with BigQuery flex slots pricing
“Flex slots” is a real game changer in the sense that it gives you full flexibility on your costs, because you can buy and release slots on the fly. If used properly, it can decrease your BigQuery costs drastically. Below is a chart from a Google post plotting the cost of a 5 TB query:
With on-demand pricing it will cost you 15 USD (5 USD per TB), while the same query with the same amount of slots reserved through flex slots will cost you a third of it.
It is however worth noting that buying less than 2,000 slots will in most cases give you slower performance than with the on-demand pricing where you have by default 2,000 slots available.
Code to get you started with flex slots pricing
In order to maximise the advantage of flex slots pricing, you need to be able to start and stop them with ease. The code on this public github repository will jump start you with that.
It gives you two Cloud Functions: one to create BigQuery flex slots commitment, reservation and assignment and the other one to delete these resources. The create function accepts parameters and this will allow you to structure your reservations and assignments.
Once you have the functions deployed on your project using Terraform, you can easily call them within your code. For instance, you could call the “start_bq_flex” function before running a batch of queries and call the “stop_bq_flex” function after the queries are done running.
You could also call these functions through Cloud Scheduler in case you want to have a certain amount of slots always booked at a certain time period (in order to call Cloud Scheduler with authentication, see this link). The “stop_bq_flex” function at the moment removes all the assignments, reservations and commitments; there is definitely room for improvement on more granularity in targeting the resources to be deleted.
Please have a close look at the README.md as this will give you all the necessary information in order to get started with the code.
In summary: BigQuery flex slots is adding a strategic pricing model between on-demand and flat-rate pricings. This new pricing model will certainly play a crucial role in democratising Big Data analytics with BigQuery for small and medium sized companies. With a sound monitoring system to determine cyclic workloads and a programmatic way of starting and stopping flexible slots, your savings might be as far as 60% as compared to the on-demand pricing model. Hopefully this article will help you master BigQuery flex slots.
Any questions? Need guidance to optimise your BigQuery spend? Contact us!