Some of our data is stored like this:
|3||c , d|
When really we want it like this:
In postgres, you can use the UNNEST function, however, there is no equivalent in Redshift.
I took inspiration from this blogpost, and combined it with the power of dbt to create a macro that unnests lists!
I also created a macro that unnests json arrays.
Check out this gist to see the code for the macros, along with some examples (I’ve kept it as a gist since there’s multiple files).