Hello folks, I am new to prefect and trying to build a process to load millions of files from a FTP location to one of the local database. While doing this we have the typical ETL transformation which happens at each file. My question here is on how can i design this using prefect? Right now we have a in house process which leverages Celery to queue and process the files but the way it runs currently, it downloads all the files and then perform the ETL process on each one of it in parallel. Shall we maintain it the same way or download each file and then perform ETL operation? Maintaining error files state even if retry logic fails. Also the current time which this process takes is about 20-24 hours as it processes Billions of records. Any insight on how to design this would be helpful.

Hi @raman, Welcome to Prefect! You’ve come to the right place with this questions 😄 Before I answer, I’d like to ask a few questions to make sure I understand your constraints and goals. * Are you trying to reduce the amount of time this flow takes? * What sort of resources are you working with? Would you have access to a large Dask cluster? * Is it possible to detect when a new file hits the FTP? If so, you may be able to process the files as they come in. Take a look at: https://medium.com/the-prefect-blog/event-driven-workflows-with-aws-lambda-2ef9d8cc8f1a

Hi @Dylan, Thank you for your response. To answer your questions: 1) Yes the primary reason for migrating this workflow is to reduce the time and also to monitor the files which have loaded and which have failed. 2) We don't have a dask cluster, do you see any advantage of having a Dask cluster with the flow whcih we have highlighted? we are leveraging pandas directly so far and In terms of resources hardware wise we have dedicated onprem servers and because of the sensitivity of the data it cannot be moved to cloud servers. 3) Yes, We can detect the new files on the FTP in few cases but since the data is sensitive and is onPrem i need to explore "non cloud" options to perform the operation. Happy to answer any further Questions you have.

@raman Great! Thanks for these

Thanks @Dylan This is very helpful!! Let me try these and will get back to you with further questions.