BigQuery Reservations: Managing Workloads, Commitments Demystified

on Data Analytics, Data Integration, Data Warehouse, Google BigQuery, Tutorials • March 22nd, 2022 • Write for Hevo

BigQuery Reservations Feature Image

Welcome to the BigQuery reservations tutorial. For many people, Data Analytics has progressed from a side project to a vital part of their operations. BigQuery reservations simplify spending and give your Data Analytics use cases more flexibility and visibility. In addition, BigQuery reservations make it easy to manage your workload and commitments.

Another benefit is that you can switch between the 2 pricing models: On-demand pricing and Flat-rate pricing. This tutorial will help you understand what BigQuery reservations and the pricing models entail. You will also learn how to use BigQuery reservations to manage workloads and commitments.

Table of Contents

Prerequisites

  • Google Cloud platform
  • A BigQuery billing model

What is BigQuery?

BigQuery Reservations: BigQuery
Image Source

BigQuery is Google Cloud’s Serverless Data Warehouse designed to help manage and analyze your data. Its notion of Data Warehousing as a service and pay-as-you-go transformed the market. It is built to handle Petabytes of data and can automatically scale as your business flourishes. Developers at Google have designed its architecture keeping the storage and computing resources separate. This makes querying more fluid as you can scale them independently.

Using standard SQL, you can accurately analyze your data and execute complex queries from multiple users simultaneously.

Simplify BigQuery Data Analysis with Hevo’s No-code Data Pipeline

Hevo Data is a No-code Data Pipeline that offers a fully managed solution to set up data integration from 100+ Data Sources (including 30+ Free Data Sources) and will let you directly load data to a Data Warehouse like BigQuery to be visualized in a BI tool. It will automate your data flow in minutes without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully automated solution to manage data in real-time and always have analysis-ready data.

Get started with hevo for free

Let’s look at some of the salient features of Hevo:

  • Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
  • Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
  • Schema Management: Hevo can automatically detect the schema of the incoming data and map it to the destination schema.
  • Scalable Infrastructure: Hevo has in-built integrations for 100’s of sources that can help you scale your data infrastructure as required.
  • Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within Data Pipelines.
  • Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
Sign up here for a 14-day free trial!

BigQuery Pricing Model

For analytics, BigQuery offers 2 pricing options:

  • On-demand Pricing:  On-demand billing is efficient. By default, you will be billed according to the On-demand pricing model. This pricing model allows you to pay for all the data scanned by your queries. With this model, you have a fixed, per-project query capacity. Furthermore, the cost is determined by the number of bytes read. You also have access to up to 2000 burstable slots of computing power per project. 
  • Flat-rate Pricing: This billing model is for people who want a consistent and stable fee for their queries. You’ll need to purchase a dedicated query processing capacity of 100-slot commitments in either flex (by-minute), monthly, or yearly plans. This billing scheme is well-used because it’s predictable.

Factors to Consider when Choosing a Pricing Model

  • Cost: As the amount of workload and data scanned by your queries keep rising, so does the cost. If you want to be billed on the amount of data scanned by your queries, you can go for the On-demand model. In contrast, if you want a more stable and fixed price for your data scanning, the Flat-rate model might be the better option for you. 
  • Efficiency: The On-demand model is deemed more efficient because it can quickly access over 2000 burstable slots that process data. On the other hand, the Flat-rate billing model is more predictable because you pay a set fee regardless of how much data you process.
  • Predictability: If you want to have control over the predictability of the monthly value of your billing plan, it is recommended you go for the Flat-rate model.
  • Resources: Before deciding to continue with the On-board billing or switch over to the Flat-rate model, determine the amount of slot you want to use for your data query, the throughput, how you want the performance to be, and the utility required.
BigQuery Reservations: Slots and Commitments
Image Source

Flat-rate billing entails purchasing a commitment to acquire a specific number of BigQuery slots. When you buy a slot commitment for the first time, BigQuery creates a reservation and allocates it to your whole Google Cloud Organization. Note that commitments are bought in units of BigQuery slots. The Flat-rate price includes the cost of all bytes processed.

BigQuery Reservations

BigQuery Reservations make it easier to keep track of and manage your BigQuery workloads. A reservation is a distinct pool of resources that has the extra benefit of allowing you to tap into idle capacity throughout your whole organization. It can also be described as a pool formed as a result of a commitment.

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.

