BigQuery Multi-Statement Transactions: Scripting, Variables, and Stored Procedures

Standard SQL is declarative: you describe what you want, not how to get it. That works well for a single SELECT or INSERT. But real data engineering often involves conditional logic, loops, and operations that need to succeed or fail together. BigQuery procedural SQL fills that gap without requiring Python or an external orchestration layer.

This guide covers variables, control flow, multi-statement transactions, and stored procedures — the features that let you write real programs inside BigQuery.

DECLARE and SET: Script Variables

Variables in BigQuery scripts follow a strict scoping model. You declare them at the top of a script block and set them with SET or during initialization.

DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);
DECLARE end_date DATE DEFAULT CURRENT_DATE();
DECLARE row_count INT64;

SET row_count = (
  SELECT COUNT(*)
  FROM my_dataset.orders
  WHERE order_date BETWEEN start_date AND end_date
);

SELECT CONCAT('Rows in last 30 days: ', CAST(row_count AS STRING));

Variables are typed. You cannot assign a STRING to an INT64 variable. The type must be declared explicitly or inferred from the DEFAULT expression.

IF / ELSEIF / ELSE: Conditional Logic

BigQuery IF blocks work like most procedural languages. The condition is a boolean expression, and each branch can contain multiple SQL statements.

DECLARE sales_total FLOAT64;

SET sales_total = (
  SELECT SUM(amount) FROM my_dataset.daily_sales
  WHERE sale_date = CURRENT_DATE()
);

IF sales_total > 100000 THEN
  INSERT INTO my_dataset.alerts (alert_type, created_at)
  VALUES ('HIGH_SALES_DAY', CURRENT_TIMESTAMP());
ELSEIF sales_total < 1000 THEN
  INSERT INTO my_dataset.alerts (alert_type, created_at)
  VALUES ('LOW_SALES_ALERT', CURRENT_TIMESTAMP());
ELSE
  -- Normal range, no action needed
  SELECT 'Sales within normal range' AS status;
END IF;

LOOP and WHILE: Iteration

Loops let you process data iteratively. BigQuery supports both LOOP (with explicit BREAK) and WHILE (condition-checked before each iteration).

-- Process months one at a time
DECLARE current_month DATE DEFAULT '2024-01-01';
DECLARE last_month DATE DEFAULT '2024-12-01';

WHILE current_month <= last_month DO
  INSERT INTO my_dataset.monthly_summaries
  SELECT
    current_month AS report_month,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM my_dataset.orders
  WHERE DATE_TRUNC(order_date, MONTH) = current_month;

  SET current_month = DATE_ADD(current_month, INTERVAL 1 MONTH);
END WHILE;

Be careful with loops that scan large tables on each iteration — each iteration triggers a full scan against BigQuery’s billing model. Consider whether a single query with GROUP BY would be cheaper.

Multi-Statement Transactions: BEGIN and COMMIT

A multi-statement transaction groups several DML operations so they either all succeed or all roll back together. This is essential when you need to update multiple related tables and keep them consistent.

BEGIN TRANSACTION;

-- Deduct from source account
UPDATE my_dataset.accounts
SET balance = balance - 500.00
WHERE account_id = 'ACC-001';

-- Add to destination account
UPDATE my_dataset.accounts
SET balance = balance + 500.00
WHERE account_id = 'ACC-002';

-- Log the transfer
INSERT INTO my_dataset.transfer_log
  (from_account, to_account, amount, transferred_at)
VALUES
  ('ACC-001', 'ACC-002', 500.00, CURRENT_TIMESTAMP());

COMMIT TRANSACTION;

If any statement inside the transaction fails, the entire block rolls back automatically. You can also issue an explicit ROLLBACK TRANSACTION based on a condition check.

BEGIN TRANSACTION
    │
    ├─ UPDATE accounts (debit)   ─── fails here
    │                                   │
    │                              ROLLBACK
    │                              all changes undone
    │
    └─ (on success) COMMIT

EXCEPTION and Error Handling

