BQBrains | BigQuery Scripting, Stored Procedures, and Multi-Statement Transactions

Many BigQuery teams outgrow single statements long before they need an external orchestrator. Scripting lets you chain statements, keep state in variables, branch, loop, and wrap changes in a transaction, all inside BigQuery. This tutorial walks through the building blocks and a re-runnable pattern you can adapt.

1. Variables and control flow

A script is a sequence of statements separated by semicolons. Declare variables at the top of a block, then set them with SET.

DECLARE run_date DATE DEFAULT CURRENT_DATE();
DECLARE row_count INT64;

SET row_count = (
  SELECT COUNT(*) FROM `proj.raw.orders`
  WHERE order_date = run_date
);

IF row_count = 0 THEN
  SELECT 'No data for ' || CAST(run_date AS STRING) AS message;
ELSE
  SELECT row_count AS rows_to_process;
END IF;

BigQuery also supports LOOP, WHILE, FOR ... IN (SELECT ...), BREAK, and CONTINUE. Prefer set-based SQL over row-by-row loops; use loops to iterate over a short list such as tables or dates, not over data rows.

2. Dynamic SQL with EXECUTE IMMEDIATE

When table or column names vary, build the statement as a string. Pass values as parameters, and never concatenate untrusted input into the string.

DECLARE tbl STRING DEFAULT 'events_20260101';
EXECUTE IMMEDIATE FORMAT("""
  SELECT COUNT(*) AS n FROM `proj.analytics.%s` WHERE country = @c
""", tbl)
USING 'DE' AS c;

Identifiers cannot be parameterized, so validate them against INFORMATION_SCHEMA.TABLES before use.

3. Stored procedures

Package a script as a reusable procedure with IN, OUT, and INOUT arguments.

CREATE OR REPLACE PROCEDURE `proj.ops.load_daily_orders`(IN p_date DATE, OUT p_rows INT64)
BEGIN
  DELETE FROM `proj.mart.orders` WHERE order_date = p_date;

  INSERT INTO `proj.mart.orders`
  SELECT * FROM `proj.staging.orders` WHERE order_date = p_date;

  SET p_rows = (SELECT COUNT(*) FROM `proj.mart.orders` WHERE order_date = p_date);
END;

DECLARE n INT64;
CALL `proj.ops.load_daily_orders`(DATE '2026-01-01', n);
SELECT n;

The delete-then-insert for one partition key makes the procedure idempotent: running it twice gives the same result. That property matters more than anything else for jobs that get retried.

4. Multi-statement transactions

A transaction makes several DML statements succeed or fail together. It works across multiple tables in one script or session.

BEGIN
  BEGIN TRANSACTION;

  DELETE FROM `proj.mart.orders` WHERE order_date = DATE '2026-01-01';
  INSERT INTO `proj.mart.orders`
  SELECT * FROM `proj.staging.orders` WHERE order_date = DATE '2026-01-01';

  COMMIT TRANSACTION;
EXCEPTION WHEN ERROR THEN
  ROLLBACK TRANSACTION;
  RAISE USING MESSAGE = FORMAT('Load failed: %s', @@error.message);
END;

Points to remember:

Transactions cover DML on tables. DDL such as CREATE TABLE has restrictions inside a transaction, so check the current documentation before relying on it.
Concurrent transactions that change the same table can conflict, and one will fail. Keep transactions short and schedule writers to the same table serially.
Always pair BEGIN TRANSACTION with a ROLLBACK in the exception handler, or the session may be left with an open transaction.

5. Error handling

BEGIN ... EXCEPTION WHEN ERROR THEN ... END catches errors. Inside the handler, @@error.message and @@error.statement_text describe the failure. Use RAISE to re-throw so the scheduler still marks the run failed. Swallowing errors silently is the most common scripting mistake.

6. Cost and debugging tips

A script is billed by the statements it runs. Use a dry run, or set maximum_bytes_billed on the job, as a guardrail.
In the console, the job history shows each child job of a script. Query INFORMATION_SCHEMA.JOBS and filter on parent_job_id to see which statement is expensive.
Use SELECT ... or ASSERT statements as checkpoints, for example ASSERT row_count > 0 AS 'staging is empty';.
@@row_count returns the rows affected by the previous DML statement, which is useful for logging.

7. When to use something else

Scripting suits SQL-only workflows with a handful of steps. If you need dependency graphs, version control, testing, or cross-system steps, use Dataform or an orchestrator and call procedures from it. See our posts on Dataform and on orchestrating BigQuery for that decision.

Checklist

Make every procedure idempotent (key-scoped delete and insert, or MERGE).
Wrap multi-table changes in a transaction with an explicit rollback.
Parameterize values, and validate identifiers.
Re-raise errors so failures are visible.
Put byte limits on scheduled scripts.

Need help turning ad hoc SQL into reliable BigQuery workflows? Get in touch with the BQBrains team.