Over the past few years we've thought about a workflow process of storing data in a database, either Oracle or SQL, and piping data from various tables into a feature layer hosted on AGOL (not running Enterprise/Portal). The workflow would also work going the other way where data entered into a SmartForm or S123 app feeds the feature layer and then those data get pulled into the Oracle/SQL tables.
One idea would be that Python with the ArcGIS API for Python be used to pull data from the FL and pipe it into a data frame where it could then be piped into one or more tables stored in Oracle/SQL. Embedded SQL queries can do this, but it can be cumbersome.
I'm wondering what others have done or if there's a better way of syncing data from AGOL to a database connection that's efficient? Any ideas, suggestions, helpful tips would be appreciated!
