With Rabbit, Aliz’s vision is to bring transparency to cloud costs, helping businesses get the most value out of their cloud use. To make a real business impact from day one, we have identified key cost factors, and are offering features to help optimize those areas first. We’re happy to introduce Rabbit’s new functionality to optimize BigQuery use, one of the major cost drivers for cloud-mature companies!

Background: understanding BigQuery’s pricing

In essence, BigQuery’s pricing is based on two main components: (1) processing data and (2) storing data.

(1) You’re paying for each query processed, including SQL queries, user-defined functions, and scripts, but also certain data manipulation language and data definition language statements that scan tables. Overall, BigQuery costs change on a query-by-query basis. This variability makes it difficult to arrive at an accurate price per query, something that would make analysis, budgeting & planning easier. The cost of suboptimal queries (for example, if lots of automated queries are set up to run periodically) can build up fast.

(2) There’s also the cost of storing data. There is no flat rate, making matters yet more complicated. While BigQuery’s prices decrease for tables that are unused for long periods of time (going down to as much as 50% of the original price after 90 days), even these data storage costs are higher than relying on cloud storage instead, leaving room for optimization.

Working with a number of large companies that operate huge data pipelines has enabled Aliz to build up a thorough understanding of how BigQuery’s costs can add up. With Rabbit, we leverage that knowledge to make BigQuery costs transparent for you, opening up the potential for significant cost savings.

How does Rabbit’s BigQuery optimization work?

There are two main ways Rabbit uses BigQuery insights to unlock that optimization potential for your company:

1. Group similar queries together by filtering out parameters and then comparing queries. Let’s give you a quick example: if you’re running a query on a daily basis, with the only difference between two queries being a change in the date filter, you can easily bundle these queries together. They are essentially similar queries, so there’s nothing stopping you from rolling them into one group to aggregate their costs.
2. Deep query analysis to find suboptimal queries and provide recommendations for cost-conscious queries. Queries are generally created from a functional or need-based point of view, with query costs being a secondary concern at best. Through a deep analysis of your queries, Rabbit can provide recommendations for optimization with costs being a priority (but without affecting functionality). For instance, we may give you the recommendation to partition and/or cluster tables and change the filter order based on clustered columns.

Rabbit provides a granular three-way view into Account, Query, and Tables data. Queries are grouped and listed together with their costs for clarity and transparency. You can check who ran the query, and Rabbit will also show you which tables were queried. With DBT integrated, queries can also be filtered by DBT labels.

Accounts lists the users running queries along with the cost of those queries. Rabbit enables users to get a transparent overview of how much those BigQuery Queries are costing the company.

Tables lets you analyze who ran which query, and also provides valuable insights into whether a table’s columns are mostly used in the SELECT clause or the WHERE clause. This enables the further optimization of clustering or partitioning to increase speed and cost efficiency in your tables.

Finally, Rabbit also provides recommendations for storing data. If Rabbit detects a table that is not actively used, it may suggest that you move that data to Google Cloud Storage to save costs. In the case of multiple terabytes of data, storing all that data in BigQuery tables can be a significant cost driver – moving data to cloud storage can result in a fair amount of cost savings.

Streamlining BigQuery costs with Rabbit

Rabbit has already helped one of our customers cut unnecessarily high BigQuery costs. In this particular case, the company realized they were updating a report daily – even though that report was only checked once a week. Optimizing the query allowed them to shave off a bit of their BigQuery costs.

Overall, Rabbit is able to aggregate and analyze your BigQuery data even from a multi-region setup, and provides actionable recommendations that enable you to optimize and control your BigQuery costs. Ready to unlock cost transparency in your BigQuery spending? Sign up for Rabbit!