In BigQuery, how does a multi-statement script execute, and what does BEGIN ... EXCEPTION WHEN ERROR add?
answer
- semicolons in one request, many jobs underneath
- each statement is billed as its own query
- variables and control flow, not cheaper compute
- a handler can read why the statement failed
- looping one row at a time is the trap
basics
~20 sA script is submitted as one request that runs a parent job, with each statement executing as its own child job and billed as a query. An EXCEPTION WHEN ERROR block catches a failing statement so the script can log it, roll back, or continue.
solid answer
~50 sBigQuery accepts several statements separated by semicolons in one request. It creates a parent job whose statements run sequentially as child jobs, each billed like an ordinary query — so a script is not a discount, it is orchestration. Inside a script you get `DECLARE` and `SET` for variables, control flow (`IF`, `WHILE`, `LOOP`, `FOR ... IN`), `CREATE TEMP TABLE` for intermediates scoped to the script, `EXECUTE IMMEDIATE` for dynamic SQL, and `CALL` for stored procedures. Wrapping statements in `BEGIN ... EXCEPTION WHEN ERROR THEN ... END` catches an error from any statement in the block; the handler can inspect `@@error.message` and `@@error.statement_text`, log a row, and `ROLLBACK TRANSACTION` if you opened one with `BEGIN TRANSACTION`. The main anti-pattern is looping row by row: each iteration is a separate job with its own latency, so a single set-based `MERGE` beats a `FOR` loop that merges one row at a time.
go deeper
Know that BigQuery accepts several statements in one request, that you can declare variables and use IF and loops, and that each statement still runs as its own query.
Explain the parent-job and child-job structure, the billing consequence, what CREATE TEMP TABLE and EXECUTE IMMEDIATE are for, and how an EXCEPTION block catches a failing statement.
Demonstrate production judgment: transactional multi-table swaps, re-raising after logging the error, and rewriting row-by-row loops as one set-based MERGE.
Decide where procedural logic belongs at all — in-warehouse stored procedures versus an external orchestrator with retries, dependencies and alerting — and how cost stays attributable inside long scripts.
## What a script is A BigQuery script is multiple SQL statements, separated by semicolons, submitted as a single request. BigQuery runs a *parent* job for the script and a *child* job for each statement it executes. That has two consequences a candidate should state without prompting: statements run sequentially in the order written, and each one is billed exactly as if you had submitted it alone. Scripting buys you control flow and variables, not cheaper queries. ## The procedural surface ```sql DECLARE cutoff DATE DEFAULT CURRENT_DATE() - 7; DECLARE affected INT64 DEFAULT 0; CREATE TEMP TABLE staged AS SELECT * FROM raw.events WHERE event_date >= cutoff; MERGE analytics.events AS t USING staged AS s ON t.event_id = s.event_id WHEN MATCHED THEN UPDATE SET payload = s.payload WHEN NOT MATCHED THEN INSERT ROW; SET affected = @@row_count; ``` The pieces: - **`DECLARE` / `SET`** define and assign typed variables. Variables can be used anywhere a literal can, including inside a `MERGE` predicate — which is how you parameterise a script without string-building. - **Control flow**: `IF ... THEN ... ELSEIF ... ELSE ... END IF`, `WHILE ... DO ... END WHILE`, `LOOP ... END LOOP` with `BREAK`/`CONTINUE`, and `FOR record IN (SELECT ...) DO ... END FOR` to iterate over a query result. - **`CREATE TEMP TABLE`** materialises an intermediate that lives for the duration of the script and then disappears — the readable alternative to a chain of enormous CTEs, and often faster because the intermediate is computed once rather than re-derived. - **`EXECUTE IMMEDIATE`** runs dynamic SQL built as a string, with `USING` for parameters and `INTO` to capture scalar results. This is how you write one script that loops over a list of tables. - **System variables** such as `@@row_count` (rows affected by the last DML statement) and the error variables inside a handler. - **`CREATE PROCEDURE`** persists a script as a routine in a dataset, invoked with `CALL`. ## Error handling By default a failing statement aborts the script. To take control: ```sql BEGIN BEGIN TRANSACTION; DELETE FROM analytics.daily WHERE day = cutoff; INSERT INTO analytics.daily SELECT * FROM staged; COMMIT TRANSACTION; EXCEPTION WHEN ERROR THEN ROLLBACK TRANSACTION; INSERT INTO ops.job_errors (ts, msg, stmt) VALUES (CURRENT_TIMESTAMP(), @@error.message, @@error.statement_text); RAISE USING MESSAGE = 'daily rebuild failed'; END; ``` The `EXCEPTION WHEN ERROR THEN` clause catches an error raised by any statement in the enclosing `BEGIN` block. Inside the handler you can read `@@error.message` and `@@error.statement_text` to log what happened, and `RAISE` re-raises so the orchestrator above still sees a failure — swallowing the error silently is how a broken pipeline reports success for a week. `BEGIN TRANSACTION` / `COMMIT TRANSACTION` / `ROLLBACK TRANSACTION` give a multi-statement transaction, which is what makes the delete-then-insert pattern above safe: readers never see the intermediate state where the day's rows are gone. ## Where scripts go wrong **Row-by-row loops.** The single most common anti-pattern is a `FOR ... IN (SELECT ...)` that issues one `MERGE` or `INSERT` per row. Each iteration is a separate child job with job-submission latency and its own overhead — a pattern that takes seconds in a row-store procedural language takes hours here. BigQuery is a set-based engine; express the whole operation as one `MERGE`. **Treating scripts as an orchestrator.** Scripts have no scheduling, no retry policy, no dependency graph, and no visibility outside the job. They are good for a unit of work that must be transactional or must branch on data. Cross-table dependency ordering, retries and alerting belong in a real scheduler. **Losing observability.** Because each statement is a child job, the parent job's row does not tell you which statement was slow or expensive; you have to look at the children. Teams that only monitor the parent lose the ability to attribute cost inside long scripts. **Forgetting that DDL and DML in a script are still full statements.** A `CREATE OR REPLACE TABLE` inside a loop rewrites the whole table each pass. The procedural wrapper does not make the statement incremental. ## When to use one Good fits: a transactional multi-table swap; a backfill that must branch on whether yesterday's partition exists; a parameterised routine published as a stored procedure so analysts can `CALL` it; dynamic SQL over a list of tables. Poor fits: anything a single set-based statement already expresses, and anything that wants retries, schedules or alerting.
- Does bundling ten statements into one script reduce what BigQuery bills?No. Each statement runs as its own child job and is billed exactly as if submitted separately. Scripting buys control flow, variables, temporary tables and transactional grouping — not a discount. If anything, a careless script costs more, because a loop that runs a statement per row pays that statement's overhead many times.
- Why is a FOR loop that runs one MERGE per row a bad idea in BigQuery?Every iteration is a separate child job with submission and planning overhead, against an engine designed to process the whole set at once. A thousand-row loop becomes a thousand jobs. Rewrite it as a single MERGE joining the source set to the target; the branching that motivated the loop almost always expresses as MERGE's WHEN clauses.
- How do you make a delete-then-insert refresh invisible to concurrent readers?Wrap it in BEGIN TRANSACTION and COMMIT TRANSACTION so readers never observe the interval where the rows are deleted but not yet reinserted, and put the whole block inside a BEGIN ... EXCEPTION handler that issues ROLLBACK TRANSACTION on failure. An alternative with no transaction at all is to build the new content in a temporary table and swap it in with one statement.
saying these in an interview costs you the question
- Thinks a script runs as one cheap job instead of many
- Loops row by row instead of writing one set-based statement
- Swallows the error in the handler without re-raising
- Believes scripting replaces a scheduler with retries and alerting
- Assumes temp tables in a script persist after it finishes