It’s worth noting that when you buy slots, a reservation called “DEFAULT” is created automatically. You can choose to make additional reservations or go with the default.

Let’s understand the various important terminologies associated with BigQuery reservations.

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.

Commitments

A capacity commitment is a purchase of computing capacity for BigQuery for a period of time. Slots, a unit of processing capability, are used to measure commitments. 

BigQuery Reservations: Commitments, Reservations, and Assignments
Image Source

Slots

A slot is a virtual CPU that SQL queries are executed with. Depending on query size and complexity, BigQuery automatically calculates how many slots each query takes.

You can choose between a Flat-rate pricing approach and an On-demand pricing model to purchase the slots. Both of these pricing methods rely on slots to process data. In contrast to the on-demand strategy, the Flat-rate model gives you explicit control over slots and analytics capacity.

BigQuery Reservations: Slots
Image Source

Resource Hierarchy

Unless overridden, each level in the GCP resource hierarchy inherits the assignment from the one above it.

Now that you have grasped the fundamentals of BigQuery reservations, it’s time to manage the workload.

Getting Started with BigQuery Reservations

Follow the below-mentioned steps to get started with BigQuery reservations.

Step 1: Choose a Billing Model

You have the option of using an On-demand or Flat-rate model. If you’re using the default On-demand model, you can switch to the Flat-rate model or combine the 2 models. The 2 models are mutually beneficial and complement each other.

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.

This project does not have to be the same as the one where your BigQuery tasks are stored. For BigQuery Reservations resources, Google advises having a separate project. Because it centralizes the billing and monitoring of your commitments, this project is termed the administration project.

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

BigQuery is designed to scale linearly as resources are added. Depending on your workload, total capacity is likely to provide incremental benefits. As a result, based on your performance, throughput, and utility requirements, determine the appropriate amount of slots to purchase.

BigQuery Reservations: Slots Estimation
Image source

Note: 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 the slot estimator to examine performance and model the effect of adding or reducing slots. The slot estimator can be used for both Flat-rate and On-demand billing.

Step 4: Create a Commitment

Purchase dedicated slots to accomplish this. In general, the more slots you buy, the more concurrent queries you can execute, and the faster complex queries will run. Also, the lower the price per slot, the longer the commitment.

There are numerous commitment plans from which to pick. They differ mostly in terms of price and the length of time you want for the commitment.

Note: You can’t change a commitment to a different administration project once it’s been created.

Step 5: Assign the Slots to a Project

You must assign one or more projects, folders, or organizations to a reservation in order to use the slots you’ve purchased.

Note that the assignment from the level above is passed down to each level in the resource hierarchy. If a project or folder is not allocated, it will automatically share slots with other projects and folders in your organization.

Example

Assume you have 1,500 slots available and 4 workload types: Web Design, Graphics Design, E-commerce, and Content Writing. You can:

  • Create a WD reservation with 700 slots, and assign all relevant projects to the WD reservation.
  • Create a GD reservation with 400 slots, and assign projects you use for Graphics Design workloads to the GD reservation.
  • Create an EC reservation with 200 slots, and assign projects connected to your E-commerce workloads to the EC reservation.
  • Create a CW reservation with 200 slots, and assign projects connected to your Content Writing workloads to the bi reservation.

You can isolate your commitment across workloads, teams, or departments by creating extra reservations and assigning projects to these reservations.

Conclusion

In this tutorial, you have learned about the important BigQuery reservations terminologies, the 2 pricing models, and how to manage workload using BigQuery Reservations. The idea behind reservations is to allocate slots to a set of projects. Then, assign the workloads on these projects based on the selected pricing model and the required slots.

It’s time to start experimenting with BigQuery reservations for improved efficiency! However, as a Developer, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, Marketing Platforms to BigQuery can be quite challenging. If you are from non-technical background or are new in the game of data warehouse and analytics, Hevo Data can help!

visit our website to explore hevo

Hevo Data with its strong integration with 100+ Sources & BI tools allows you to not only export data from sources & load data in the destinations like BigQuery, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools.

Give Hevo Data a try and sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs, check them out!

Share your experience of working with BigQuery reservations in the comments section below.

No-code Data Pipeline for Google BigQuery