Skip to main content

BigQuery Lens

The pricing of Google Cloud BigQuery, a serverless, multi-cloud data analytics platform, mainly consists of two parts: compute pricing for the cost of processing queries, and storage pricing for the cost of storing data.

DoiT BigQuery Lens analyzes BigQuery usage and generates actionable recommendations for both compute and storage, helping you maximize the efficiency of cloud spend, improve query performance, and ensure sustainable growth as your data scales. This page is a brief introduction to DoiT BigQuery Lens.

Dataset and audit log sink

BigQuery Lens creates the following in your billing project:

  • A dataset named doitintl-cmp-bq that is created when you updated your service account with the required permissions and removed when you removed the service account.

  • An audit log sink named <Billing_Project_ID>.doitintl-cmp-bq.cloudaudit_googleapis_com_data_access for all your BigQuery jobs.

Queries run by BigQuery Lens

BigQuery Lens runs the following processes:

  • Table discovery process: Collects table metadata. This process runs on a schedule. It writes the table metadata it collected into a table named tables_discovery_v2 in the doitintl-cmp-bq dataset.

  • Dashboard update process: Updates the BigQuery Lens dashboard.

    • Daily run of cost simulation queries to provide the cost savings information and potential savings.
    • Daily run of lens calculation queries to provide detailed BigQuery usage.
  • Reservation mapping process: If you have workloads on the capacity pricing model, this process maps the edition and capacity commitments in use when any job completed. It runs once per hour and writes the data it collected into the following tables in the doitintl-cmp-bq dataset.

    • A reservations_mapping_history table that contains a list of reservations with their associated editions and the projects they're allocated to.
    • A capacity_commitments_history table that contains information about the capacity commitments that were active on that day.

BigQuery Lens cost

BigQuery Lens creates and maintains the doitintl-cmp-bq dataset on your behalf and runs queries in your billing project to provide you with analytics data.

The actual cost of running the queries depends on the volume of your logs and the compute pricing model you choose. If the billing project is assigned to a reservation, BigQuery Lens jobs use slots from the assigned reservation.

Note

If you have an excess of historical jobs, you may see an increased cost for BigQuery Streaming Inserts when we backfill them for the last 30 days during your service account setup.

Slots autoscaling

BigQuery compute capacity is measured in slots. When reserving BigQuery capacity, you can enable BigQuery editions and create a reservation or a capacity commitment associated with an edition.

If you use reservations with baseline and autoscaling slots, be aware of the following:

  • You're always charged for the baseline slots allocated to the reservation. Autoscaling slots are only added after all of the baseline slots (and idle slots if applicable) are consumed.

  • The number of slots always scales by multiples of 50, it may scale more than 50 slots within one step. You are charged for the number of scaled slots, regardless of whether they are used.

  • Autoscaled capacity is retained for at least 60 seconds (scale-down window).

When calculating BigQuery costs, we use the RESERVATIONS_TIMELINE view to track changes in reservations over time and run a reservation mapping process to find out the edition and commitments in use when a job completed.

When there are multiple jobs during an autoscaling event, we use a price based on a weighted average as jobs that have used baseline slots are not distinguished from those that have used autoscaled slots. As a result, while the overall totals are largely accurate, the cost of an individual job or reservation may not be.