I have created external table in snowflake and it should refresh if data is there in s3.

my S3 bucket URL is :

s3://compnay_bucket/pitc/2022-03-25/Investor

In the above URL date should be dynamic and I have set the pattern as :

set investor_pattern = '\.*/'||to_char(current_date(), 'YYYY-MM-DD')||'\/Investor/'||'\.*';

My External DDL is

create or replace external table PITCHBOOK_DEV.PB_Investor 
(
    InvestorID varchar(60) as ( $1:InvestorID::varchar ),
  InvestorName varchar(60) as ( $1:InvestorName::varchar )

)
with location = @compa/pitc/
file_format = company_parquet
pattern=$investor_pattern
aws_sns_topic='arn:aws:sns:us-west-1:5:snowfla'
auto_refresh = true

So far tried :

i) When I execute "show external table PITCHBOOK_DEV.PB_Investor" command. I can see SNS notification being kicked off. SO there Is NO ISSUE with SNS.

ii) when I do an ls on stage with today's date it is returning me files.

if I execute "investor_pattern" and "create table..", I am able to see today's data. This should happen dynamically.

Please let me know what should be changed here.?