running multiple sql statements from a macro in a pre_hook

I want to run multiple sql statements before my main model (due to some idiosyncracies of teradata data warehouse that I’m using). The sql statements I want to run in the pre_hook depend on whether the model is being run incrementally or as a full refresh, so I have written a macro which needs to have access to the result of the is_incremental function. In my examples there are just two statements but the actual number of statements will also depend on this context.

I am aware of several github issues (2370, 4023, 3985, 3986, 5413 (I can only hyperlink the first two as i’m a new user) ) where they mention config information such as is_incremental or custom schema get rendered at parse time instead of run time, and the solution seems to be to nest curlies to make sure it gets re-rendered, e.g.:

{{ config(
    materialized='incremental',
    pre_hook="{{ conditional_pre_hook_macro() }}"
) }}

However this solution seems to only work for singular string value for the pre_hook argument of config, whereas I need to pass an array of strings as the pre_hook argument. I can’t figure out how to get the macro to render properly with the correct result from is_incremental being rendered at run time, whilst having the pre_hook argument render as an array, not a string.

Here an example of the macro I am using:

{% macro conditional_pre_hook_macro() %}
{% if is_incremental() %}
create multiset volatile table temp as (
    select
    now() as ts,
    'ran incrementally' as status
)
with data and stats
primary index (ts)
on commit preserve rows
;
insert into temp
select
now(),
'foo            '
;
{% else %}
create multiset volatile table temp as (
    select
    now() as ts,
    'fully refreshed  ' as status
)
with data and stats
primary index (ts)
on commit preserve rows
;
insert into temp
select
now(),
'bar            '
;
{% endif %}
{% endmacro %}

And here is an example of the model. I’m using the split method to ensure that the result is an array of strings instead of just one string, but it seems to have no effect:

{{ config(
    materialized='incremental',
    pre_hook="{{ conditional_pre_hook_macro() }}".split(';')
) }}
select 1 as foo

Running this gives the following error, because it is trying to run two statements in one go (this is why the pre_hook needs to be an array, not just a single string):

[Teradata Database] [Error 3722] Only a COMMIT WORK or null statement is legal after a DDL Statement.

I have tried making my macro return an array of strings, but then the pre_hook just gets rendered as a string with an array inside of it, e.g. '["select foo", "select bar"]', and I can’t seem to coerce this back to an actual array.

Is there a trick to this or is what i’m trying to do fundamtentally at odds with how dbt is desgined to work?

Many thanks,

Sam

1 Like