Snowflake "Pivot" for dynamic columns with dbt macro

user_3073340 0
Viewed 936

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
dbt snowflake-cloud-data-platform jinja2
2 Answers

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
user_30733400

** UPDATE 2 **: As tuple is not supported as a filter in Jinja, so we can use join instead:

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') | join(",") }} 
        )
)

select * from pivot

** UPDATE 1 **: In case you'd like to do it with dbt, let's try this:

  1. Convert dbt_utils.get_column_values's result to tuple
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') | tuple }} 
        ) )
)

select * from pivot
  1. You can always try to create a custom dbt macro which is copy of dbt_utils.get_column_values in your local project and to use it:

Origin get_column_values

Custom:

...
{%- set values = value_list['data'] | map(attribute=0) | tuple %}
...

** ORIGIN ** Please see carefully the pivot doc.

SELECT ...
FROM ...
   PIVOT ( <aggregate_function> ( <pivot_column> )
            FOR <value_column> IN ( <pivot_value_1> [ , <pivot_value_2> ... ] ) )

[ ... ]

It doesn't have [ ] which is incorect in your query

So the fix should be:

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
user_28304640
Related