A loop, and BEGIN ... EXCEPTION

Some jobs repeat: the same steps for each make, each month, each table. A script can repeat statements in a loop and carry on when one pass goes wrong, though one GROUP BY query beats a loop over data almost every time.

How it works

LOOP runs its statements again and again until a LEAVE ends it. A counter variable says which item the pass is on, and a temp table keeps what each pass found, so the last SELECT can return all of it. BEGIN ... EXCEPTION WHEN ERROR THEN ... END is a block with a safety net. If a statement inside it fails, BigQuery stops that block and runs the handler instead, and the script carries on after END.

Common mistake

WHEN ERROR catches every error, including your own mistakes. A typo in a column name of the block's INSERT is an error too, so the handler runs on every pass and the script still finishes, with every make marked as failed. Keep the block small, and make the handler record something you can see, such as a failed flag or a skipped status.

Worked example

DECLARE makes ARRAY<STRING> DEFAULT ['HONDA', 'MAZDA', 'MAZDAA'];
DECLARE i INT64 DEFAULT 0;

CREATE TEMP TABLE audit (make STRING, step_no INT64, step STRING);

LOOP
  IF i >= ARRAY_LENGTH(makes) THEN
    LEAVE;
  END IF;

  BEGIN
    DECLARE target_make STRING DEFAULT makes[OFFSET(i)];

    INSERT INTO audit (make, step_no, step) VALUES (target_make, 1, 'started');
    INSERT INTO audit (make, step_no, step)
    SELECT
      target_make,
      2,
      CONCAT('finished: ', CAST(ROUND(COUNTIF(test_result = 'PASSED') / COUNT(*), 3) AS STRING))
    FROM `learn.mot.fact_mot_test`
    WHERE make = target_make
      AND completed_date BETWEEN '2024-01-01' AND '2024-12-31';
  EXCEPTION WHEN ERROR THEN
    INSERT INTO audit (make, step_no, step) VALUES (makes[OFFSET(i)], 3, 'failed');
  END;

  SET i = i + 1;
END LOOP;

SELECT make, step_no, step FROM audit ORDER BY make, step_no;

The list holds three makes. MAZDAA is a typo for MAZDA, so it has no tests in 2024. i counts the passes, and the LEAVE ends the loop once i has gone past the last make.

Each pass is a BEGIN ... END block. Its DECLARE comes first in the block, and target_make exists only for that pass.

The block writes a started row, then tries to write a finished row with the make's pass rate. HONDA and MAZDA get both rows.

For MAZDAA the second INSERT fails with a division by zero. The handler writes a failed row, and the loop goes on. The started row is still there, because a handler undoes nothing. The handler cannot use target_make, so it reads the make from the list again.

You do not need a script to count tests per make, because one GROUP BY query does that. The script helps because each pass can fail on its own, and the handler records that pass and lets the other passes finish.

All BigQuery lessons