Array subtraction in Snowflake

[samLozier]

@joellabes suggested that I post this question here. I'd initially posted this over on the dbt-utils issue tracker.

Basically, as far as I'm aware, snowflake can combine arrays, check for intersection and aggregate but it is not easy to subtract arrays using a built in function. I don't know if this behavior is the same in other databases, so this may not be broadly useful, but I have found a way to do this in Snowflake that may be useful as a utils function.

I described the method in reasonable depth over on the issue page dbt-labs/dbt-utils#609

It sounds like for this to be rolled out in dbt-utils, this would need to be broadly useful across databases, hence the need for further discussion.