CODEWITHSUNDEEP
amazon-s3snowflake-cloud-data-platformsnowpipe
Abiodun Adeoye
Abiodun Adeoye

Reputation: 1095

How to trigger Snowflake task after snowpipe completes its run

I am new to snowflake and I am trying to load data from S3 into Table A and trigger a Task to load data into table B from Table A. I have successfully implemented an automated snow pipe that loads data into Table A from an S3 immediately after data arrives in S3. It uses as SNS that listens to any changes in the S3 bucket and then sends an event to Snowpipe which triggers the copy into function in the pipe and loads data into table A.

The objective is to trigger a Task that loads data into Table B from Table A when snowpipe completes its run and then truncates tables A. I'm unsure how to approach this as I am concerned some data might flow into the s3 while the Task is running and when its truncates table A it may delete additional data that came in.

Upvotes: 3

Views: 1977

Answers (1)

Robertino Bonora
Robertino Bonora

Reputation: 108

Depending on the frequency of the data ingestion but you could create a stream over TableA, this stream does CDC (change data capture) so it records any dml that happens on the table.

Then you could create a task that is triggered only when the stream has data, inside that task you could call a stored procedure that merge the data from TableA to TableB

Upvotes: 2

Related Questions