I’m trying to scale Snowflake warehouse for running a dbt model using pre & post hooks.
I scale up the warehouse in pre-hook and then scale it down in post hook.
I want to make this entire process transactional. Often the warehouse gets scaled down by some other DML operation and my model query does not run on the desired warehouse size.
Now, I want to run the model query immediately after scaling up the warehouse on the scaled warehouse. Once the SQL statements are executed, the warehouse will be scaled down. This entire process should be atomic in nature and should be executed as a single transaction. This will ensure that the SQL query always run on the scaled warehouse.
How can I implement this?