Automating ETL to SFTP Server Using Python and SQL | by Mary Ara

Learn how to automate a daily data transfer process on Windows, from PostgreSQL database to a remote server

The process of transfering files from one location to another is obviously a perfect candidate for automation. It can be daunting to do repetitively, especially when you have to perform the entire ETL (Extract, Transform, Load) process for several groups of data.

Imagine your company has their data in their data warehouse, and then they decide to contract out part of their analytics to an external data analytics supplier. This supplier is offering a bespoke analytics software that will display dashboards and reports for the core poduction team of your company.

The implication of this is that, you, as the data engineer, will have have to transfer data to this supplier daily, hourly, every 30 minutes or any other frequency decided upon by the out-sourcing contract.

This article explains in detail this ETL process that includes an SFTP upload. We will incorporate Secure File Transfer Protocol (SFTP) which is a secure means of transfering files between two remote servers, by encrypting the files using what is known as the Secure Shell (SSH) protocol.