in_transaction macro for pre- / post-hooks

[dbeatty10]

Contributions

• I have read the contribution docs, and understand what's expected of me.

Link to the page on docs.getdbt.com requiring updates

https://docs.getdbt.com/reference/resource-configs/pre-hook-post-hook#transaction-behavior

What part(s) of the page would you like to see updated?

dbt has a macro named in_transaction (#510). Handy!

It didn't show up in a search in our docs though:

You can use it (along with its siblings before_begin and after_commit ) to add arbitrary user-defined statements in 4 different places:

1. Before the main materialization code but not within the same transaction
2. Before the main materialization code and within the same transaction
3. After the main materialization code and within the same transaction
4. After the main materialization code but not within the same transaction

Example

{{ config(
    pre_hook=[
      before_begin("select 1 as id"),
      in_transaction("select 2 as id"),
    ],
    post_hook=[
      in_transaction("select 3 as id"),
      after_commit("select 4 as id"),
    ]
) }}

select 1234 as id

That isn't the only way to do it, of course. If using a dictionary looks like more fun to you, knock yerself out:

{{ config(
    materialized="plain",
    pre_hook=[
      {
        "sql": "select 1 as id",
        "transaction": False
      },
      {
        "sql": "select 2 as id",
        "transaction": True
      }
    ],
    post_hook=[
      {
        "sql": "select 3 as id",
        "transaction": True
      },
      {
        "sql": "select 4 as id",
        "transaction": False
      }
    ]

) }}

select 1234 as id

Additional information

The main caveat is that not all dbt adapters support transactions. There is already a caveat for this, but I'm not sure if it's fully up-to-date or not (#6225):

Important note: Do not use this syntax if you are using a database where dbt does not support transactions. This includes databases like Snowflake, BigQuery, and Spark or Databricks.