ELT in Snowflake using Stored Procedure

You can build ELT in Snowflake using Snowflake Stored Procedure. Snowflake Stored Procedure are created using Java Script.

How to call Stored Procedure & Function: You can call Stored Procedure using a call Statement (directly using call statement or from TASK). You can use Function call in the SQL statement to get the value.

Stored Procedure Call : CALL PERSON_MASTER_PROCEDURE(arguments1);

Function Call : SELECT PERSON_MASTER_FUNCATION(argument1) FROM PERSON_MASTER;

Returning a Value from a Stored Procedure is Optional: A stored procedure is allowed to return a value, but is not required to return a value. A function, on the other hand, is required to return a value.

In this example we will create stored procedure to run Multiple SQL statements to automate data Load from PERSON_MASTER to two tables PERSON_AGE(Name, Age) and PERSON_LOCATION(Name, Location). This stored procedure will be called by TASK.

This Stored Procedure can be part of the ELT job, we can CALL this stored procedure using TASKS to run recursively based on a schedule and when there is a data in Stream to complete the data pipeline.

You can also create variables dynamically in stored procedures and use them in the SQLs inside stored procedure. If you want to build a Cleanup job then you can create Stored Procedure to run DELETE Statements or TRUNCATE Statements or DROP statements to perform Table/Data clean up.

This blog is continuation of my previous blog on Snowflake Pipeline. Will create below two new tables to load them from PERSON_MASTER.

Create Stored Procedure which runs below 2 SQLs.

1. Insert data into Location table from Person Master table.
2. Insert data into Age table from Person Master table.

We can call this Procedure directly using CALL statement or Schedule to run using TASK.

1. Call Statement:

2. Create TASK

This task runs every 1 minute, if there is data in Stream PERSON_NESTED_STREAM then it CALL the Stored Procedure. If there is no data in Stream then the TASK will be skipped.

As we ran the Procedure manually using CALL statement, data should be loaded to PERSON_AGE and PERSON_LOCATION tables.

Conclusion:

We can use Stored Procedure in Snowflake to build ELT to continuously load data to Snowflake and also to run multiple SQLs. You can dynamically create variables in Stored Procedure. You can call Stored Procedure directly using CALL statement or using TASKs.

I hope this article is useful. Feel free to leave a comment or share this post and connect with me on Linkedin. I am a AWS Certified Solution Architect with Strong Knowledge on Snowflake Development, Architect, Design, Security and Networking. Actively looking for new job opportunities.