Terraform Module: GCP BigQuery Reservation — predictable, slot-based BigQuery cost control

Quick take — Provision BigQuery slot reservations as code with a reusable Terraform module: capacity commitments, reservations, and assignments wired for Editions-based autoscaling and predictable query cost. New here? Jump to the Quickstart below to deploy it in minutes; read on for how it works and when to reach for it.

Quickstart (copy-paste)

Minimal, runnable configuration — drop this in a .tf file and fill in the "..." placeholders (each required input is commented):

provider "google" {
  project = "my-project"
  region  = "us-central1"
}

module "bigquery_reservation" {
  source = "git::https://dev.azure.com/teknohut/kloudvin/_git/terraform-modules//terraform-module-gcp-bigquery-reservation?ref=v1.0.0"

  project_id = "..."  # Admin/billing project that owns the reservation and com…
  location   = "..."  # BigQuery admin location (`US`, `EU`, or a region like `…
  name       = "..."  # Reservation name (lowercase, digits, dashes; unique per…
}

Then terraform init && terraform apply. Every other input has a sensible default — see Inputs below to override behaviour.

What this module is

BigQuery bills queries one of two ways: on-demand (you pay per TB scanned) or capacity-based (you buy dedicated compute measured in slots). Once your scan volume becomes large or spiky, on-demand pricing turns unpredictable and expensive. BigQuery Reservations are the capacity-based model: you purchase slots, group them into a reservation, and assign projects, folders, or your whole org to that reservation so their jobs draw from the pooled slots instead of the on-demand pool.

In the current Editions model there are three resources that work together:

• google_bigquery_capacity_commitment — a time-bound (or flex) purchase of a slot count under an edition (STANDARD, ENTERPRISE, or ENTERPRISE_PLUS). This is the billing commitment.
• google_bigquery_reservation — a named pool of slots with a baseline (always-on) and an autoscale.max_slots (burst ceiling). Jobs assigned here run on these slots.
• google_bigquery_reservation_assignment — binds a consumer (project/folder/org) and a job_type (QUERY, PIPELINE, ML_EXTERNAL, BACKGROUND) to a reservation.

This module is google_bigquery_reservation-centric and optionally provisions the commitment and one or more assignments alongside it, so a single module block stands up the full “buy slots → pool them → point workloads at them” chain. Wrapping it makes the slot math, edition choice, idle-slot policy, and assignment wiring consistent and reviewable instead of clicked together in the console.

When to use it

• Your on-demand bytes-scanned bill is high or unpredictable and you want a fixed/known monthly compute floor with a controlled burst ceiling.
• You need to isolate workloads: give the BI/dashboard project guaranteed baseline slots while batch ELT bursts via autoscale, so a 3 TB backfill never starves Looker dashboards.
• You want org- or folder-level capacity governance — assign a whole folder to a reservation once instead of per-project.
• You are standardizing BigQuery on Editions (Standard/Enterprise/Enterprise Plus) and want the edition, baseline, and autoscale ceiling captured in code and code review.
• Skip it if you run light, infrequent analytics — plain on-demand is cheaper and needs no reservation at all. Reservations only pay off above a sustained slot-hour threshold.

Module structure

terraform-module-gcp-bigquery-reservation/
├── versions.tf
├── main.tf
├── variables.tf
└── outputs.tf

versions.tf

terraform {
  required_version = ">= 1.5.0"

  required_providers {
    google = {
      source  = "hashicorp/google"
      version = "~> 5.0"
    }
  }
}

main.tf

locals {
  # Reservations live in a specific BQ admin region (e.g. "US", "EU", "asia-south1").
  reservation_location = var.location

  # Only create assignment objects for entries that actually target a consumer.
  assignments = {
    for a in var.assignments : "${a.job_type}-${a.assignee_id}" => a
  }
}

# (Optional) Buy the slots. A reservation can only allocate baseline slots that
# are backed by a capacity commitment in the same location, except when you rely
# purely on autoscale (baseline = 0).
resource "google_bigquery_capacity_commitment" "this" {
  count = var.create_capacity_commitment ? 1 : 0

  project           = var.project_id
  location          = local.reservation_location
  slot_count        = var.commitment_slot_count
  plan              = var.commitment_plan
  edition           = var.edition
  renewal_plan      = var.commitment_plan == "ANNUAL" ? var.commitment_renewal_plan : null
  capacity_commitment_id = var.capacity_commitment_id
}

