Multiple redshift unload table from dbt core

Question

I am trying to loop through some values in dbt retrieved by a model and each time I am applying a filteron a value I want to execute an unload function to s3. So I am trying to create a different file for each filter. I am able to run the unload function on his own but can't seem to make it work in the for loop.

I set a macro to get these distinct values to filter on:

{% macro get_ids() %}

{% set ids_query %}
select distinct id from {{ source('my', 'data') }}  
where flag = 1
order by 1
{% endset %}

{% set results = run_query(ids_query) %}

{% if execute %}
{# Return the first column #}
{% set results_list = results.columns[0].values() %}
{% else %}
{% set results_list = [] %}
{% endif %}

{{ return(results_list) }}

{% endmacro %}

And I have a model:

-- models/my_model.sql
{% set ids = get_ids() %}

{% for id in ids %}
    -- Generate the CSV data using SQL queries
    {% set unload_sql = "SELECT * FROM x WHERE col = '" ~ id ~ "';" %}

    -- Define the S3 path for each UNLOAD operation
    {% set s3_path = 's3://<bucket_name>/<folder_name>/' ~ id ~ '.csv' %}

    -- Use the redshift.unload_table command within the loop
    {{
    config(
        {
            "post-hook": [
                "{{ redshift.unload_table(unload_sql,
                                s3_path=s3_path,
                                iam_role='iam_role',
                                header=True,
                                delimiter=',',
                                overwrite=true) }}"
                ]
            }
        )
    }}
    
{% endfor %}

select * from {{ ref('test_table') }}

I am able to run the unload code on his own and works fine:

{{
  config(
      {
        "post-hook": [
                "{{ redshift.unload_table('my',
                                'table',
                                s3_path='s3://<bucket_name>/<folder_name>/<file_name>.csv',
                                iam_role='iam_role',
                                header=True,
                                delimiter=',',
                                overwrite=true) }}"
            ]
        }
    )
}}

select * from {{ ref('lkp__employment_types') }}

Multiple redshift unload table from dbt core

0 Answers0