I’m wondering if and how prefect could be used to transfer large amounts of data between different servers/clouds. Basically, I’d need to move 10-100 gigabyte csv/Jason files from an SFTP server to ADLS, and later on between other sources and sinks. Moving this amount of data as a one gigantic in memory string between tasks does not seem very sound approach for many reasons. So how would you actually do that?

Hi Matias, you’re right, moving that much data in memory is always challenging. Especially if you need to convert to parquet, it sounds like this could be a good fit for a Spark job or Dask, which can both work with partitioned data. It’s been a while since I had to load anything from SFTP so I confess I don’t remember if it’s possible to read that remote data in chunks (I suspect not), but at least a cluster would give you adequate RAM. In that case, you could use Prefect to schedule, launch, monitor, and clean up the Spark job.

Hi @Matias, just to add to what Jeremiah mentioned, we've had a good experience with an open source tool to transfer large data sets called Embulk: https://github.com/embulk/embulk From their github page: "Embulk is a parallel bulk data loader that helps data transfer between various storages, databases, NoSQL and cloud services. Embulk supports plugins to add functions. You can share the plugins to keep your custom scripts readable, maintainable, and reusable."

That’s interesting, thank you. Would you wire embulk somehow to Prefect to make them work together?

We are planning to have Prefect tasks that execute an embulk command, e.g. transfer a file from SFTP to Amazon Redshift.

Thanks for the comments @Joe Schmid, very helpful — I wasn’t familiar with Embulk

Thanks @Joe Schmid. We've been using singer taps and targets for a similar workflow--running from command line to get the connection right and then automating using prefect

Thanks @Adam Roderick I'd be curious to hear what your early experience with Singer has been like and what you'll use it for. I like that Singer is python-based and conceptually it looks a lot like Embulk, i.e. plugins/taps, etc. (Feel free to DM if you prefer.)

@Joe Schmid It's been great for a few reasons. I wanted flexibility and a community that contributes to plugins. We've been able to do local development in postgres and production development in redshift, all with just configuration changes--the consistency of the tap + target pattern is very nice. We've also found writing taps to be pretty straightforward and have written one and forked a couple of others. The streaming aspect is nice, although it looks like it depends on the implementation. For example, the redshift target batches because of the nature of how redshift loads data from S3. Not quite streaming, but the batch configuration settings get you some of the benefits. We create data platforms for our customers, and I wanted a repeatable way to do the ELT pattern, and singer does extract and load quite nicely.

Another vote here for Singer

Thanks @Adam Roderick & @Sergi de Pablos for the comments on Singer -- much appreciated.