Transfer data from SFTP to Snowflake?

Matillion ETL
1

I am looking to pull data from an SFTP location (CSV file that is loaded to this daily) and then import this into our Snowflake database.

The process I am looking to achieve is:

  • CSV file lands in SFTP (scheduled at a set time per day)
  • Matillion pulls the CSV file (scheduled for a set time)
  • Matillion transforms data and appends to a table in Snowflake

What would be the best process for this in terms of orchestration/transformation nodes?

2

Hey @thomas​ ,

The best approach I have found is to land the CSV data directly into a a temporary or transient table. This would be a different table than the final table you want to land the data in. Doing this allows you do any data cleanup and validation as you move it from the landing/raw table into the final/staged table.

If you follow this approach you can accomplish it a couple different ways. You could have an orchestration that moves the files from SFTP to an S3 or Blob Storage location. Then use an S3 Load or Blob Load component to load the data into a transient or temp table.

0694G00000IKiryQAD_0D74G000007kWwbSAE

Then you would do the transformation and moving of the data from the raw table to the final/staged using a transformation. Something like this:

0694G00000IKjMLQA1_0D74G000007kWwbSAE
0694G00000IKjMLQA1_0D74G000007kWwbSAE688×253 38.5 KB

Hopefully this helps. Let us know if you need more info or detail. Thanks for posting!