Pipeline inside a transaction

Help Connectors and Pipelines
1

Hello,
Is it possible to start an Azure pipeline inside a transaction?
for example:
START TRANSACTION;
TRUNCATE TABLE x;
START PIPELINE y;
COMMIT/ROLLBACK;
so if the pipeline fails, a rollback can be performed and table x wouldn’t be truncated.

1 Like
2

TRUNCATE can’t participate in a multi-statement transaction, so no.

3

You are right, its a bad example. Lets say DELETE FROM instead of TRUNCATE.

4

Hey Chen,

Let me give a quick note before answering the question you literally asked.
Pipelines have “exactly once semantics” (even from Azure), so if you say start pipeline or start pipeline foreground and some file fails for whatever reason, that file will be retried a few times, and if it fails, the pipeline will stop. This information can be monitored in information_schema.PIPELINES_FILES. So, very likely, all you want to do is start the pipeline and let it run. Also, you can use START PIPELINE <name> FOREGROUND to run it in the foreground, START PIPELINE as written starts it in the background and it will continue running and loading new files as they appear.

Now to answer your actual question. Even if this were delete (or any other statement), this is not possible, as start pipeline starts a pipeline in the background. However, you can use a stored procedure to transactionally handle pipeline data. For instance, your procedure could be

create procedure p(batch query(...))
as
begin
    delete from x;
    insert into y select * from batch;
end

pipeline SP’s automatically are wrapped in a transaction, and included in that transaction is updating the offsets of the pipeline, (that is, "updating pipelines_files").

However, this procedure will run each “batch”, which is a collection of some number of files. There is currently no way to “transactionally load all files” in a bucket, rather, every file in the bucket will be loaded exactly once (or the pipeline will give up). See my first paragraph about why it probably doesn’t matter.

Hope this helps,
-JoYo

1 Like
5

Hi JoYo,
Thanks for the detailed and informative answer, its very helpful!

1 Like