Unpivoting util in dbt

Question

i have data with the following structure:

I want to unpivot the columns so my target data looks as below:

I am trying to achieve this using dbt unpivot util:

{{
    dbt_utils.unpivot(
        relation=ref("resp_data"), --data will come from staging layer
        cast_to="varchar",
        field_name=['PartID','Col1','Col2','Col3'],
        value_name=['PartID','Unpivcol','Unpivval']
    )
}}

This is not working correctly. Can i get some help on correcting this? Also can i push the generated unpivoted data into a table in staging layer?

Answer 1

By checking the docs here, you can see that field_name and value_name are not expecting an array, but a string. So you are using the macro incorrectly.

I believe the correct way would be to do something like the following:

{{
    dbt_utils.unpivot(
        relation=ref("resp_data"),
        cast_to="varchar",
        exclude=["PartID"],
        field_name="unpivcol",
        value_name="unpivval"
    )
}}

where you exclude PartID from the pivoting, and you give names to the columns accordingly.