How to create a stored procedure that creates a table at the end of the month, but the table name gets automatically generated based on system date

Question

Trying to create a stored procedure that updates each month on Snowflake:

Step1: Creating the Procedure

Create or replace procedure test_table()
Returns VARCHAR(16777216)
LANGUAGE SQL
AS '
BEGIN
   CREATE OR REPLACE TABLE TEST_DATA_MARCH_2024 AS
   SELECT * FROM TEST_VIEW;
   RETURN ''Test snapshot for March'';
end;
';

Step 2: From the above Procedure, I have set up a task to refresh every month.

create or replace task Test_Task
warehouse = XYZ
SCHEDULE = 'using cron 0 7 1 * *'
as CALL test_table()

alter task Test_task resume

This logic will create the table on the first of every month at 7 AM, Now I want to update the name of the table automatically, Instead of overwriting TABLE TEST_DATA_MARCH_2024 I want the procedure to create the table name having the respective month and year name in it.

So for April 2024, The table created should be TEST_DATA_APRIL_2024 instead of TEST_DATA_MARCH_2024.

any tips or tricks will help, Thanks

Answer 1

Yes it is possible, Snowflake has MONTHNAME function returns 3 letter Month names. You can set a variable at the beginning of the stored proc and use in the table name

set dt = to_date('2024-04-01'); --replace with current_date()
set table_suffix = (select UPPER(MONTHNAME($dt-1)) || '_' || YEAR($dt-1));
select UPPER($table_suffix) will give you the value

To add the code to your SP

Create or replace procedure test_table()
Returns VARCHAR(16777216)
LANGUAGE SQL
AS '
BEGIN
   set dt = to_date('2024-04-01'); --replace with current_date()
   set table_suffix = (select UPPER(MONTHNAME($dt-1)) || '_' || YEAR($dt-1));
   CREATE OR REPLACE TABLE TEST_DATA_$table_suffix AS
   SELECT * FROM TEST_VIEW;
   RETURN ''Test snapshot for March'';
end;
';