Allow dynamic SQL in pre- and post-hooks

[jakebiesinger]

Feature

Allow dynamic SQL in pre- and post-hooks

Feature description

Currently, the SQL associated with pre- and post-hooks is generated at compile-time and is never re-interpolated. This has many implications including:

• Hooks cannot refer to other tables via ref, since at compile-time, ref resolves to this, no matter what string you pass in.
• Hooks that rely on macros that have different behavior at run-time are broken (We use many such macros, e.g., with call, you don't want to execute the statement at both compile- and run-time)
• Hooks cannot be dynamic (their contents can't be based on the result of any work done by DBT)

Our use case in particular involves inspecting the results of a DBT-managed query and putting high-water marks into a separate audit table.

Request is to make the pre- and post-hook SQL be re-evaluated at run-time (ideally this would be deferred until right when the hook executes, so that the hook could be properly data-dependent).

Implementation-wise, this seems a little difficult since the config function where these hooks are attached seems to be executed once at compile-time and never again thereafter.

Who will this benefit?

Advanced users who want to create advanced hook behavior.

[jakebiesinger]

• Hooks can't even refer to the schema of the their containing table ({{ this.schema }}) since schema overrides also aren't available at compile-time.

[drewbanin]

@jakebiesinger as discussed off-line, I think you're going to want to put your hook code inside of a string. This is a little funky, but dbt can't possibly do the right thing here at parse-time. Putting the macros inside of a string will defer the executing of the macro until runtime, which is you want.

So, instead of:

{{ config({	
    'materialized': 'incremental',	
    'sql_where': 'TRUE',	
     'post-hook': post_hook_mark_dest_dates_complete(this)	
}) }}

You'd want

{{ config({	
    'materialized': 'incremental',	
    'sql_where': 'TRUE',	
     'post-hook': "{{ post_hook_mark_dest_dates_complete(this)	}}"
}) }}

This string syntax is a little funky, but it originates from the "grant" use-case for hooks. A typical hook might look like:

{{ config({	
     'post-hook': "grant select on {{ this }} to some_user"
}) }}

dbt doesn't have an accurate view of what this is at parse-time. While the above code works, the following code will definitely not:

{{ config({	
     'post-hook': "grant select on " ~ this ~ to some_user"
}) }}

By exposing the jinja expression outside of the string, jinja is forced to interpolate a likely incorrect value. Instead, you can just return a full string, and then let the jinja interpolation happen dynamically at runtime.

I don't love that this works this way, but I think we'd need to give any alternatives some more thought. Ultimately, I suspect Jinja might not be the best medium for complex logic like this, and we'll probably be best off tackling it through something like #594

Closing this, but happy to discuss in the comments below