I'm using a series of streams and tasks to extract, load, and transform raw data from our ERP system to Snowflake. I'm noticing a 2+ minute delay between when the root task completes and when the next dependent task starts. Each task calls an SQL stored procedure and completes in less than 5 seconds, so I though everything would complete in under a minute. There are a few other queries running at the same time, but they all complete in less than 5 seconds too and nothing that would explain such a long delay. Warehouse usage doesn't indicate being overloaded. Here's the sequence:
Task 1 runs every 5 minutes using
COPY INTOto move files from internal table stage to raw data table.Stream 1 records inserts only on the raw data table.
Task 2 depends on Task 1 using
MERGE INTOto transform and move records from stream 1 to the current data table.Stream 2 records inserts / updates / deletes on the current data table.
Task 3 depends on Task 2 using
MERGE INTOto move records from stream 2 to the historical data table.
Here's a copy of the activity log from Snowflake:
| Task | Start Time | End Time | Duration |
|---|---|---|---|
| 1 | 4:29 PM | 4:29 PM | 1.9 seconds |
| 2 | 4:31 PM | 4:31 PM | 2.7 seconds |
| 3 | 4:33 PM | 4:33 PM | 3.0 seconds |
Any ideas what could be causing this delay? Or have I overlooked something?