How to schedule a daily sql script in snowflake db

You could use Snowflake TASKS in order to schedule execution of SQL statements. Task can execute a single SQL statement, including a call to a stored procedure. Tasks run according to a specified execution configuration, using any combination of a set interval and/or a flexible schedule using a subset of familiar cron utility syntax.

For your goal I would create a Stored Procedure (so that you could use variables for managing changing filename and for any more complex things).

SF Doc: https://docs.snowflake.com/en/sql-reference/sql/create-task.html

--create a new task that executes a single SQL statement based on CRON definition
CREATE TASK mytask_hour
  WAREHOUSE = mywh
  SCHEDULE = 'USING CRON 0 9-17 * * SUN America/Los_Angeles'
  TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
AS
INSERT INTO mytable(ts) VALUES(CURRENT_TIMESTAMP);


--create a new task that executes a Stored Procedure every hour
create task my_copy_task
  warehouse = mywh
  schedule = '60 minute'
as
  call my_unload_sp();

After creating a task, you must execute ALTER TASK … RESUME in order to enable it.

Use SHOW TASKS to check your task's definition/configuration and then query TASK_HISTORY in order to check executions.

Your Snowflake JS Stored Procedure could be something like this:

create or replace procedure SP_TASK_EXPORT()
    RETURNS VARCHAR(256) NOT NULL
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
as $$    

function getToday_yyyymmdd()
{
    var v_out_Today;
    rs = snowflake.execute ( { sqlText: `SELECT to_char(current_date,'yyyymmdd');` }  );
    if( rs.next())
    {
       v_out_Today = rs.getColumnValue(1); // get current date as yyyymmdd
    }

    return v_out_Today; 
}   

  var result = new String('Successfully Executed');
  var v_Today = getToday_yyyymmdd();

  try {
       
    var sql_command = `copy into @unload_gcs/LH_TBL_FIRST` + v_Today + `.csv.gz from ........`;
 
    var stmt = snowflake.createStatement({sqlText: sql_command});
    var res = stmt.execute();
     
    
   }
    catch (err) {
    result =  "Failed: Code: " + err.code + " | State: " + err.state;
    result += "\n  Message: " + err.message;
    result += "\nStack Trace:\n" + err.stackTraceTxt; 

  } 

 return result;

  $$;

Before creating your task and schedule it, test your Stored Procedure invoking it:

call SP_TASK_EXPORT();