Local Variable/set variable usage in config block

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"
]
}}

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() }}”
]
}}

1 Like

Thank you @ted.conbeer (y)

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.