Subtracting columns in snowflake/DBT

user_14307098 0
Viewed 214

I have two columns one with pick objects and another with unpicked objects for each client.

I need to create a column with the final objects for example:

Customer_ID Picked. Unpicked. . Final Cart
799235 shirt, pants, glasses . glasses. shirt, pants
799246 dress, pants, glasses . pants, dresses glasses

I was trying to do this in Snowflake but it looks like none of the defined functions work.

And a customer can add and remove an item as many times as they want, I'm only interested in the final values.

dbt snowflake-cloud-data-platform
1 Answers

Unfortunatley, dbt jinja isn't useful for this problem, because it's (almost always) compiled before SQL run-time, so it doesn't have access to the values.

This can, however be solved with Snowflake SQL. The basic pattern is to flatten out the values, then do your subtraction, then to aggregate them back together.

Here's some code:

with carts as (
    select 799235 as customer_id, array_construct('shirt', 'pants', 'glasses')  as picked, array_construct('glasses') as unpicked
    union
    select 799246, array_construct('dress', 'pants', 'glasses'), array_construct('pants', 'dress')
),

picked_expanded as (
    select
        carts.customer_id,
        picked.value::varchar as item
    from carts, lateral flatten ( input => carts.picked ) as picked
),

unpicked_expanded as (
    select
        carts.customer_id,
        unpicked.value::varchar as item
    from carts, lateral flatten ( input => carts.unpicked ) as unpicked
),

combined as (
    select * from picked_expanded
    except
    select * from unpicked_expanded
),

agg as (
    select
        customer_id,
        array_agg(item) as final_cart
    from combined
    group by 1
)

select * from agg

Which gives the result:

CUSTOMER_ID FINAL_CART
799246 ["glasses"]
799235 ["shirt", "pants"]
user_46919200
Related