resource "google_bigquery_reservation" "this" {
  project  = var.project_id
  location = local.reservation_location
  name     = var.name
  edition  = var.edition

  # Always-on slots. Must be a multiple of 50 for Enterprise/Enterprise Plus, 100 for Standard.
  slot_capacity = var.baseline_slots

  # When true, queries can use idle slots from OTHER reservations in the same
  # admin project/location. Set false to hard-isolate this workload.
  ignore_idle_slots = var.ignore_idle_slots

  # Burst ceiling above the baseline. Set max_slots = 0 (omit autoscale) for a
  # fixed-size reservation with no autoscaling.
  dynamic "autoscale" {
    for_each = var.autoscale_max_slots > 0 ? [1] : []
    content {
      max_slots = var.autoscale_max_slots
    }
  }

  # Optional: cap concurrent queries (0 = BigQuery decides automatically).
  concurrency = var.concurrency

  # Ensure slots exist before the reservation tries to allocate a baseline.
  depends_on = [google_bigquery_capacity_commitment.this]
}

# Point one or more consumers (project/folder/org) at the reservation.
resource "google_bigquery_reservation_assignment" "this" {
  for_each = local.assignments

  project     = var.project_id
  location    = local.reservation_location
  reservation = google_bigquery_reservation.this.id
  assignee    = each.value.assignee_id
  job_type    = each.value.job_type
}

variables.tf

variable "project_id" {
  type        = string
  description = "Administration project that owns the reservation and commitment (the billing/admin project, not necessarily the consumer)."
}

variable "location" {
  type        = string
  description = "BigQuery admin location for the reservation, e.g. \"US\", \"EU\", or a region like \"asia-south1\"."
}

variable "name" {
  type        = string
  description = "Reservation name. Lowercase letters, numbers and dashes; must be unique within the location."

  validation {
    condition     = can(regex("^[a-z][a-z0-9-]{0,62}$", var.name))
    error_message = "name must start with a lowercase letter and contain only lowercase letters, numbers, and dashes (max 63 chars)."
  }
}

variable "edition" {
  type        = string
  description = "BigQuery edition for both the reservation and commitment."
  default     = "ENTERPRISE"

  validation {
    condition     = contains(["STANDARD", "ENTERPRISE", "ENTERPRISE_PLUS"], var.edition)
    error_message = "edition must be one of STANDARD, ENTERPRISE, or ENTERPRISE_PLUS."
  }
}

variable "baseline_slots" {
  type        = number
  description = "Always-on (baseline) slot capacity. Use 0 to run purely on autoscale. Standard increments by 100; Enterprise/Enterprise Plus by 50."
  default     = 0

  validation {
    condition     = var.baseline_slots >= 0 && var.baseline_slots % 50 == 0
    error_message = "baseline_slots must be >= 0 and a multiple of 50."
  }
}

variable "autoscale_max_slots" {
  type        = number
  description = "Maximum additional slots the reservation may autoscale to above the baseline. 0 disables autoscaling."
  default     = 0

  validation {
    condition     = var.autoscale_max_slots >= 0 && var.autoscale_max_slots % 50 == 0
    error_message = "autoscale_max_slots must be >= 0 and a multiple of 50."
  }
}

variable "ignore_idle_slots" {
  type        = string
  description = "If \"true\", queries may borrow idle slots from other reservations in the same admin project/location. \"false\" hard-isolates this reservation's capacity."
  default     = "false"

  validation {
    condition     = contains(["true", "false"], var.ignore_idle_slots)
    error_message = "ignore_idle_slots must be the string \"true\" or \"false\"."
  }
}

variable "concurrency" {
  type        = number
  description = "Target maximum concurrent queries for this reservation. 0 lets BigQuery choose automatically."
  default     = 0

  validation {
    condition     = var.concurrency >= 0
    error_message = "concurrency must be >= 0 (0 = automatic)."
  }
}

# ---- Capacity commitment (optional) -----------------------------------------

variable "create_capacity_commitment" {
  type        = bool
  description = "Whether to purchase a capacity commitment to back the baseline slots. Set false if baseline_slots = 0 (pure autoscale) or the commitment is managed elsewhere."
  default     = false
}

variable "capacity_commitment_id" {
  type        = string
  description = "Optional explicit ID for the capacity commitment. If null, BigQuery generates one."
  default     = null
}

