Reduce your BigQuery bills with BI Engine capacity orchestration

Learn how we at REEA are orchestrating BI Engine reservations by dynamically changing allocation size based on usage using Cloud Workflows on a 5TB dataset, considered small for BigQuery but with big cost savings and accelerated queries.

This article resulted from my experience at REEA, and as GDE on Stackoverflow, where I observed a lot of questions that raise problems over usage and how BI Engine works in accordion with DataStudio and BigQuery.

Introduction

By turning on BI Engine you expect accelerated queries and lower bills. In Google Cloud, everything is measurable and accessible by API, or CLI commands. Those audit logs are so helpful. So we can say, that by having access to the metrics we could achieve more optimal usage of our BI Engine reservation. The question we ask:

Is there a way to resize BigQuery BI Engine to match on-demand query usage, applying daily usage patterns using some automation?

This article covers defining a Cloud Workflow to adjust the BI Engine size parameter up/down to ensure lower costs for your BigQuery on-demand or DataStudio queries. Next, we will see how to use cloud orchestration for making this an automatic process, and combined with BigQuery and Datastudio query complexity that might…