is Airbyte open source a good solution for automating a once daily download of a file from an ftp or site that has it's own data structure, extract just a select part of that data and put it into a new CSV document, and then finally upload that through a new sftp?

There is a source connector for SFTP and a more generic Files source connector, both of these can be scheduled to sync daily! It looks like either of them would work for your use case. Loading that into CSV is possible, however the CSV destination connector outputs the file onto the local system and Airbyte does not yet have an SFTP destination connector that outputs in CSV. From there it would probably mean using

scp

or

sftp

(maybe with cron?) on the system hosting Airbyte to transfer files out. If you're ok with receiving JSON in the destination, you can configure the source connector to sync with the SFTP JSON destination connector on a daily schedule. I would personally choose this option, it's less moving pieces and you don't have to mess around with cron/scheduled tasks, you should also be able to convert the JSON loaded onto the destination system into CSV using python pandas or a tool like dasel. Unfortunately it's harder to make a generic destination connector similar to the

Files

source connector I linked above, because Airbyte extracts and stores data as a generic json blob format 😞 all restructuring/transformations are performed after loading into the destination. This is part of why the tool is so performant, but sometimes there is an extra step to get the data into the format you need.

hi @Sunny Hashmi (Airbyte) ! could i ask you for help configuring an sftp-json destination - i get an error when trying to using a public key in password field