Stored Procedures in BigQuery

A Stored Procedure is a set of SQL statements that are saved and stored in a BigQuery dataset. Procedures allow you to encapsulate complex multi-step logic into a single named object, making your data pipelines easier to manage, share, and reuse across different users and applications.

The general syntax for creating a stored procedure is as follows:

SQL Syntax

CREATE OR REPLACE PROCEDURE dataset_name.procedure_name()
BEGIN
-- SQL statements here
END

Cost Note

Creating and using stored procedures in BigQuery involves specific billing considerations.

Note: Creating a stored procedure is essentially a free administrative task.

  1. Creation Cost: Running a CREATE PROCEDURE statement does not scan table data and therefore is not billed.
  2. Storage Cost: BigQuery does not charge for storing the SQL code within a stored procedure.
  3. Execution Billing: You only pay for the data scanned by the queries inside the procedure when it is executed using the CALL statement.

Calling Stored Procedures in BigQuery

The CALL statement is used to execute a stored procedure that has been previously created. Calling a procedure triggers the execution of all SQL statements contained within its body.

Note: The CALL statement itself has no cost. You are billed only for the bytes scanned by the queries that run during the procedure's execution.

  1. Execution-Only Billing: You only pay for the data processed by the SQL statements inside the stored procedure.
  2. Dry Run Tip: You cannot perform a dry run directly on a CALL statement to estimate cost. Instead, you must estimate the cost of the individual queries inside the procedure body.

Setup: The CALL Statement

To execute a procedure, use the keyword CALL followed by the procedure's full name and parentheses.

SQL

-- Calling a procedure with no arguments
CALL `my_project.my_dataset.DailyCleanup`();