Snowflake "Pivot" for dynamic columns with dbt macro

Question

Starting context:

1. There is a dbt_utils "pivot" function. This question is not concerned with that function.

2. There is some discussion about the limitations of Snowflake's built-in PIVOT, namely the inability to use dynamic columns and/or values for this function.

example_model.sql

with pivot as (
    select * 
    from {{ ref('my_base_model') }}
        pivot( sum(VALUE) for KEY in ( 
        {{ dbt_utils.get_column_values(table=ref('my_base_model'), column='KEY') }} 
        ) )
)

select * from pivot

dbt resolves that handily except for one hickup. When the above is compiled (code block below), it generates the values as a python list [...] when snowflake is expecting more like a tuple (...)

with pivot as (
    select * 
    from DB.SCHEMA.my_base_model
        pivot( sum(VALUE) for KEY in ( ['value_1', 'value_2', 'value_3', 'value_4', 'value_5', 'value_6', 'value_7'] ) )
)

select * from pivot

I was looking into using something like as_native to cast the resulting list to a tuple but have been unsuccessful so far.

Error within the dbt run:

001003 (42000): SQL compilation error:
  syntax error line 5 at position 39 unexpected '['.
  syntax error line 5 at position 961 unexpected ']'.
  compiled SQL at target\run\dbtproject\models\staging\my_application
\my_base_model.sql

Answer 1

Perhaps not the best answer, but a working answer is:

pivot_model.sql

{% set pivot_cols = dbt_utils.get_column_values(table=ref('my_base_model'), column='KEY') %}

with pivot as (
    select * 
    from {{ ref('my_base_model') }}
        pivot( sum(VALUE) for KEY in (
            {% for pivot_col in pivot_cols %}
                '{{pivot_col}}'{% if not loop.last %}, {% endif%}
            {% endfor %}
         ))
)

select * from pivot