How to use multiple transactions in Snowflake Task?

Question

I have two ETL jobs running on a stream I've created on a table. I need to run both on the same stream data and I read that in order to do so the DML statements (in my case merge statements) need to be wrapped in a transaction and committed at the end. I can't seem to be able to do that in a task though. I think I'm messing the semi-colon somewhere. This is what I've tried

create or replace task my_task as
begin;
  merge into my_table1 t using my_stream s on t.id=s.id when matched insert values (id, col1);

  merge into my_table2 t using my_stream s on t.id=s.id when matched insert values (id, col2);
commit;

This is just an example, the merge statements do more complex stuff.

The script just runs up to begin if I use a semi-colon or get an EOF error if I don't use one even though I have multiple semi-colons later in the script (so it tries to read past commit)

user_132438 · Answer

The task can call a stored procedure that contains the different statements within a transaction:

create procedure ...
    as
    $$
        ...
        statement1;

        BEGIN TRANSACTION;
        statement2;
        COMMIT;

        statement3;
        ...

    $$;

https://docs.snowflake.com/en/sql-reference/transactions.html

How to use multiple transactions in Snowflake Task?

1 Answers