Week 10 - Stored Procedures & UDFs

Frosty Consulting has a client who wants to be able to load data from a stage in a manual but dynamic fashion. To be more specific they want to be able to:

• execute a single command (stored procedure)
• do so manually, meaning it won't be scheduled and there won't be any Snowpipes
• dynamically determine the warehouse size, if a file is over 10KB they want to use a small warehouse, anything under that size should be handled by an xsmall warehouse.

-- Create the warehousescreate warehouse if not exists my_xsmall_whwith warehouse_size = XSMALLauto_suspend = 120;create warehouse if not exists my_small_whwith warehouse_size = SMALLauto_suspend = 120;-- Create the tablecreate or replace table <table_name>(date_time datetime,trans_amount double);-- Create the stagecreate or replace stage week_10_frosty_stageurl = 's3://frostyfridaychallenges/challenge_10/'file_format = <enter_file_format>;-- Create the stored procedurecreate or replace procedure dynamic_warehouse_data_load(stage_name string, table_name string)<code goes here>-- Call the stored procedure.call dynamic_warehouse_data_load('week_10_frosty_friday_stage', '<table_name>');

Use the above skeleton script, and add the stored procedure.

RESULT

When you execute the last line of the above script "call dynamic_warehouse_data_load()" then you should get the following result.

And when querying the QUERY_HISTORY, you should see that different warehouses were used for different files.

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don't know how to here)
3. Post the URL in the comments of the challenge.