Most BigQuery estates start the same way: someone clicks Create dataset in the console, someone else runs a CREATE TABLE in a query tab, and a year later nobody can say why analytics_prod is multi-region US while analytics_stage is us-central1, or who granted that service account roles/bigquery.dataEditor.
Infrastructure as code fixes that, and for BigQuery the tool of record is Terraform with the Google provider. This tutorial walks through codifying the pieces that actually matter — datasets, partitioned tables, views, IAM, and slot reservations — and then wiring a plan/apply pipeline that a reviewer can trust. It assumes you know BigQuery well and Terraform a little.
What belongs in Terraform, and what doesn't
A rule that saves a lot of grief: Terraform owns containers and access; your ELT tool owns data and derived objects.
Good candidates for Terraform:
- Datasets, their location, default table expiration, and labels
- Long-lived source tables and their schemas
- External / BigLake tables and connections
- Authorized views and authorized datasets
- IAM bindings at project, dataset, and (where needed) table level
- Scheduled query and Dataform repository wiring
- Reservations, capacity commitments, and reservation assignments
Leave to Dataform, dbt, or your pipeline:
- Staging and mart tables that are rebuilt by SQL
- Materialized views tied to a model's lifecycle
- Anything whose schema changes weekly with the business
If Terraform owns a table that your ELT also rewrites, every plan becomes a diff you have to explain. Draw the line once and document it in the repo README.
Project layout
A layout that scales to several environments without copy-paste:
infra/
modules/
bq_dataset/
main.tf
variables.tf
outputs.tf
envs/
prod/
main.tf
backend.tf
terraform.tfvars
stage/
main.tf
backend.tf
terraform.tfvars
schemas/
orders.json
customers.json
State goes in GCS, one prefix per environment, with versioning enabled on the bucket:
# envs/prod/backend.tf
terraform {
required_version = ">= 1.6.0"
required_providers {
google = {
source = "hashicorp/google"
version = "~> 6.0"
}
}
backend "gcs" {
bucket = "acme-terraform-state"
prefix = "bigquery/prod"
}
}
provider "google" {
project = var.project_id
region = var.region
}
Pin the provider with ~>, not >=. Google ships provider releases constantly, and an unpinned minor bump has been known to reorder or deprecate BigQuery attributes mid-sprint.
Datasets: location is forever
resource "google_bigquery_dataset" "analytics" {
dataset_id = "analytics"
friendly_name = "Analytics (curated)"
description = "Curated marts. Managed by terraform: infra/envs/prod."
location = "US"
default_partition_expiration_ms = null
delete_contents_on_destroy = false
labels = {
env = "prod"
owner = "data-platform"
cost_centre = "analytics"
}
}
Three things to internalise:
locationis immutable. Changing it forces a destroy-and-recreate of the dataset and everything in it. Decide multi-region (US,EU) versus single region (us-central1,europe-west2) up front, based on data residency and on where the services you join against live.delete_contents_on_destroy = falseis the safe default. Set it totrueonly in ephemeral sandboxes. Combine it withlifecycle { prevent_destroy = true }on production datasets.- Labels are your cost story. Slot and storage costs roll up by label in billing export, so an
env/owner/cost_centretriple applied consistently is what makes chargeback possible later.
Tables: schema in JSON, partitioning in HCL
Keep schemas in separate JSON files. They are easier to diff, and they are the same format the bq CLI emits, so you can bootstrap from an existing table with bq show --schema --format=prettyjson project:dataset.table.
resource "google_bigquery_table" "orders" {
dataset_id = google_bigquery_dataset.analytics.dataset_id
table_id = "orders"
schema = file("${path.module}/../../schemas/orders.json")
deletion_protection = true
description = "Order headers, one row per order. Loaded by Datastream CDC."
time_partitioning {
type = "DAY"
field = "order_ts"
require_partition_filter = true
}
clustering = ["country_code", "customer_id"]
labels = {
env = "prod"
}
}
Notes from the field:
deletion_protectiondefaults totruein recent provider versions, and that default has saved real data. Leave it on; flip it deliberately, in its own reviewed commit, when you genuinely intend to drop a table.require_partition_filter = trueis the single cheapest cost guardrail in BigQuery. Set it in code so nobody can quietly turn it off.- Clustering order matters — put the column you filter on most first. See our guide to partitioning versus clustering for how to choose.
- Terraform will happily apply additive schema changes (new nullable columns). Anything else — retyping, dropping, or making a column required — is a destructive change the provider will refuse or force-replace. Do those with
ALTER TABLEin SQL, then update the JSON so state and reality agree.
Iterating over many tables
When you have twenty source tables, don't write twenty blocks. Drive them from a map and for_each:
locals {
source_tables = {
orders = {
partition_field = "order_ts"
clustering = ["country_code", "customer_id"]
}
customers = {
partition_field = "created_ts"
clustering = ["country_code"]
}
}
}
resource "google_bigquery_table" "source" {
for_each = local.source_tables
dataset_id = google_bigquery_dataset.analytics.dataset_id
table_id = each.key
schema = file("${path.module}/../../schemas/${each.key}.json")
deletion_protection = true
time_partitioning {
type = "DAY"
field = each.value.partition_field
require_partition_filter = true
}
clustering = each.value.clustering
}
Use for_each with a map, never count with a list. With count, removing the first table renumbers every index and Terraform proposes destroying and recreating tables that never changed.
Access: dataset IAM without clobbering
This is where teams get burned. There are three ways to express dataset access, and mixing them corrupts your state:
| Resource | Semantics | Use when |
|---|---|---|
access blocks inside google_bigquery_dataset | Authoritative for the whole dataset | The dataset is fully Terraform-managed |
google_bigquery_dataset_access | Additive, one grant per resource | Other systems also grant access |
google_bigquery_dataset_iam_binding / _member | IAM-style, binding is authoritative per role | You standardise on IAM resources project-wide |
Pick one style per dataset. If you use inline access blocks and something else adds a grant out of band, the next apply silently removes it — which is either exactly what you want (drift correction) or an outage, depending on whether you meant it.
An authorized view, expressed additively:
resource "google_bigquery_dataset" "reporting" {
dataset_id = "reporting"
location = "US"
}
resource "google_bigquery_table" "orders_masked" {
dataset_id = google_bigquery_dataset.reporting.dataset_id
table_id = "orders_masked"
deletion_protection = false
view {
use_legacy_sql = false
query = <<-SQL
SELECT
order_id,
order_ts,
country_code,
total_amount
FROM `${var.project_id}.analytics.orders`
WHERE order_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 400 DAY)
SQL
}
}
# Let the view read the underlying dataset without granting humans access to it.
resource "google_bigquery_dataset_access" "authorize_view" {
dataset_id = google_bigquery_dataset.analytics.dataset_id
view {
project_id = var.project_id
dataset_id = google_bigquery_dataset.reporting.dataset_id
table_id = google_bigquery_table.orders_masked.table_id
}
}
For per-row and per-column control, keep the policy in SQL or in Dataplex tag templates rather than trying to model every masking rule in HCL — see row-level security, column masking, and policy tags.
Reservations: capacity as code
Slot capacity is the part of BigQuery most often managed by whoever last clicked through the reservations UI. Codifying it makes capacity changes reviewable, which matters because they cost money.
resource "google_bigquery_capacity_commitment" "annual" {
capacity_commitment_id = "annual-enterprise"
location = "US"
edition = "ENTERPRISE"
slot_count = 500
plan = "ANNUAL"
}
resource "google_bigquery_reservation" "elt" {
name = "elt"
location = "US"
edition = "ENTERPRISE"
slot_capacity = 300
autoscale {
max_slots = 600
}
ignore_idle_slots = false
}
resource "google_bigquery_reservation_assignment" "elt_project" {
location = "US"
reservation = google_bigquery_reservation.elt.id
assignee = "projects/${var.elt_project_id}"
job_type = "QUERY"
}
Two cautions. First, commitments are billed for their full term — an annual commitment created by a careless apply is a twelve-month obligation, so gate these resources behind a separate workspace or a required approval. Second, deleting a reservation assignment silently drops the assigned project back to on-demand billing, which can turn into a surprise invoice rather than an error. Model assignments explicitly and alert on them. Our BigQuery Editions guide covers how to size slot_capacity and max_slots in the first place.
Importing what already exists
You almost never start green-field. Terraform 1.5+ supports declarative import blocks, which are far safer than the old terraform import command because the import shows up in plan before anything is written to state:
import {
to = google_bigquery_dataset.analytics
id = "projects/acme-prod/datasets/analytics"
}
import {
to = google_bigquery_table.source["orders"]
id = "projects/acme-prod/datasets/analytics/tables/orders"
}
Run terraform plan -generate-config-out=generated.tf to have the provider write starter HCL for the imported objects, then prune it down to the attributes you actually want to manage. The goal for the first apply after an import is a clean, empty plan. If the plan wants to change the table you just imported, your HCL is wrong — fix the code, not the cloud.
A CI/CD pipeline that reviewers trust
The workflow that works, whatever your CI system:
- Pull request opened →
terraform fmt -check,terraform validate, thenterraform plan -lock=false -out=tfplanagainst production credentials with read-only permissions where possible. Post the plan as a PR comment. - Policy gate → fail the build automatically if the plan contains a
destroyof anygoogle_bigquery_datasetorgoogle_bigquery_table, or anygoogle_bigquery_capacity_commitmentcreate.terraform show -json tfplanplus a short script, or Conftest/OPA, is enough. - Human review → the reviewer reads the plan, not the diff. The plan is the truth.
- Merge to main →
terraform apply tfplanwith the saved plan file, using a service account that hasroles/bigquery.adminon the target project and nothing else. - Nightly drift check →
terraform plan -detailed-exitcodeon a schedule; a non-empty plan opens a ticket. Drift you discover on Tuesday morning is cheap; drift you discover during an incident is not.
Authenticate CI with Workload Identity Federation rather than a downloaded JSON key. A key in a CI secret store is a long-lived credential with bigquery.admin on your warehouse, and it will outlive the person who created it.
Traps worth knowing before they find you
- Views referencing tables Terraform creates in the same apply. The provider validates view SQL at creation time, so a view can fail because its dependency does not exist yet. Add an explicit
depends_onto the tables the view reads. schemadiff churn from formatting. The API normalises your JSON (field ordering, omittedmode: NULLABLE). If every plan shows a schema change with no semantic difference, round-trip the schema throughbq show --schemaand commit the API's own formatting.external_data_configurationand autodetect. Autodetected schemas drift as files change and produce a permanent diff. Declare external table schemas explicitly.- Dropping a
for_eachkey. Removing an entry from the map plans a table destroy. That is the correct behaviour and the reason for the policy gate in step 2. - State is as sensitive as the warehouse. Plan output can include view SQL and, in some resources, sample values. Restrict the state bucket, enable object versioning, and never print full plans into public build logs.
A 90-minute starter path
If you want the smallest useful version of all this, do it in one sitting:
- Create a GCS state bucket with versioning on and a
bigquery/devprefix. - Write one dataset resource and one partitioned table resource in a
devenvironment. terraform apply, thenterraform planagain and confirm the plan is empty.- Import a single existing production dataset with an
importblock and iterate until its plan is empty too. - Add a CI job that runs
fmt,validate, andplanon every pull request.
That is the whole hard part. Extending it to the rest of the estate is repetition, and repetition is what for_each is for.
Where this usually goes wrong
The failure mode is rarely Terraform itself; it is a half-migration. Some datasets are coded, some are clicked, the state file lives on someone's laptop, and the pipeline has -auto-approve in it. That is worse than no IaC, because the plan output stops meaning anything.
If you want a second pair of hands to draw the ownership boundary, import an existing estate without downtime, or build the policy gate that stops a stray destroy reaching production, BQBrains does exactly this work — see BigQuery Data Architecture Consulting and BigQuery Data Governance and Compliance, or just get in touch with a description of your current setup.