title: Snowflake COPY INTO from AWS S3 while including functions
body: <p>I am thinking of a few ways to do this but would like some input about effective options. The problem here is this. I need to perform some ETL actions using COPY INTO into a database I've setup for Snapshot. Which is defined as the past day. The stage here is an AWS S3 bucket. To capture the most recent files I need to implement a filepath into the from statement or pattern.</p>
  
  <pre><code>CREATE OR REPLACE TASK ETL_1
  WAREHOUSE = W1
  SCHEDULE = 'USING CRON 30 */4 * * *  America/Los_Angeles'
  AS
  copy into DB.SCHEMA.TABLE1
    from @STAGE/TABLE1
    pattern='.*/.*/.*[.]csv'
    on_error = 'continue'
    file_format = csv_etl;
  </code></pre>
  
  <p>Using something like this I get get the file path I need</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION CURRENT_DATE_FILEPATH()
      returns string
      as 
      as REPLACE(CURRENT_DATE(), '-', '/')
      ;
  </code></pre>
  
  <p>This will return the date format like '2020/04/15' that I then want to somehow use in the stage format
   to be like @STAGE/TABLE1/2020/04/15/* </p>
  
  <p>Is there a way to include this into my copy into statement?</p>
  
  <p>I can do this in a procedure using Snowflake's Javascript functionality sure but unless I make a task for each procedure I will lose the granularity of being able to monitor data being loaded into every table.</p>
  
  <p>As it would indeed only take one procedure looping through my tables to do this. But these tables can be fairly massive day to day.</p>
  
  <p>Is this the best way? </p>