Snowflake - loading queries and controlling the execution of sequence of steps

Question

As a part of our overall flow, data will be ingested into Azure blob from Influx DB and SQL DB, the thought process is to use Snowflake queries/SP to load the data from blob to snow flake in a scheduled manner (batch process). The thought process is to use the Tasks to schedule and orchestrate the execution using Snowflake scripting. Few questions,

1. Dynamic queries can be created and executed based on a config table - Ex: A copy command specifying the exact paths and file to load data from.
2. As a part of snowflake scripting, per understanding a sequence of steps (queries / SP) stored in a configuration DB can be executed in order along with some control mechanism.
3. Possibilities for sending email notifications of error records by loading into a table. whether this should be handled outside of snowflake after the data load process by using Azure data factory / logic apps.

Whether the above approach is possible and are there any limitations in using the above manner? Are there any alternate approaches that can be considered for the above.

Answer 1

you can dynamically generate and execute queries with a SP. You can chain activities within an SP's logic or by linked tasks running separate SPs. There is no functionality within Snowflake that will generate emails