DBT - Pivoting a table with multiple columns

Question

Wondering if anyone can help here, I’m trying to use dbt_utils.pivot to get this table:

+-------+-------+-------+--------+
|metric1|metric2|metric3| date   |
|-------+-------+-------+--------+
|   100 | 10400 |  8000 | 01/01  |
|   200 | 39500 | 90700 | 02/01  |
|   200 | 39500 | 90700 | 03/01  |
+-------+-------+-------+-------+

To look like this table:

+-------+-------+-------+-------+-------+
| metric_name| 01/01 | 02/01 | 03/01 | etc . . .
|-------+-------+-------+-------+----|
| metric1    | 10400 |  8000 | 11000 |
| metric2    | 39500 | 90700 | 12000 |  
| metric3    | 39500 | 90700 | 12000 |  
+-------+-------+-------+-------+-------+

I would take each metric one by one, (create a table with just metric1 and date) and pivot the dates, then union each table,

My problem is that dbt_utils.pivot doesn’t support CTEs, so I would be required to create a model for each metric (I have more than 3) so I’m wondering is there a way to get around this? Due to the number of dates I also can’t use Snowflake’s PIVOT function as this requires you to explicitly name each row you want to pivot, and there would be too many to list off and would constantly be new dates added!

Answer 1

What you're looking for is actually to transpose your table, not pivot it. This can be achieved by an unpivot (or "melt") operation, followed by a pivot on a different column.

dbt-utils has a macro for unpivoting, too:

-- unpivot_model.sql
{{
    dbt_utils.unpivot(
        relation=ref("table_name"),
        cast_to="int",
        exclude=["date"],
        field_name="metric_name",
        value_name="metic_value",
    )
}}

this becomes:

date	metric_name	metric_value
01/01	metric1	100
02/01	metric1	200
03/01	metric1	200
01/01	metric2	10400
...	...	...

Then you can pivot that new table:

-- pivot_model.sql
select
    metric_name,
    {{
        dbt_utils.pivot(
            "date",
            dbt_utils.get_column_values(ref("unpivot_model"), "date"),
            then_value="metric_value",
        )
    }}
from {{ ref("unpivot_model") }}
group by metric_name