Use of bigquery multi-statement transaction in DBT

Dear all,
I am trying to integrate dbt in a workflow where I have a set of table in big query being continuously updated by a 3rd party tool (insert or update only).
I know that the provider is supposed to guarantee the consistency of the table with respect to some statements, ie. the set of table is always updated with the transaction feature.
Know, I am wondering if I can regularly extract a snapshot of all the tables throughout the use of multi statement transactions in bigquery:

To me it seems that for a specific set of model (in my case the snapshot models), I could potentially configure pre/post hook in dbt project like in here: pre-hook & post-hook | dbt Developer Hub to use that feature for my usecase.
In my understanding it should be possible to have the pre-hook (ie begin transaction) running before the first model of the directory is executed, and the post hook (end) is ran after the last model of the directory.

My questions are now:
1: Is my understanding correct, a pre-post hook can be ran only once before and after a set of models ?
2: See the following comment from dbt hook documentation:

To achieve this, you can use one of the following syntaxes. (Note: You should NOT use this syntax if using a database where dbt does not use transactions by default, including Snowflake, BigQuery, and Spark/Databricks.)


  INSERT INTO mydataset.NewArrivals
    VALUES ('top load washer', 100, 'warehouse #1');
  -- Trigger an error.
  SELECT 1/0;

  -- Roll back the transaction inside the exception handler.
  SELECT @@error.message;

Why one couldn’t use this syntax with bigquery as bigquery is supposed to support it ?

Thank you in advance for your help

Best regards

1 Like

Trying to up that topic for visibility (I’ll delete the message if this is an issue)


Did you manage to find a solution to this? I am interested.

As a note, the quote you provide from the dbt hook documentation comes from this doc page, and I think it was specifically related to the use of the before_begin and after_commit helper macros.


Wondering if there has been any update on a solution to this?