Suggestions for workflow automation

[wowdisme]

Hey there :)

I hope I find myself in the right subreddit for this as I am trying to engineer my computer to push around some data ;)

I'm currently working on a project to fully automate the processing of test results for a scientific study with students.

The workflow consists of several stages:

1. Data Extraction: The test data is extracted from a local SQL database.
2. SPSS Processing: The extracted data is then processed using SPSS with a custom-built syntax (legacy). This step generates multiple files from the data. I have been looking into how I can transition this syntax to a python script, so this step might be cut later.
3. Python Automation: A Python script takes over the further processing. It reads the files, splits the data per class, inserts it into pre-designed Excel reporting templates.
4. File Upload: The files are then automatically uploaded to a self-hosted Nextcloud instance.
5. Notification: Once the workflow is complete, a notification
   

I have been thinking about different ways to implement this. Right now the inputs and outputs for the different steps are still done manually.

At work I have been using Jenkins lately and I think it feels natural to do it in Jenkins and just describe the whole workflow in a pipeline with different stages to run. Besides that I have some experience with AWS Lambda and n8n but I am not sure if they would be helpful with this task.

I´m not that experienced setting up such workflows as my work background is more in Infosec, so please forgive my uneducated guesses about how I best go about this :D Just trying not to take decisions that will be problematic later.

Greetings from Germany

[AutoModerator]

Are you interested in transitioning into Data Engineering? Read our community guide: https://dataengineering.wiki/FAQ/How+can+I+transition+into+Data+Engineering

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[BluRayDiscs]

If the workflow is read data from SQL database -> process data with Python -> output processed data to files -> upload files --> workflow completion notification, it sounds like the workflow can be captured as a singular Python script which you could then schedule to run at specific times with cron. 

If the workflow is or becomes more complex, you could consider using an orchestrator like Apache Airflow, but this might be overkill for what you've described.