Can we have task start condition dependent on Sucess condition of PIPE in SNOWFLAKE

Question

I have a requirement where 3 different file will be loaded to a single table with 3 different PIPE. I want target my target process to be triggered only once all 3 file has been loaded to my stage.

I don't want to run my target process multiple times.

So is there any way we can have start condition of task on PIPE sucess.

I went to documentation but didn't find any such info or is there way of implementing it which I might be missing.

Answer 1

The general way to implement this pattern is with streams. Your pipes would load to three separate tables, each with a stream on it. You can then have a task that runs on a schedule, with the WHEN parameter set with SYSTEM$STREAM_HAS_DATA, three times. This ensures that your TASK only runs when all three pipes have completed successfully. Example:

CREATE TASK mytask1
 WAREHOUSE = mywh
  SCHEDULE = '5 minute'
WHEN
  SYSTEM$STREAM_HAS_DATA('MYSTREAM') AND SYSTEM$STREAM_HAS_DATA('MYSTREAM2')
  AND SYSTEM$STREAM_HAS_DATA('MYSTREAM3')
AS
  <Do stuff.>;

You have a couple options here. You can:

1. use the data in the streams to do whatever you want to in the task, or
2. you can use the data in the streams to fill the single table that the three pipes were originally filling.

If you choose option 1, you might then also want to create a view that replaces your original single table.

If you choose option 2, you can set up a task that runs using the AFTER clause to do whatever it is that you want to do.