Scripts can catch errors using EXCEPTION WHEN ERROR THEN. This prevents a runtime failure from surfacing as an unhandled error and lets you log it or take remedial action.

BEGIN
  DECLARE error_message STRING;

  BEGIN TRANSACTION;

  UPDATE my_dataset.inventory
  SET quantity = quantity - 10
  WHERE product_id = 'SKU-999';

  COMMIT TRANSACTION;

EXCEPTION WHEN ERROR THEN
  SET error_message = @@error.message;
  ROLLBACK TRANSACTION;

  INSERT INTO my_dataset.error_log (error_msg, failed_at)
  VALUES (error_message, CURRENT_TIMESTAMP());
END;

Stored Procedures: Reusable Script Blocks

Stored procedures let you package a script into a named, callable unit. Arguments can be IN (read-only), OUT (write-only), or INOUT.

CREATE OR REPLACE PROCEDURE my_dataset.refresh_daily_summary(
  IN report_date DATE,
  OUT rows_inserted INT64
)
BEGIN
  DELETE FROM my_dataset.daily_summary
  WHERE summary_date = report_date;

  INSERT INTO my_dataset.daily_summary (summary_date, total_orders, total_revenue)
  SELECT
    report_date,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue
  FROM my_dataset.orders
  WHERE order_date = report_date;

  SET rows_inserted = @@row_count;
END;

Call the procedure from another script:

DECLARE inserted INT64;

CALL my_dataset.refresh_daily_summary(CURRENT_DATE(), inserted);

SELECT CONCAT('Inserted: ', CAST(inserted AS STRING), ' rows') AS result;

Real-World Pattern: Idempotent Daily ETL

A common production pattern combines variables, conditional logic, and a transaction to build an idempotent daily load — safe to re-run without duplicating data.

DECLARE target_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
DECLARE already_loaded BOOL;

SET already_loaded = (
  SELECT COUNT(*) > 0
  FROM my_dataset.load_log
  WHERE load_date = target_date AND status = 'SUCCESS'
);

IF NOT already_loaded THEN
  BEGIN TRANSACTION;

  INSERT INTO my_dataset.facts (order_id, customer_id, amount, order_date)
  SELECT order_id, customer_id, amount, order_date
  FROM my_dataset.orders_staging
  WHERE order_date = target_date;

  INSERT INTO my_dataset.load_log (load_date, rows_loaded, status, loaded_at)
  SELECT target_date, @@row_count, 'SUCCESS', CURRENT_TIMESTAMP();

  COMMIT TRANSACTION;
ELSE
  SELECT 'Already loaded, skipping' AS status;
END IF;

Limits and Considerations

┌─────────────────────────────────────┬──────────────────────────────┐
│ Limit                               │ Value                        │
├─────────────────────────────────────┼──────────────────────────────┤
│ Max script statement count          │ 1,000 statements per script  │
│ Max nesting depth (IF, LOOP, etc.)  │ 50 levels                    │
│ Max script bytes                    │ 1 MB                         │
│ Variable types supported            │ All BigQuery types           │
│ DML inside transactions             │ UPDATE, INSERT, DELETE, MERGE│
│ DDL inside transactions             │ Not supported                │
└─────────────────────────────────────┴──────────────────────────────┘

DDL statements like CREATE TABLE and CREATE VIEW cannot run inside BEGIN TRANSACTION / COMMIT TRANSACTION blocks. If you need DDL as part of a script, run it outside the transaction boundary.

Summary

BigQuery scripting brings control flow to SQL without requiring an external orchestration layer. Variables track state, IF/WHILE blocks add conditional and iterative logic, and multi-statement transactions guarantee atomicity across DML operations. Stored procedures package that logic into reusable units that other scripts or scheduled queries can call.

The main shift in thinking is cost awareness: each SQL statement inside a script incurs the normal per-byte billing. Loops that query large tables on every iteration add up quickly. Prefer set-based SQL wherever possible and reach for scripting when you genuinely need conditional logic or atomic multi-table updates.