BigQuery Pricing and Reservation
In this article we are going to discuss the BigQuery pricing and reservation in BigQuery there are two models of workload management. The following models define how you manage workloads and how you are Billed for the workloads.
On-demand Pricing: By default, you will be invoiced for the data examined by the query, and your expense is determined by the volume of bytes handled by each query.
Capacity-based pricing The pricing structure is based on the capacity, which is measured in slots over a given timeframe.
For example, you might run some workloads with on-demand pricing and others with capacity-based pricing. As the billing model is specified per project, this would require that you use multiple projects for your query jobs
Commitments
A capacity commitment is a purchase of computing capacity for BigQuery for some time. Slots, a unit of processing capability, are used to measure commitments.
Reservation
BigQuery Reservations make it easier to keep track of and manage your BigQuery workloads.
Also, reservations allow you to assign slots in a way that makes sense for your business. For example, you can create BigQuery reservations named comp for your company workloads and a reservation called emp for employee workloads.
As a result, your employees’ jobs will not compete for the resources required by your company’s workload. You can also create BigQuery reservations for multiple departments.
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.
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
Assignments
The amount of slots specified by a reservation is assigned to one or more projects through assignments. When work is launched from a project assigned to a reservation, the work uses that reservation’s slots. The jobs in a project that are not allocated to a reservation use On-demand pricing.
Creating a BigQuery Reservation
Step 1: Choose a Billing Project
You have the option of using on-demand Pricing and Capacity-Based Pricing. If you use the Default on-demand pricing model, you can switch to the Capacity-based pricing.
Step 2: Create an Admin project
Only Admin Projects can create assignments, commitments, and reservations. When you create the reservations and commitments, they are linked to a Google Cloud project. The admin project is in charge of the BigQuery reservation’s resources, and it is the primary billing source for the resources.
For instance, you can give your admin project a descriptive name like hdq-ORGANIZATION_NAME-admin. Then, to hold your BigQuery jobs, create one or more independent projects.
Step 3: Estimate How many slots you have to purchase
It is essential that when buying reserved slots in BigQuery, you must figure out how many slots you’ll need for your workload. You can use a slot estimator to get an idea about slots
Step 4: Create a Commitment
In general, the more slots you buy the more concurrent queries you can execute, and faster complex queries will run
You can’t change a commitment to a different administration project once it’s been created.
By Taking an Example :
Assume you have 1,000 slots available and 3 workload types: Business, IT, and Marketing.
. Create a Business reservation with 500 slots and assign all relevant projects to the Business workload to the Business Reservation
. Create an IT reservation with 300 slots and assign all relevant projects to the IT workload to the IT Reservation
. Create a Marketing reservation with 200 slots and assign all relevant projects to the Marketing workload to the Marketing Reservation
Using Slot commitments
- When to use capacity: when you are out of the Reservation Baseline as you demand then you need an extra reservation or commitment.
For Example: As you cross the baseline of your slots you can increase the number of baseline slots in a reservation every few minutes
Baseline and Autoscaling are intended to provide capacity Based on your recent workload “If you are anticipated a large workload that is very different from your workload in the recent past, we recommend increasing your baseline capacity ahead of the event rather than rely on autoscaling slots to occur the workload capacity”
By taking the above picture reference we can relate that if the traffic is coming from higher than you expected then you can access the commitments slots to reduce the problem that you are facing or the queries
Limitations of Reservation
- Reservations that you buy cannot be shared with other organizations.
2. You must create a separate reservation and a separate administration project for each organization.
3. Idle slots are not shared between reservations of different editions.
4. Autoscaled slots are not shareable as they will scale down when no longer required
Conclusion This article gives you information about BigQuery Reservation, Assignments, Flex and commitments, how to create a reservation, and Limitations of reservation.
Note: We at Techsalo Infotech are a team of engineers solving complex Data engineering and Machine learning problems. Please reach out to us at sales@techsalo.com for any query on How to build these systems at scale and in the cloud.
