Understanding the Utility of Transactions in BigQuery

BigQuery supports transactions, which are useful for ensuring that a series of operations either all succeed or all fail, reverting to the previous state in case of failure. This is useful for changes to multiple tables at once or changes to one table in stages based on intermediate calculations.

BigQuery supports multi-statement transactions within a single query or across multiple queries when using sessions. These transactions allow for mutating operations on one or more tables, committing or rolling back changes atomically. Transactions guarantee ACID properties and support snapshot isolation.

A transaction must be contained in a single SQL query, except when in Session mode. To start a transaction, use the BEGIN TRANSACTION statement. The transaction ends when a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement is executed, or when the query ends before reaching either of these two statements.

BigQuery supports the following statement types in transactions:

• Query statements: SELECT
• DML statements: INSERT, UPDATE, DELETE, MERGE, and TRUNCATE TABLE
• DDL statements on temporary entities: CREATE TEMP TABLE, CREATE TEMP FUNCTION, DROP TABLE on a temporary table, DROP FUNCTION on a temporary function.

Transactions cannot use DDL statements that affect permanent entities.

Transactions can also be used to update inventory with new arrivals and delete the corresponding records from NewArrivals. Assuming that all statements complete successfully, the changes in both tables are committed atomically as a single transaction.

BigQuery assigns a transaction ID to each multi-statement transaction. The transaction ID is attached to each query that executes inside the transaction.

Transactions cannot mutate data in more than 100 tables and can perform at most 100,000 partition modifications.

BI Engine does not accelerate queries inside a transaction.

Read full documentation at Google Cloud help center here.