Forum Discussion

manishmishra256's avatar
manishmishra256
New Contributor
4 years ago

Snowflake Bulk Upsert: How to have conditional key columns?

I’ve two pipelines, let’s call them Parent Pipeline and Child Pipeline:

In the Child pipeline, I am using Snowflake Upsert snap like below:

The key column i.e. _KEY_COLUMN is passed in Parent Pipeline via Execute Pipeline snap parameters.
Now, I want to add multiple key columns i.e. consider below image:

but the trick is I want to be able to decide which one gets invoked i.e. is there a way I can say from the parent that _KEY_COLUMN_1 is not required?

Now, there can ofcourse be n no. of key columns, but want to start with a finite (say 4) at the moment.

More context about the nature of the pipelines:

  • the child pipeline is basically a generic pipeline which accepts a dataset and tablename which it upserts into and also creating the audit entries
  • the parent pipeline can be any other pipeline which is responsible for a specific dataset and when it comes to upserting that in SF, it calls the child pipeline.

But some datasets may have 1 key column, some can have 2, some can have more. How can I control this generically?

Any suggestions is appreciated.

1 Reply

  • SpiroTaleski's avatar
    SpiroTaleski
    Valued Contributor
    4 years ago

    @manishmishra256

    I don’t believe that you can dinamically adjust the number of selected key columns per case(dataset).

    If each dataset would have the same number of key columns(but different columns) then you would be able switch the key columns by using the ternary expressions.

    In your case, probably you should have separate bulk upsert for each dataset or to standardize each dataset to have the same number of key columns. Or, third option to check if there is a way to achieve the same by using the Snowflake upsert query.

    Regards,
    Spiro Taleski