dbt Pre-Hooks and Post-Hooks: A Complete Tutorial with Example and Execution

 
dbt pre hook & post hook
 
1.     What is dbt pre-hook and post-hook?
In dbt (data build tool), pre-hooks and post-hooks are SQL commands or scripts that run automatically before or after a specific model, seed, snapshot, or other resource is executed. They allow you to perform setup or cleanup tasks, enforce constraints, or execute auxiliary operations around your core transformations.
Uses of

Pre-Hook	Post-Hook
Pre-Hook execute before the main operation	Post-Hook execute after the main operation
Dropping or truncating staging tables.	Adding constraints or indexes.
Setting session variables.	Validating data.
Creating temporary tables needed for the main operation.	Logging or auditing.
	Moving or copying data to other locations.

 
2.     How to specify hook in dbt?
Hooks are specified directly model’s .sql file or dbt_project.yml or properties.yml file
3.     Example of pre-hook  and post-hook in mode’s .sql file
 
·         Direct method – in model’s .sql file we can specify as below
{{ config (
    materialized='view' ,
pre_hook= ["INSERT INTO ANALYTICS.AUDIT_LOG(ID,EVENT,PROCESS,NAME,RUN_DATE) VALUES('M3','START','MODEL','HOOK_MODE_SELF',CURRENT_TIMESTAMP)"],
 post_hook= ["INSERT INTO ANALYTICS.AUDIT_LOG(ID,EVENT,PROCESS,NAME,RUN_DATE) VALUES('M3','END','MODEL','HOOK_MODEL_SELF',CURRENT_TIMESTAMP)"]
)
}}
 
 
 
  
·       Before_begin
·       After_commit
{{  config(materialized='view',
    pre_hook=before_begin("INSERT INTO ANALYTICS.AUDIT_LOG(ID,EVENT,PROCESS,NAME,RUN_DATE) VALUES('M5','START','MODEL','HOOK_MODEl_before_after',CURRENT_TIMESTAMP)"),
    post_hook=after_commit("INSERT INTO ANALYTICS.AUDIT_LOG(ID,EVENT,PROCESS,NAME,RUN_DATE) VALUES('M5','END','MODEL','HOOK_MODEl_before_after',CURRENT_TIMESTAMP)")
  )
}}

·       Dictionary method

{{ config (

    materialized = 'view',

    pre_hook ={ "sql": "INSERT INTO ANALYTICS.AUDIT_LOG(ID,EVENT,PROCESS,NAME,RUN_DATE) VALUES('M4','START','MODEL','HOOK_MODEL_DICTIONARY',CURRENT_TIMESTAMP)",

    "transaction": False },

    post_hook ={ "sql": "INSERT INTO ANALYTICS.AUDIT_LOG(ID,EVENT,PROCESS,NAME,RUN_DATE) VALUES('M4','END','MODEL','HOOK_MODEL_DICTIONARY',CURRENT_TIMESTAMP)",

    "transaction": False }

) }}

 

Specify pre-hook and post-hook in Properties.yml and dbt_project.yml

 

    hook_project_seed_t:

      +pre-hook: ["INSERT INTO ANALYTICS.AUDIT_LOG(ID,EVENT,PROCESS,NAME,RUN_DATE) VALUES('S1','START','SEED','HOOK_PROJECT_SEED_T',CURRENT_TIMESTAMP)"]

      +post-hook: ["INSERT INTO ANALYTICS.AUDIT_LOG(ID,EVENT,PROCESS,NAME,RUN_DATE) VALUES('S1','END','SEED','HOOK_PROJECT_SEED_T',CURRENT_TIMESTAMP)"]

 

    seed_test_1:

      pre-hook:

        - "INSERT INTO ANALYTICS.AUDIT_LOG(ID,EVENT,PROCESS,NAME,RUN_DATE) VALUES('S2','START','SEED','HOOK_PROJECT_SEED_TEST_1',CURRENT_TIMESTAMP)"

      post-hook:

        - "INSERT INTO ANALYTICS.AUDIT_LOG(ID,EVENT,PROCESS,NAME,RUN_DATE) VALUES('S2','END','SEED','HOOK_PROJECT_SEED_TEST_1',CURRENT_TIMESTAMP)"