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.
Click to see the skeleton script
-- Create the warehouses create warehouse if not exists my_xsmall_wh with warehouse_size = XSMALL auto_suspend = 120; create warehouse if not exists my_small_wh with warehouse_size = SMALL auto_suspend = 120; -- Create the table create or replace table <table_name> ( date_time datetime, trans_amount double ); -- Create the stage create or replace stage week_10_frosty_stage url = 's3://frostyfridaychallenges/challenge_10/' file_format = <enter_file_format>; -- Create the stored procedure create 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.
When you execute the last line of the above script “call dynamic_warehouse_data_load()” then you should get the following result.
Remember if you want to participate:
- 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)
- Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
- Post the URL in the comments of the challenge.