how to pass a variable from one macro to another in pre_hook

Question

I have macros get_date

{% macro get_date() %}
{% if execute %}
{% set p_date = run_query("select  max(date) date from table" ).columns[0][0] %}
{% else %}
{% set p_date = '2024-04-01' %} 
{% endif %}
{%do return(p_date)%}
{% endmacro %}

I call it in the model and then pass the variable to another macro

{% set my_date = get_date() %} 
{{
    config(
      pre_hook=[ 
       "{{ insert_table(report_date='" ~ my_date ~ "',pre_hook=True) }}"
          ]
    )
}}

code of macro insert_table

{% macro insert_table(report_date,pre_hook=false) %}  
{% if execute %}  
insert into  public.my_table
    values ('{{report_date}}',current_timestamp,0) 
  {% endif %} 
  {% endmacro %}

When I run my model sql in marco get_date returns '2024-05-05' in model my_date has value also '2024-05-05' but in macro insert_table gets '2024-04-01' and insert it into the table public.my_table.

I don't understand why macro insert_table its gets '2024-04-01' while it must gets '2024-05-05'. please Can someone explain what happens and what I do wrong ?

Answer 1

I believe the cause is that dbt runs in two passes:

1. Parsing
2. Execution

Even though pre_hook executes during the second pass (with execute variable = True), it looks like it takes other macros values from the first pass. See more details here

A workaround for you is to put get_date() code into insert_table() macro.

{% macro insert_table(pre_hook=false) %}  
{% if execute %}  
{% set p_date = run_query("select  max(date) date from table" ).columns[0][0] %}
insert into  public.my_table
    values ('{{p_date}}',current_timestamp,0) 
{% endif %} 
{% endmacro %}

---

Please note

1. You face this issue because you are trying to use dbt in an unexpected way
2. If you are using any hardcoded DDL in your dbt project (e.g., insert statements, create table statements), most probably you are not using dbt properly
3. You should not hardcode object references. In your case, it's from table, public.my_table
4. You might want to redesign your dbt project, so that models will be recreated from scratch on each dbt run (using only view and table materialization). In this scenario, your current problems will be gone most probably