Dynamic SQL Pivots for any database using dbt

Coupling SQL with iterative and conditional logic to create dynamic pivots for any database

photo by Felipe Hoffa
dynamic_pivot (columns_to_show, column_to_pivot, column_to_aggregate, table_name)

Break it down

Since we can’t rely on Snowflake’s PIVOT function, we’ll need to adopt the classic pattern of CASE statements to test for pivot values. It’s a tedious approach and prone to error because expected values must be manually specified in the query.

for item in pivot_itemsCASE WHEN pivot_column = '{{item}}' THEN aggregate_column ELSE 0 END

Put it back together with dbt

Now that we’re broken down the problem into its parts, we can put them together through a dynamic script using dbt. We will be using what dbt calls “Analysis” for this exercise since all we are trying to do is run some SQL, not build anything permanent.

  • getting a distinct list of pivot values
  • looping over the pivot values to generate a dynamic script

What makes this script work

As we discussed, the key to making this work is getting a list of pivot values which we can then iterate over.

{%- set my_query -%}SELECT distinct {{pivot_column}}  FROM {{my_table}} ;{%- endset -%}{%- set results = run_query(my_query) -%}{%- if execute -%}{%- set items = results.columns[0].values() -%}{%- endif %}
{%- if not loop.last %} 
('item_1', 'item_2', 'item_3')  --good
--instead of
('item_1', 'item_2', 'item_3',) --error

Analysis + parameters = Macro

If you look closely at the Analysis we’ve created, you’ll notice that except for the input variables we set at the start, there is nothing else in the script specific to our table or data values. This means that you can run this neat little script for any table and its corresponding columns—the very reason why dbt macros were invented.

Conclusion

Dynamic pivots are a frequent challenge when doing modeling or data analysis. Still, the key takeaway here is to become aware of the possibilities of what you can achieve when you couple SQL with conditional and iterative logic.

Peaceful warrior. Traveler sans terminus. BI Architect.