Cap what each query and each month can cost

How to do it

Warehouses that charge per scan bill each query, so one SELECT * over a year of events can cost more than the month's plan. Set the limit on the account, or on each tool's connection, such as the maximum bytes billed. Partition by event date, so a query for one week scans one week. Set the account budget as Give every account a budget with alerts does.

Examples

Python example

from google.cloud import bigquery

MAX_BYTES_PER_QUERY = 10 * 1024**3

def run_report(client: bigquery.Client, sql: str):
    config = bigquery.QueryJobConfig(maximum_bytes_billed=MAX_BYTES_PER_QUERY)
    return client.query(sql, job_config=config).result()

JavaScript example

import { BigQuery } from '@google-cloud/bigquery';

const MAX_BYTES_PER_QUERY = String(10 * 1024 ** 3);

export async function runReport(sql) {
  const [job] = await new BigQuery().createQueryJob({ query: sql, maximumBytesBilled: MAX_BYTES_PER_QUERY });
  const [rows] = await job.getQueryResults();
  return rows;
}

How to check

A query on a partitioned table without a date filter is refused. A test query above the per-query limit fails before it runs. The warehouse appears in the budget alerts with its owner.

Sources

• BigQuery Best practices for controlling costs

Related rules

• INF-53 Give every account a budget with alerts in Infrastructure and environments