Showing results for 
Search instead for 
Did you mean: 

Dynamically process json files from sftp to snowflake

New Contributor II

Hello everyone,

I’m new to snapLogic and I have this business need to process json files everyday or even every hour from a SFTP server.

The 1st difficulty: Files aren’t stored in the root folder, but in Year → Month → Day → Hour folders (so 4 subfolders)

I have managed to retrieve the filenames using this process (in the last attached snap, on $Path variable)
Capture d’écran 2023-07-11 à 17.58.34
Capture d’écran 2023-07-11 à 17.59.42

Now to make it dynamic I wanted to query on Snowflake the filenames that are already stored and Filter the result so the filenames in my query should not be inserted in my target table ie i want to retrieve only the paths that aren’t in my table.

Does someone know how should I proceed? Tell me if i’m missing in detail. Thank you so much for your help 🙂

FYI There is about 70000 json files per “Day” Folder


New Contributor II

I have actually added a pipeline execute unattached to the pipeline shared above to retrieve the snowflake query result :
Capture d’écran 2023-07-11 à 18.08.12
Now i’m not sure how to join both of it