variable "commitment_slot_count" {
  type        = number
  description = "Slot count to commit to when create_capacity_commitment is true. Must be >= baseline_slots and a multiple of 50."
  default     = 0

  validation {
    condition     = var.commitment_slot_count >= 0 && var.commitment_slot_count % 50 == 0
    error_message = "commitment_slot_count must be >= 0 and a multiple of 50."
  }
}

variable "commitment_plan" {
  type        = string
  description = "Commitment plan length."
  default     = "ANNUAL"

  validation {
    condition     = contains(["FLEX", "MONTHLY", "ANNUAL", "THREE_YEAR"], var.commitment_plan)
    error_message = "commitment_plan must be one of FLEX, MONTHLY, ANNUAL, or THREE_YEAR."
  }
}

variable "commitment_renewal_plan" {
  type        = string
  description = "Renewal plan applied when an ANNUAL/THREE_YEAR commitment expires. Use \"FLEX\" to avoid auto-renewing into another long-term commitment."
  default     = "FLEX"

  validation {
    condition     = contains(["FLEX", "MONTHLY", "ANNUAL", "THREE_YEAR", "NONE"], var.commitment_renewal_plan)
    error_message = "commitment_renewal_plan must be one of FLEX, MONTHLY, ANNUAL, THREE_YEAR, or NONE."
  }
}

# ---- Assignments ------------------------------------------------------------

variable "assignments" {
  type = list(object({
    assignee_id = string # e.g. "projects/my-prod", "folders/1234567890", "organizations/9876543210"
    job_type    = string # QUERY | PIPELINE | ML_EXTERNAL | BACKGROUND
  }))
  description = "Consumers bound to this reservation and the job type each binding covers."
  default     = []

  validation {
    condition = alltrue([
      for a in var.assignments :
      contains(["QUERY", "PIPELINE", "ML_EXTERNAL", "BACKGROUND"], a.job_type)
    ])
    error_message = "Each assignment job_type must be one of QUERY, PIPELINE, ML_EXTERNAL, or BACKGROUND."
  }
}

outputs.tf

output "reservation_id" {
  description = "Fully-qualified reservation ID (projects/<p>/locations/<l>/reservations/<name>)."
  value       = google_bigquery_reservation.this.id
}

output "reservation_name" {
  description = "Short reservation name."
  value       = google_bigquery_reservation.this.name
}

output "baseline_slots" {
  description = "Baseline (always-on) slot capacity configured on the reservation."
  value       = google_bigquery_reservation.this.slot_capacity
}

output "autoscale_max_slots" {
  description = "Maximum autoscale slots above the baseline (0 if autoscaling is disabled)."
  value       = var.autoscale_max_slots
}

output "edition" {
  description = "BigQuery edition of the reservation."
  value       = google_bigquery_reservation.this.edition
}

output "capacity_commitment_id" {
  description = "ID of the created capacity commitment, or null when none was created by this module."
  value       = try(google_bigquery_capacity_commitment.this[0].id, null)
}

output "assignment_ids" {
  description = "Map of \"<job_type>-<assignee>\" to the assignment resource name."
  value       = { for k, a in google_bigquery_reservation_assignment.this : k => a.name }
}

How to use it

module "bigquery_reservation" {
  source = "git::https://dev.azure.com/teknohut/kloudvin/_git/terraform-modules//terraform-module-gcp-bigquery-reservation?ref=v1.0.0"

  project_id = "kv-data-admin"
  location   = "asia-south1"
  name       = "analytics-prod"
  edition    = "ENTERPRISE"

  # 200 always-on slots, allowed to burst to +300 (500 total) for batch ELT.
  baseline_slots      = 200
  autoscale_max_slots = 300
  ignore_idle_slots   = "false"

  # Back the 200 baseline slots with a 1-year commitment for the discount.
  create_capacity_commitment = true
  commitment_slot_count      = 200
  commitment_plan            = "ANNUAL"
  commitment_renewal_plan    = "FLEX"

  # Send the prod analytics project's queries here; pipelines from the ELT project too.
  assignments = [
    {
      assignee_id = "projects/kv-analytics-prod"
      job_type    = "QUERY"
    },
    {
      assignee_id = "projects/kv-elt-prod"
      job_type    = "PIPELINE"
    },
  ]
}

