Snowflake date Dynamic variable

user_17867413 0
Viewed 59

My S3 path is

s3://datahub-processed-zone/pit/2022-03-25/Investor/

and Snowflake-S3 Integration is s3://datahub-processed-zone which is called com_stage. I am generating a dynamic stage and I have set variable for date. The dyamnic stage should read all files in the Investor folder

set investor_pattern2 = '/'||current_date()||'\/Investor/'

ls @com_stage/pit pattern=investor_pattern2

but it is don't giving me any files. Can some take a look into why is it not returning me any data ?

Thanks, Xi

snowflake-schema snowflake-cloud-data-platform
2 Answers

The variable needs to be prefixed with $ when you use it e.g.

ls @com_stage/pit pattern=$investor_pattern2
user_136583990

Alternatively, you can access string variables using identifier():

ls @com_stage/pit pattern=identifier('investor_pattern2')
user_105219590
Related