Context
I like using Snowpipe a lot but I have no way to apply my upsert logic when using it.
This is how my upsert logic looks like:
create temp table temp_table (like target);
copy into temp_table from @snowflake_stage;
begin transaction;
delete from target using temp_table
where target.pk = temp_table.pk;
insert into target
select * from temp_table;
end transaction;
drop table temp_table;
However with Snowpipe I am only allowed to define a single copy command, hence I am not able to execute a sequence of commands.
What I have tried to do
I thought about using Tasks & Streams but Tasks do not seem to support transactions (more than a single query per task). I also thought about using MERGE but I have to explicitly define the columns I want to INSERT.
For example, I can't do things like (insert without defining what to insert):
merge into src using temp_table on src.pk = temp_table.pk
when not matched then insert;
Are there any other ways I could upsert my data while still using Snowpipe?