Transactions in BigQuery

Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified
Search for a command to run...

Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified
No comments yet. Be the first to comment.
Short, practical posts on SQL and BigQuery — from core language features to advanced query patterns. A reference for data practitioners at every level.
Here's a useful Dataform concept: pre_operations and post_operations. As the name implies, these represent a set of actions that run before and after the main operation (table, view, or SQL operations

BigQuery has always been a SQL engine for tabular data. Object tables add an interesting twist to that. Instead of rows containing values, an object table gives you one row per file — pointing at da

Query your data lake with warehouse-grade security and performance — without moving a single file.

Ever run a heavy BigQuery SQL query, processed gigabytes of data — and then accidentally closed the tab or forgot to save the results? 😬 Don't re-run it. Your results are still there. BigQuery automa

You can use query parameters in BigQuery hashtag#SQL (now in the console as well!) — but how are they different from variables, and when should you use each? Both parameters and variables act as place

For a long time I didn't even know transactions existed in BigQuery. How are they useful?
Say you are performing a number of operations that you would like to succeed in full (so all steps are successful) or be aborted entirely (reverting to the previous state), basically ALL or NOTHING.
In case an error occurs in any of the operations inside the transaction block (BEGIN TRANSACTION -> COMMIT TRANSACTION), all the operations are rolled back, with the state being reverted to what it was before.
The docs point out that use cases for transactions could include:
- changes to multiple tables at ones
- changes to one table in stages, based on some intermediate calculations we perform
In the example below, we wanted to perform another operation after inserting a row in the table, but upon encountering an error, that change is reverted as well, leaving us with the state we had before running this code.
Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.