The problem I’m having
I have a redshift table that has three columns that have values such as this: “sc_mba”, “sc_mba”, “yt_uwe”, “sc_mba”. I am looking to write a model or cte that would select that column and run code to collapse that list such that there are no consecutive repeating values. so my example would end up turning into “sc_mba”, “yt_uwe”, “sc_mba”
The context of why I’m trying to do this
this is tracking movement in a web session and they may or may not go to different domains. I only care to see when they switch domains so thats why I only want cases where in that list the value changes after each comma
What I’ve already tried
attempted to use a macro to pass the value from the field in a select statement but i kept getting errors because the field name was being passed not the field value. My understanding now is that macros dont run on the db level? so I am curious how I get around this? Use run_query perhaps?
I would think applying a custom function to a field in a select statement would be a common use case for dbt so maybe I am missing something. Thanks in advance