[CT-2004] [Bug] Insert statement in Post hook not working as expected

[jaswanthColibri]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Hi Team

I have a macro that takes two arguements

1. {{this}}
2. source(src_name,tbl)

and i call this macro in a post hook to insert a record in my audit table.

macro looks like below

`
{% macro post_hook_audit_macro( tgt_tbl,src_tbl) -%}

{{ print("Target Table " ~ tgt_tbl)}}
{{ print("Source Table " ~ src_tbl)}}

insert into {{ var('audit_table') }}
select sysdate,'{{tgt_tbl}}',(select count() from {{tgt_tbl}}),
'{{src_tbl}}',(select count() from {{src_tbl}} );

{{ print("Target Table after" ~ tgt_tbl)}}
{{ print("Source Table after" ~ src_tbl)}}

{%- endmacro %}

The problem is i could see proper target table and source table in printed logs.

But after insert is done instead of below data

sysdate | target_table_name|target_table_count|src_table_name|src_table_count

I am seeing below in Warehouse audit table

sysdate | target_table_name| target_table_count | target_table_name| target_table_count

how can i insert record as mentioned in first format?

`

Expected Behavior

Audit table should have below data

sysdate | target_table_name|target_table_count|src_table_name|src_table_count

instead of

sysdate | target_table_name| target_table_count | target_table_name| target_table_count

Steps To Reproduce

create a model and have post hook to insert record into a audit table.

audit table should have

sysdate
target model name
target model count
source model name
source model count

to macro pass {{this}} and source(src_name, tbl_name)

Relevant log output

No response

Environment

- OS:
- Python:
- dbt:

Which database adapter are you using with dbt?

No response

Additional Context

No response

[dbeatty10]

Hey @jaswanthColibri !

Without seeing an example of how you are specifying your post-hook, it's a bit hard for me to tell. Is there any chance you could provide that?

Is it similar to this by any chance?

{{
  config(
    post_hook=post_hook_audit_macro(this, source('your_source_name', 'your_table_name')),
  )
}}

...

If so, you'll actually want to express it like this:

{{
  config(
    post_hook="{{ post_hook_audit_macro(this, source('your_source_name', 'your_table_name')) }}",
  )
}}

...

@jtcohen6 has a such nice explanation here that I won't even try to improve upon it:

• Post-hook doesn't resolve custom schema #4023 (comment)

Note in particular where he compares post_hook=delete_from_this() to post_hook="{{ delete_from_this() }}" -- maybe this describes your situation?

Either way, will be eager to hear if any of this is helpful for you.

[jaswanthColibri]

@dbeatty10 thank you for the response. It is working

can i put source() expression in a variable and pass it to macro like below.

{% set src = source('src_name', 'table_name')) %}

post_hook = "{{ post_hook_audit_macro(this, src }}"

when i try this src is empty string in macro. I want to put source expression in a variable and pass to macro in post hook.

[dbeatty10]

It is working

Great news @jaswanthColibri ! 🎉

Putting an expression in a variable and passing it into a macro in the config

These work

Could you try something like this? Note all the quotes -- they are crucial!

{% set source_macro_string = "source('your_source_name', 'your_table_name')" %}

{{
  config(
    post_hook="{{ post_hook_audit_macro(this, " ~ source_macro_string ~ ") }}",
  )
}}

See below for an explanation why this works.

Side note: as unreadable as it is, this actually works too!

{{
  config(
      post_hook="{% set src_table = source('your_source_name', 'your_table_name') %}{{ post_hook_audit_macro(this, src_table) }}",
  )
}}

These don't work

This won't have the effect you intend:

{% set source_relation = source('your_source_name', 'your_table_name') %}

{{
  config(
    post_hook="{{ post_hook_audit_macro(this, source_relation) }}",
  )
}}

And neither will this:

{% set source_relation = source('your_source_name', 'your_table_name') %}

{{
  config(
    post_hook="{{ post_hook_audit_macro(this, '" ~ source_relation ~ "') }}",
  )
}}

The reason why neither of these work is explained nicely here.

An explanation

As @jtcohen6 mentioned here, dbt Jinja-renders your model twice:

1. once when the model is being parsed (extract dependencies, resolve configs)
2. again when the model is being executed.

Both {{ this }} and {{ source('your_source_name', 'your_table_name') }} won't give the correct values during the first rendering during parsing -- their values won't be available until the second rendering during execution.

👉 So you want the entirety of your post-hook to be re-rendered at execute time.

Working backwards

It's helpful to think about it backwards.

Ultimately, you want the following string template to be rendered during that 2nd execution phase (which will yield the SQL to execute as a post-hook):

{% set final_string = "{{ post_hook_audit_macro(this, source('your_source_name', 'your_table_name')) }}" %}

Separating the source() portion of the string as a separate variable gives:

{% set source_macro_string = "source('your_source_name', 'your_table_name')" %}
{% set final_string = "{{ post_hook_audit_macro(this, " ~ source_macro_string ~ ") }}" %}

Renaming final_string to post_hook helps us see all the pieces we need:

{% set source_macro_string = "source('your_source_name', 'your_table_name')" %}
{% set post_hook = "{{ post_hook_audit_macro(this, " ~ source_macro_string ~ ") }}" %}

Now we can see how to separate the last bit into the config of the model (leaving the first bit outside of the config):

{% set source_macro_string = "source('your_source_name', 'your_table_name')" %}

{{
  config(
    post_hook="{{ post_hook_audit_macro(this, " ~ source_macro_string ~ ") }}",
  )
}}

[jtcohen6]

@jtcohen6 has a such nice explanation here that I won't even try to improve upon it

@dbeatty10 and yet you did!

I think we simply must update documentation for this in a way that preempts this same issue coming up over & over: dbt-labs/docs.getdbt.com#2818

Going to close this for now as a duplicate