insert_overwrite (static) + macro

Help
, ,
1

Please, help me how it pass macro value to config partitions?

./macros/partitions_to_replace.sql

{% macro partitions_to_replace_eiei() %}

    {% set is_lastday_of_month = run_query("SELECT IF(LAST_DAY(current_date()) = current_date(), 1, 0)") %}
    {% if execute %}

        {{ print(is_lastday_of_month[0].values()) }}

        {% if is_lastday_of_month.columns[0].values()|int == 0 %}

            {{ return(['current_date',
                        'date_sub(current_date, interval 1 day)']) }}

        {% else %}

            {% set where_sql = [] %}
            {% for i in range(1,36) %}

                {{ where_sql.append('date_sub(current_date, interval '+i|string+' day)') }}

            {% endfor %}
            {{ return(where_sql) }}

        {% endif %}
    {% endif %}


{% endmacro %}

./model/test.sql

{% set partitions_to_replace = partitions_to_replace_eiei() %}

{{ 
    config(
        database="gillcapital-datalake",
        materialized="incremental",
        partition_by = {'field': 'order_date', 'data_type': 'timestamp','granularity': 'day'},
        incremental_strategy='insert_overwrite',
        partitions= partitions_to_replace
    )
}}

./target/run/partitions_to_replace/models/test.sql

SELECT id,
user_id,
order_date,
status 
FROM `test_project.z_test_dbt.raw_order`

            
        WHERE timestamp_trunc(order_date, day) in (current_date,date_sub(current_date, interval 1 day))
            
) as DBT_INTERNAL_SOURCE
        on FALSE

    when not matched by source
         and timestamp_trunc(DBT_INTERNAL_DEST.order_date, day) in (
              
, 
,  ,  ,  ,  , 
,  ,  ,  ,  , 
, 
, 

          ) 
        then delete

    when not matched then insert
        (`id`, `user_id`, `order_date`, `status`)
    values
        (`id`, `user_id`, `order_date`, `status`)
2

Hi @AumThanapol, unfortunately, this will not work and to understand this you have to understand the execute mode

When you execute a dbt compile or dbt run command, dbt:

  1. Reads all of the files in your project and generates a ā€œmanifestā€ comprised of models, tests, and other graph nodes present in your project. During this phase, dbt uses the ref statements it finds to generate the DAG for your project. No SQL is run during this phase, and execute == False.
  2. Compiles (and runs) each node (eg. building models, or running tests). SQL is run during this phase, and execute == True.

What then?

the config information is generated in parse-time, the run-query macro works just in run-time. So, when you run the query to get the dates, the config block is already done, and you cannot pass anything to it at run-time, as far as I know.

If you give more details of what you want to do I can try to help finding a solution

1 Like
3

Hi @brunoszdl, Thanks a lot for replied!!.

My aim is to use or pass values from a macro to config partitions.


Scenario
Letā€™s say I have over 20 models. Currently, if I want to change the date in the ā€˜insert_overwriteā€™ model (which is static), I have to modify all of the model files. This is quite time-consuming.

If I could use a macro in the config partitions, I would only need to update the macro file and all the models would automatically get updated. Thatā€™s what Iā€™m trying to achieve.


Unfortunately, I havenā€™t had any success with the following two methods:

  1. Having the config partitions read from a set variable:
{% set partitions_to_replace = partitions_to_replace_eiei() %}

{{ 
    config(
        partitions = partitions_to_replace*
    )
}}
  1. Having the config partitions read from a macro function:
{{ 
    config(
        partitions= partitions_to_replace_eiei()
    )
}}


Interestingly, it works in is_incremental()

{% set partitions_to_replace = partitions_to_replace_eiei() %}
... 
...
{% if is_incremental() %}
		
    WHERE DATE(order_date) in ({{ partitions_to_replace | join(',') }})
		
{% endif %}


I need help in understanding why these methods arenā€™t working in the config partitions. Could you help me with this?

4

It does not work because when the query is run (run-time), your model config was already defined by dbt (parse-time, which is before run-time)

It works in the is_incremental() because this macro is evaluated at run time

What you can do is use variables

Define a variable in your dbt_project.yml and call this variable in the config block

When you want to change the dates you can change the variable in dbt_project.yml

You can also pass a list of dates to the variable

1 Like
5

Thanks @brunoszdl, but when I try this it doesnā€™t work

vars:
  partitions_to_replace: partitions_to_replace_eiei()


I want to use a macro because I need to pass a list into the configuration

Example, if it isnā€™t last day of month use this list
([current_date, date_sub(current_date, interval 1 day)])

Or, it is last day of month then use this list
[current_date, date_sub(current_date, interval 1 day), ..., date_sub(current_date, interval x day)]

Model.sql

config( partitions = [<list above>])
6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.