I would like to run a DELETE statement before the execution of a model. The model is called ga_events and the DELETE statement should be based on another model called stg_events_inc. There fore I've created a macro:
{%- macro get_delete_statement(table, stg_table) -%}
DELETE FROM {{ table }} WHERE event_date BETWEEN (SELECT MIN(event_date) FROM {{ stg_table }}) AND (SELECT MAX(event_date) FROM {{ stg_table }});
{%- endmacro -%}
I’m calling the macro in the ga_events model inside the config block, like so:
{{
config(
materialized = 'incremental',
pre_hook = get_delete_statement(this, ref('stg_events_inc')),
on_schema_change = 'fail'
)
}}
The final compilated DELETE statement looks like this:
DELETE FROM `project`.`schema`.`ga_events` WHERE event_date BETWEEN (SELECT MIN(event_date) FROM `project`.`schema`.`ga_events`) AND (SELECT MAX(event_date) FROM `project`.`schema`.`ga_events`);
No matter what reference to a model I put into the the macro arguments, it always references the model itself (ga_events). I just do not understand this kind of behaviour. However, if this is not in the config block it works normally.
The same applies for the sql_header config parameter.