I am designing a workflow that pulls data from our scheduler databases so that I can build a dashboard that allows me to view when workflows are scheduled, and how often they fail. Whenever I run this workflow it consistently causes failures to appear in the results database. I believe this is caused because other scheduled workflows finish running as I pull the data from AS_results. Currently, there are some short running workflows scheduled to run every 5 minutes, and the workflow I designed takes more than 5 minutes to run, so I can't find an optimal time to run this workflow.
Here are the solutions I have considered:
Rescheduling the workflows set to run every 5 minutes to not run on a weekend, as that is an off day, and they don't need to be scheduled then. This would allow me to schedule my new workflow on one of the weekend days, and solve my problem.
This didn't work, because it seems that I can only schedule a workflow to run either on a set of days at one specific time, or every x minutes on all days. Because of this it seems I would need to make more than 200 daily schedules to schedule something every 5 minutes Monday-Friday. This didn't seem feasible
Copying and pasting the sqlite files so that I am only working on the copy, and the main tables stay intact.
This solution seems like it could possibly work, but I have concerns about what will happen if the alteryx scheduler needs to add data to the table as it is being copied. I did some research that seemed to suggest that it would only potentially cause issues in the copied database, but not in the original database. I haven't attempted this solution yet, because even if the original would stay intact, it would not be ideal for the workflow I made to sometimes have incorrect or corrupt data, and I can't guarantee that it won't corrupt the original. I would appreciate any knowledge you have about this subject.
Switching to mongoDB. I believe mongo DB wouldn't have the same issue when inserting and selecting data from the database.
I haven't tried this solution yet, because it seems that it would affect all of our running workflows, and potentially require us to still use the old sqlite database, and the new mongoDB database, which would be more work to maintain.
I would appreciate any advice if you have been in a similar situation. Also, if you believe there is an easier solution I haven't considered, or that one of the solutions I proposed is easier or more effective than I described, please let me know.
Thanks for your help
