ETL Procedures in Snowflake

To continue on from my last post I have kept on playing in Snowflake and am working towards building something that resembles ETL Procedures on Snowflake. Now as this is just something I am messing around with at home it is much more about learning that doing things 100% correctly.  For me the below script was the first time in Snowflake SQL procedure I have: 

• Used Exception handling 
• That works and inserts into a log table, now to see if I can log the error message
• Performed a dynamic insert using data retrieved from one of my own tables. 

Whilst these are not earth shattering they are little thing I was not confident on how to do this morning and now I have a working example. I was not confident this morning as I am honestly finding it hard to find real world examples written in Snowflake using their SQL language, all examples seem to be in the Javascript one. 

As with my previous post below is the code and a little gif showing the code being called. 

	create or replace procedure p_run_control(p_csl_id number) returns number
	language sql as
	declare
	v_sql varchar2(2000);
	v_year_month number(6);
	begin
	select period into v_year_month from KEBOOLA_7127.WORKSPACE_15661914.CURRENT_PERIOD cp ;
	select sql_text into v_sql from KEBOOLA_7127.WORKSPACE_15661914.ICE_CONTROL_SOURCE_LINK where csl_id = :p_csl_id;
	v_sql := 'INSERT INTO KEBOOLA_7127.WORKSPACE_15661914.ICE_AMOUNT(year_month,csl_id,amount) select ' || v_year_month || ' , ' || p_csl_id || ' , amount FROM (' || v_sql || ' ) x ' ;
	execute immediate v_sql;
	exception
	when other then insert into KEBOOLA_7127.WORKSPACE_15661914.ICE_CONTROL_ERROR(icsl_id, year_month)
	values(:p_csl_id,:v_year_month) ;
	end ;

view raw Snowflake_Run_Control.sql hosted with ❤ by GitHub