# Downstream: surface the reservation ID to a monitoring alert policy so on-call
# is paged when this specific reservation's slot utilization stays pinned high.
resource "google_monitoring_alert_policy" "slot_saturation" {
  project      = "kv-data-admin"
  display_name = "BQ reservation ${module.bigquery_reservation.reservation_name} slots saturated"
  combiner     = "OR"

  conditions {
    display_name = "Slot utilization > 90%"
    condition_threshold {
      filter = join("", [
        "resource.type=\"bigquery_project\" AND ",
        "metric.type=\"bigquery.googleapis.com/slots/allocated_for_reservation\" AND ",
        "resource.label.\"reservation_id\"=\"${module.bigquery_reservation.reservation_name}\"",
      ])
      comparison      = "COMPARISON_GT"
      threshold_value = 0.9
      duration        = "600s"
      aggregations {
        alignment_period   = "300s"
        per_series_aligner = "ALIGN_MEAN"
      }
    }
  }
}

With Terragrunt

Terragrunt keeps this module DRY across environments — define the backend and provider once in a root config, then a thin terragrunt.hcl per environment supplies only the inputs that differ.

1. Root config — live/terragrunt.hcl (inherited by every module):

remote_state {
  backend = "gcs"
  generate = { path = "backend.tf", if_exists = "overwrite" }
  config = {
    # ...gcs state bucket/container + key per path...
  }
}

2. Module config — live/prod/bigquery_reservation/terragrunt.hcl:

include "root" {
  path = find_in_parent_folders()
}

terraform {
  source = "git::https://dev.azure.com/teknohut/kloudvin/_git/terraform-modules//terraform-module-gcp-bigquery-reservation?ref=v1.0.0"
}

inputs = {
  project_id = "..."
  location = "..."
  name = "..."
}

3. Deploy one environment, or roll out all modules together:

cd live/prod/bigquery_reservation && terragrunt apply        # this module
terragrunt run-all apply                      # every module under live/prod

Why Terragrunt here: the backend and provider live in one place instead of being copy-pasted into every module; inputs is overridden per environment (dev / stage / prod) without forking the module; and run-all orchestrates dependencies across modules. Reach for it once you have more than one environment or more than a handful of modules — for a single stack, the plain Quickstart above is enough.

Inputs

Outputs

Enterprise scenario

A retail analytics platform runs Looker dashboards and a nightly dbt pipeline against the same BigQuery data, and their on-demand bill swung from ₹4L to ₹11L month-to-month with the ELT backfills. They deploy this module twice in asia-south1: a bi-prod reservation with 200 baseline Enterprise slots, ignore_idle_slots = "false", assigned to the BI project’s QUERY jobs so dashboards always have guaranteed capacity; and an elt-batch reservation with baseline_slots = 0 plus autoscale_max_slots = 600, assigned to the dbt project’s PIPELINE jobs so backfills burst on cheap autoscale slots and drop to zero overnight. A 1-year capacity commitment backs the BI baseline for the committed-use discount, turning a volatile six-figure on-demand line into a predictable floor plus controlled burst.

Best practices

• Separate baseline from burst. Give latency-sensitive BI a non-zero baseline_slots with ignore_idle_slots = "false" so dashboards never wait, and run batch ELT in a separate reservation with baseline_slots = 0 + autoscale so it scales to zero when idle. Never let an unbounded backfill share the BI baseline.
• Right-size from INFORMATION_SCHEMA.JOBS before committing. Query historical total_slot_ms to model the slot-hours you actually use, then set baseline_slots near the p50 and autoscale_max_slots near the p95 — don’t guess. Re-check quarterly.
• Use FLEX renewal on long-term commitments. Set commitment_renewal_plan = "FLEX" so an ANNUAL/THREE_YEAR commitment doesn’t silently auto-renew into another lock-in; you re-commit deliberately after reviewing usage.
• Cap the blast radius with autoscale_max_slots. Autoscale slots bill per slot-hour while active — an unbounded ceiling can quietly outspend on-demand. Always set an explicit, reviewed max.
• Assign at the right scope. Bind a whole folder once for org-wide governance, but assign critical prod projects individually so their QUERY capacity is explicit and not accidentally inherited or overridden.
• Name and locate consistently. Encode environment and workload in name (e.g. bi-prod, elt-batch) and remember reservations are per-admin-location — keep the reservation location aligned with the datasets it serves to avoid cross-region surprises, and protect the admin project’s bigquery.reservationAdmin role tightly since it controls real spend.