Local Variable/set variable usage in config block

[ravi4r]

Hi Team,

Is there any way we can use variable which value through set function can be used in config pre_hook or post_hook section ?

eg:
{%- set empDelId = get_emp_id_to_delete() %}

{{
config(materialized = ‘incremental’,
pre_hook = [ " DELETE FROM {{ source(‘myWIsource’, ‘Employees’) }} where employee_di = empDelId"
]
}}

[ted.conbeer]

No, this won’t work – set creates a local variable that is inaccessible outside the model’s context.

Instead, I would create a macro and call the macro in the pre_hook:

{{
config(materialized = ‘incremental’,
pre_hook = [ "{{ delete_users() }}"]
}}

{% macro delete_users() %}
{%- set empDelId = get_emp_id_to_delete() %}
DELETE FROM {{ source(‘myWIsource’, ‘Employees’) }} where employee_di = {{ empDelId }}
{% endmacro %}

Or you could just call the get_emp_id_to_delete macro from your pre-hook:
{{
config(materialized = ‘incremental’,
pre_hook = [ “DELETE FROM {{ source(‘myWIsource’, ‘Employees’) }} where employee_di = {{ get_emp_id_to_delete() }}”
]
}}

[ravi4r]

Thank you @ted.conbeer (y)