LISTAGG issue while running from a DBT macro to snowflake

Help
1

Hi,

I am executing LISTAGG function from a dbt macro to Snowflake as below.
After execution, I am assigning the value to a varible so that i can use it in the next steps

I am using the below commands to execute and assign the execution result to the dbt variable

{% macro dbt_macro_access_ready_incr_load_dynamic() %}

{% set col_list_agg_query %}

select LISTAGG(COLUMN_NAME)  as AGG_LIST  from DB_NAME.INFORMATION_SCHEMA.COLUMNS 
where TABLE_SCHEMA = 'ABC' and TABLE_NAME = 'SUMMARY';
{% endset  %}

{% set columns_agg = run_query(col_list_agg_query) %}

{% set column_names = columns_agg.columns[0].values() %}

{{ log("List aggregation: " ~ column_names, info=True) }}

{% set col_list_length_query %}
select LENGTH({{ column_names }}))  as LENGTH from dual;
{% endset  %}

{% set columns_length = run_query(col_list_length_query) %}

 {% set column_len = columns_length.columns[0].values() %}

{{ log("Length " ~ column_len, info=True) }}

{% endmacro %}

Error Message:

09:53:19 Encountered an error while running operation: Database Error
001003 (42000): SQL compilation error:
syntax error line 1 at position 149 unexpected ‘)’.
syntax error line 1 at position 151 unexpected ‘)’.

Observation:

  1. A single quote → ’ and open bracket → ( is being appened at the start of LISTAGG i.e.,
    ('EDP_INSRT_APP_ID,SNAPSHOT_ID

  2. A single quote → ’ ,comma → , and close bracket → ) at the end of the result like below
    EDP_INSRT_USR’,)

09:53:19 List aggregation: (‘EDP_INSRT_APP_ID,SNAPSHOT_ID,COMMENTS,SNAPSHOT_INSERT_DTTM,VERSION,EDP_INSRT_APP,USER,EDP_INSRT_DTTM,EDP_INSRT_RUN_ID,EDP_INSRT_USR’,)